GridView insert update delete in asp.net – Part 23

Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists

Link for text version of this video
http://csharp-video-tutorials.blogspot.com/2013/03/gridview-insert-update-delete-in-aspnet.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

In this video we will discuss about performing an insert, update and delete on asp.net gridview control using sqldatasource. 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 gridview and a sqldatasource 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

Drag and drop gridview control on WebForm1.aspx. 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 Deleting” and “Enable Editing” checkboxes. At this point “Delete” and “Edit” buttons should appear on the gridview control.

We will be using gridview control’s footer for inserting a new record. Set “ShowFooter” property of the GridView1 control to “true”. This can be done from the properties window, or in the HTML.

By default GridView control has generated bound fields to display EmployeeId, Name, Gender and City columns.

We need to convert these bound fields into template fields. This can be done very easily using the designer.
1. On the “GridView Tasks” pane click on “Edit Columns” link button.
2. Select “EmployeeId” from “Selected Fields” section and click on “Convert this field into a template field” link
3. Do the same for Name, Gender and City

Now modify the template fields in the HTML. Please note
1. In every TemplateField, along with EditItemTemplate and ItemTemplate, we also need FooterTemplate.
2. A dropdownlist is used in EditItemTemplate and FooterTemplate of “Gender” template field, instead of a textbox control.
3. To validate data during edit and insert operations, notice that, we are using validation controls, in EditItemTemplate and FooterTemplate.
4. A linkbutton is used in the footer template of “EmployeeId” template field, to enable the user to insert a new employee row
5. We have used ValidationGroup=”Insert” for all the validation controls in FooterTemplates. LinkButton’s ValidationGroup is aslo set to “Insert”, so that all the validation controls in in FooterTemplates are fired only on Insert LinkButton click.
6. We have set LinkButton’s OnClick=”lbInsert_Click”.
7. After the closing tag of GridView, notice that we are using 2 validationsummary controls, to display all the validation messages in one place. ValidationSummary1 control’s ValidationGroup is set to “Insert”. ValidationSummary1 control displays all insert related validation errors, and edit related validation errors are displayed using ValidationSummary2 control.

Finally copy and paste the following event handler method in WebForm1.aspx.cs
protected void lbInsert_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters[“Name”].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl(“txtName”)).Text;
SqlDataSource1.InsertParameters[“Gender”].DefaultValue =
((DropDownList)GridView1.FooterRow.FindControl(“ddlGender”)).SelectedValue;
SqlDataSource1.InsertParameters[“City”].DefaultValue =
((TextBox)GridView1.FooterRow.FindControl(“txtCity”)).Text;
SqlDataSource1.Insert();
}