Editing and updating data in gridview using sqldatasource control - Part 17

Suggested Videos 
Part 14 - ConflictDetection property of SqlDataSource control
Part 15 - Deleting data from gridview using objectdatasource control
Part 16 - ConflictDetection property of objectdatasource control



In this video we will discuss about editing and updating data in gridview control using sqldatasource control

We will be using tblEmployee table for this demo. SQL script to create and populate this table with sample data is available in Part 13 of asp.net gridview tutorial.

Create an asp.net web application. Drag and drop a sqldatasource control and a gridview control on webform1.aspx.



Configure "SqlDataSource1" control 
1. Right click on "SqlDataSource1" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. On "Configure the Select Statement" screen, select "tblEmployee" table from dropdownlist.
6. Click on "Advanced" button
7. Select "Generate INSERT, UPDATE and DELETE statements" checkbox and click OK
8. Click Next and Finish

Now flip "WebForm1.aspx" to "HTML Source" mode. Notice that, the wizard has automatically generated INSERT, UPDATE and DELETE statements. Since, we only want to enable the gridview control to eidt and update data, get rid of InsertCommand, InsertParameters, DeleteCommand, and  DeleteParameters. 

Now let us associate "SqlDataSource1" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag" 
2. Select "SqlDataSource1" from "Choose Data Source" dropdownlist
3. Select "Enable Editing" checkbox. At this point "Edit" button should appear on the gridview control.

Now run the application. Click "Edit" button. Notice that "Update" and "Cancel" buttons are displayed, when the row is in EDIT mode. Also, notice that except "EmployeeId", all the other columns display a TextBox control, so the user can change data. By default, bound fields display a textbox control as the editing interface. It makes more sense to display a dropdownlist control as the editing interface, for "Gender" and "City". We will discuss about doing this in a later video session.

EmployeeId column is the primary key, and it is unusal to change key column values. Notice the HTML of the gridview control. The bound column that displays "EmployeeId" is marked with ReadOnly="True". This is the reason EmployeeId is not editable, when the row is in EDIT mode.

1. Click "Edit" button in a row
2. Delete the value in "Name" column
3. Click "Update"
4. Check the data for this column in the database. 

Notice that NULL is stored. This is because, "BoundField" and "UpdateParameters" has a property "ConvertEmptyStringToNull". This property is true by default. If you want to store an "EmptyString" instead of NULL, set this property to false. 

This property need to be set at both places.
1. On the BoundField and
2. On the respective update parameter