Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

GridView insert update delete in asp.net - Part 23

Suggested Videos 
Part 20 - Keep gridview in edit mode when update fails due to data conflict 
Part 21 - GridView TemplateField in asp.net
Part 22 - Using validation controls with asp.net gridview when editing data
GridView insert update delete in asp.net



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
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1" 
                ShowFooter="true">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="Name" HeaderText="Name" 
            SortExpression="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" 
            SortExpression="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" 
            SortExpression="City" />
    </Columns>
</asp:GridView>

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
Convert boundfield to templatefield in gridview

Now modify the template fields in the HTML, as shown below. 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.

Here is the complete HTML
<div style="font-family: Arial">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
              DataKeyNames="EmployeeId" DataSourceID="SqlDataSource1" 
              ShowFooter="True" BackColor="#DEBA84" BorderColor="#DEBA84"
              BorderStyle="None" BorderWidth="1px" CellPadding="3" 
              CellSpacing="2">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:TemplateField HeaderText="EmployeeId" InsertVisible="False" 
                           SortExpression="EmployeeId">
            <EditItemTemplate>
                <asp:Label ID="Label1" runat="server" 
                           Text='<%# Eval("EmployeeId") %>'>
                </asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" 
                           Text='<%# Bind("EmployeeId") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:LinkButton ID="lbInsert" ValidationGroup="Insert" 
                    runat="server" OnClick="lbInsert_Click">Insert
                </asp:LinkButton>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name" SortExpression="Name">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox1" runat="server" 
                             Text='<%# Bind("Name") %>'>
                </asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEditName" runat="server" 
                    ErrorMessage="Name is a required field"
                    ControlToValidate="TextBox1" Text="*" ForeColor="Red">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" 
                    Text='<%# Bind("Name") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvInsertName" runat="server" 
                    ErrorMessage="Name is a required field" 
                    ControlToValidate="txtName" Text="*" ForeColor="Red"
                    ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Gender" SortExpression="Gender">
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownList1" runat="server" 
                    SelectedValue='<%# Bind("Gender") %>'>
                    <asp:ListItem>Select Gender</asp:ListItem>
                    <asp:ListItem>Male</asp:ListItem>
                    <asp:ListItem>Female</asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvEditGender" runat="server" 
                    ErrorMessage="Gender is a required field" Text="*"
                    ControlToValidate="DropDownList1" ForeColor="Red" 
                    InitialValue="Select Gender">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" 
                    Text='<%# Bind("Gender") %>'>
                </asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="ddlInsertGender" runat="server">
                    <asp:ListItem>Select Gender</asp:ListItem>
                    <asp:ListItem>Male</asp:ListItem>
                    <asp:ListItem>Female</asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="rfvInsertGender" runat="server" 
                    ErrorMessage="Gender is a required field" Text="*"
                    ControlToValidate="ddlInsertGender" ForeColor="Red" 
                    InitialValue="Select Gender" ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City" SortExpression="City">
            <EditItemTemplate>
                <asp:TextBox ID="TextBox3" runat="server" 
                    Text='<%# Bind("City") %>'></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvEditCity" runat="server" 
                    ErrorMessage="City is a required field" Text="*"
                    ControlToValidate="TextBox3" ForeColor="Red">
                </asp:RequiredFieldValidator>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label4" runat="server" 
                    Text='<%# Bind("City") %>'></asp:Label>
            </ItemTemplate>
            <FooterTemplate>
                <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                <asp:RequiredFieldValidator ID="rfvInsertCity" runat="server" 
                    ErrorMessage="City is a required field" Text="*" 
                    ControlToValidate="txtCity" ForeColor="Red" 
                    ValidationGroup="Insert">
                </asp:RequiredFieldValidator>
            </FooterTemplate>
        </asp:TemplateField>
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ValidationSummary ID="ValidationSummary1" ValidationGroup="Insert" 
    ForeColor="Red" runat="server" />
<asp:ValidationSummary ID="ValidationSummary2" ForeColor="Red" 
    runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>"
    DeleteCommand="DELETE FROM [tblEmployee] WHERE [EmployeeId] = @EmployeeId" 
    InsertCommand="INSERT INTO [tblEmployee] ([Name], [Gender], [City]) 
        VALUES (@Name, @Gender, @City)"
    SelectCommand="SELECT * FROM [tblEmployee]" 
    UpdateCommand="UPDATE [tblEmployee] SET [Name] = @Name, [Gender] = @Gender,
        [City] = @City WHERE [EmployeeId] = @EmployeeId">
    <DeleteParameters>
        <asp:Parameter Name="EmployeeId" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="Name" Type="String" />
        <asp:Parameter Name="Gender" Type="String" />
        <asp:Parameter Name="City" Type="String" />
        <asp:Parameter Name="EmployeeId" Type="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>
</div>

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("ddlInsertGender")).SelectedValue;
    SqlDataSource1.InsertParameters["City"].DefaultValue = 
        ((TextBox)GridView1.FooterRow.FindControl("txtCity")).Text;
    SqlDataSource1.Insert();
}

15 comments:

  1. Hi sir,
    When i worked on GridView Insert,Update and Delete using SQLDataSource, after adding the Validation field elements i got the error. I used the above code itself.
    I am using Visual Studio 2012.
    The error is as follows:
    Server Error in '/' Application.
    WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for 'jquery'. Please add a ScriptResourceMapping named jquery(case-sensitive).

    Can you please help on this?

    Thanks,
    Rajiv

    ReplyDelete
  2. Rajiv,
    I ran into the same error too because I used VS 2012. You need to install the following Nuget packages:

    jQuery
    ASPNET.ScriptManager.jQuery
    Microsoft.AspNet.ScriptManager.MSAjax
    Microsoft.AspNet.ScriptManager.WebForms

    This was documented under this blog:
    http://jupaol.blogspot.mx/2012/09/enabling-unobtrusive-validation-from.html

    I hope this helps.

    Beginner ASP.NET 2012

    ReplyDelete
  3. I really like the way u teach very clear and very helpful thank you so much.
    can u help me on this
    I have tried to use a grid-view to edit update delete and insert to a database as u put in the tutorial but in my case i need to use a tow drop-dawn box inside the grid-view the first drop dawn list refers to the other as a pass parameter to display the filtered result based on the drop-dawn selection like continent and country in the insert template but when i tried to give a parameter value for the dropdawn box the only control available in the wizard is the Gridview1 not the drop-down box can u pls help me.

    ReplyDelete
  4. I am getting the following error

    Server Error in '/' Application
    An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Please help me

    ReplyDelete
  5. Hello sir,
    can you tell me how to get lbInsert_Click event directly?

    pls help ..
    thanks in advance

    ReplyDelete
  6. Hello Sir,
    i got Error at the time to Edit Button Press
    Server Error in '/' Application.
    'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.
    Parameter name: value

    ReplyDelete
    Replies
    1. Delete selected value property from the grid view....

      Delete
    2. delete SelectedValue property from the dropdown....

      Delete
  7. While Edit Row getting an error due to below line of code

    SelectedValue='<%# Bind("Gender") %>


    'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.
    Parameter name: value

    ReplyDelete
  8. I am getting the error as "'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.
    Parameter name: value ". If I remove the SelectedValue then the row is not getting updated. Please suggest..

    ReplyDelete
  9. Thank you so much for your great effort.
    If there are no rows you cant add a first one because the footer row wont show on empty data. how to solve this?

    ReplyDelete
  10. DataList insert update and delete i want

    ReplyDelete
  11. This is wonderful, thanks so much for sharing.

    ReplyDelete

It would be great if you can help share these free resources