Skip Navigation LinksHome > Categories > Code from a Category

Simple Edit, Update and Delete of multiple tables from SQLDataSource using Stored Procedure



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 2287
Add Date: 02/21/2013
In this article we will learn about simple edit, update and delete of multiple tables from sqldatasource using stored procedures.
Introduction

We, the asp.net developers, knew the importance of using data binding controls, sometimes it’s a very challenging task, to use it to update or delete or manipulate data from two or more tables which is joined together with or without foreign key references.

So this article takes the pride of explaining the concept by using SQLDataSource and a Stored Procedure that edits, updates and deletes data in two tables at a time. Besides that, this article speaks little bit about the Stored Procedure with optional parameters, and binding a DropDownList control that contains some static items with the value from the database.

Sample Scenario

For demonstration purpose of this article, we display some customer information along with their choice of interest. The customer information is fetched from the Customer table and their interest is kept stored in another table known as CustomerInterest. The Customer and CustomerInterest tables are joined, updated and deleted together using single Stored Procedure called “Proc_UpdateTwoTable” that is called from the SQLDataSource control. From here we go directly to the working arena.

Create the Stored Procedure

In your MS SQL Server database, create the Stored Procedure “Proc_UpdateTwoTable”. The complete code is given below

The above Stored Procedure takes 4 parameters such as @ExecFor, @Cus_Code, @Cus_Email and @Cus_Interest. A parameter to a Stored Procedure can be optional, which means, while calling the Stored Procedure, it is not mandatory to pass the value for that parameter. An optional parameter is created by assigning a null value to it in the parameter declaration section of the Stored Procedure. The optional parameters in the above Stored Procedure are highlighted below,


The @ExecFor parameter is like the Execution ID, instructs the Stored Procedure about which section of command to execute. So when we pass the @ExecFor as 1, then Select statement will work and this will return data to fill the GridView control. When @ExecFor is 2, the Update statements will work, which will update the customer email id in Customers table and customers interest in the CustomerInterest table. Finally, when @ExecFor is 3, the delete statements will work. Now let us see how we are going to use the Stored Procedure in the SQLDataSource control to manipulate multiple tables. In your aspx page, drag and drop a GridView control and an SQLDataSource control, and the name will be GridView1 and SqlDataSource1 respectively.

Setup GridView control

Set the DataSourceId of the GridView control as “SqlDataSource1”, set its AutoGenerateColumns property as “false” and set its DataKeyNames as “Cus_Code”. The customer code [Cus_Code] is the primary unique key column in the Customer Table. We are going to manipulate the data based on this field. Next create some databound columns in the GridView control to display data, for this article purpose; we have created data bound columns such as Customer Code [Cus_Code], Customer Name [Cus_Name], Customer Age [Cus_Age], Customer Email [Cus_Email] and Customer Interest [Cus_Interest]. The last Customer Interest column is a TemplateField column, so place a Label control in the ItemTemplate section and place a DropDownList control in the EditItemTemplate section. Set the Text property of the Label control to display the Customer Interest value, sometimes, the value in table might contain a null or empty value, so if null value exists, we have to replace that with “Nothing” like below,

Next add some values in the DropDownList control in the EditItemTemplate section and bind its SelectedValue property as “Cus_Interest” as follows,

Then add two CommandField columns for Edit and Delete purpose. The complete source code of the GridView control is given below,

Setup SQLDataSource control

In the SQLDataSource control, set the ConnectionString property to point your database. The best way is to place your connection string in the web.config file and refer it like below.


Next in the SQLDataSource control, we are going to set up the Select, Update and Delete commands as the Stored Procedure name and its command types as “StoredProcedure” as below,


Finally, we are going to set the parameters for each command and set the ExecFor parameter with default value as follows
Select Command Parameter

Update Command Parameter

Delete Command Parameter

The complete html source of the SQLDataSource control is given below,


Points to Remember:

1. While specifying the Name of the Parameter use the same name as specified in the Stored Procedure.
2. If a parameter in Stored Procedure is optional, then you must not include in the commands unnecessarily.
3. Make sure the number of parameters in Stored Procedure and SQLDataSource controls is same.


That’s it. Save all and browse this page. By default the SQLDataSource control will execute the SelectCommand to fill the GridView control. So you can edit and delete any rows and see it is affecting both the tables.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.