Insert Update Edit Delete Rows Record In GridView

In this example i am explaining how to Insert Update Edit Delete Records Rows In GridView With SqlDataSource Using C# VB.NET Asp.Net using SqlDataSource.

For inserting record, i've put textboxes in footer row of GridView using ItemTemplate and FooterTemaplete.

Go to design view of aspx page and drag a GridView control from toolbox, click on smart tag of GridView and choose new datasource

Select Database and click Ok

 

In next screen, Enter your SqlServer name , username and password and pick Database name from the dropdown , Test the connection

 

 

 In next screen, select the table name and fields , Click on Advance tab and check Generate Insert,Edit and Delete statements checkbox , alternatively you can specify your custom sql statements 

 

 

Click on ok to finish 

Check Enable Editing , enable deleting checkbox in gridView smart tag 

Now go to html source of page and define DatakeyNames field in gridview source

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataKeyNames="ID"
              DataSourceID="SqlDataSource1" 
              OnRowDeleted="GridView1_RowDeleted" 
              OnRowUpdated="GridView1_RowUpdated" 
              ShowFooter="true" 
              OnRowCommand="GridView1_RowCommand">
</asp:GridView>

Remove the boundFields and put ItemTemplate and EditItemTemplate and labels and textboxs respectively, complete html source of page should look like this

<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataKeyNames="ID"
              DataSourceID="SqlDataSource1" 
              OnRowDeleted="GridView1_RowDeleted" 
              OnRowUpdated="GridView1_RowUpdated" 
              ShowFooter="true" 
              OnRowCommand="GridView1_RowCommand">
<Columns>
    <asp:CommandField ShowDeleteButton="True" 
                      ShowEditButton="True" />
    <asp:TemplateField HeaderText="ID" SortExpression="ID">
    <ItemTemplate>
    <asp:Label ID="lblID" runat="server" 
                          Text='<%#Eval("ID") %>'>
    </asp:Label>
    </ItemTemplate>
    <FooterTemplate>
    <asp:Button ID="btnInsert" runat="server" 
                Text="Insert" CommandName="Add" />
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="FirstName" 
                       SortExpression="FirstName">
    <ItemTemplate>
    <asp:Label ID="lblFirstName" runat="server" 
               Text='<%#Eval("FirstName") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtFirstName" runat="server" 
                 Text='<%#Bind("FirstName") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtFname" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="LastName" 
                       SortExpression="LastName">
    <ItemTemplate>
    <asp:Label ID="lblLastName" runat="server" 
               Text='<%#Eval("LastName") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtLastName" runat="server" 
                 Text='<%#Bind("LastName") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtLname" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="Department" 
                       SortExpression="Department">
    <ItemTemplate>
    <asp:Label ID="lblDepartment" runat="server" 
               Text='<%#Eval("Department") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtDepartmentName" runat="server" 
                 Text='<%#Bind("Department") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtDept" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
    
    <asp:TemplateField HeaderText="Location" 
                       SortExpression="Location">
    <ItemTemplate>
    <asp:Label ID="lblLocation" runat="server" 
               Text='<%#Eval("Location") %>'>
    </asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtLocation" runat="server" 
                 Text='<%#Bind("Location") %>'>
    </asp:TextBox>
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox ID="txtLoc" runat="server">
    </asp:TextBox>
    </FooterTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:DBConString%>"
DeleteCommand="DELETE FROM [Employees] WHERE [ID] = @ID" 
InsertCommand="INSERT INTO [Employees] ([FirstName], 
[LastName],[Department], [Location]) 
VALUES (@FirstName, @LastName, @Department, @Location)"
SelectCommand="SELECT [ID], [FirstName], [LastName], 
[Department], [Location] FROM [Employees]"
UpdateCommand="UPDATE [Employees] SET 
[FirstName] = @FirstName, [LastName] = @LastName, 
[Department] = @Department, [Location] = @Location 
WHERE [ID] = @ID" OnInserted="SqlDataSource1_Inserted">

<DeleteParameters>
    <asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
    <asp:Parameter Name="FirstName" Type="String" />
    <asp:Parameter Name="LastName" Type="String" />
    <asp:Parameter Name="Department" Type="String" />
    <asp:Parameter Name="Location" Type="String" />
    <asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
    <asp:Parameter Name="FirstName" Type="String" />
    <asp:Parameter Name="LastName" Type="String" />
    <asp:Parameter Name="Department" Type="String" />
    <asp:Parameter Name="Location" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:Label ID="lblMessage" runat="server" 
           Font-Bold="True"></asp:Label><br />
</div>
</form>

Write this code in RowCommand Event of GridView in codebehind
C# code Behind

protected void GridView1_RowCommand
(object sender, GridViewCommandEventArgs e)
{
  if (e.CommandName == "Add")
  {
   string strFirstName = ((TextBox)
   GridView1.FooterRow.FindControl("txtFname")).Text;

   string strLastName = 
   ((TextBox)GridView1.FooterRow.FindControl
                         ("txtLname")).Text;

   string strDepartment = 
   ((TextBox)GridView1.FooterRow.FindControl
                            ("txtDept")).Text;
   string strLocation = ((TextBox)GridView1.FooterRow.
                          FindControl("txtLoc")).Text;
   //SqlDataSource1.InsertParameters.Clear();
   //SqlDataSource1.InsertParameters.Add
                       //("FirstName", strFirstName);
   //SqlDataSource1.InsertParameters.Add
                          //("LastName", strLastName);
   //SqlDataSource1.InsertParameters.Add
                         //("Department", strDepartment);
   //SqlDataSource1.InsertParameters.Add
                              //("Location", strLocation);

  SqlDataSource1.InsertParameters["FirstName"].DefaultValue 
                                             = strFirstName;
  SqlDataSource1.InsertParameters["LastName"].DefaultValue 
                                             = strLastName;
  SqlDataSource1.InsertParameters["Department"].DefaultValue 
                                             = strDepartment;
  SqlDataSource1.InsertParameters["Location"].DefaultValue
                                            = strLocation;
  SqlDataSource1.Insert();
  }
}

VB.NET Code Behind

Protected Sub GridView1_RowCommand(ByVal sender As Object, 
                       ByVal e As GridViewCommandEventArgs)
        If e.CommandName = "Add" Then
            Dim strFirstName As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtFname"), TextBox).Text()

            Dim strLastName As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtLname"), TextBox).Text()

            Dim strDepartment As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtDept"), TextBox).Text()
            Dim strLocation As String = 
            DirectCast(GridView1.FooterRow.
            FindControl("txtLoc"), TextBox).Text()

            'SqlDataSource1.InsertParameters.Clear();
            'SqlDataSource1.InsertParameters.Add
            '("FirstName", strFirstName);
            'SqlDataSource1.InsertParameters.Add
            '("LastName", strLastName);
            'SqlDataSource1.InsertParameters.Add
            '("Department", strDepartment);
            'SqlDataSource1.InsertParameters.Add
            '("Location", strLocation);

            SqlDataSource1.InsertParameters("FirstName").
            DefaultValue = strFirstName
            SqlDataSource1.InsertParameters("LastName").
            DefaultValue = strLastName
            SqlDataSource1.InsertParameters("Department").
            DefaultValue = strDepartment
            SqlDataSource1.InsertParameters("Location").
            DefaultValue = strLocation
            SqlDataSource1.Insert()
        End If
    End Sub

Download the sample code attached


You would also like to read
Edit or update multiple records/rows in gridview with checkbox using C# in ASP.NET

Delete multiple rows records in Gridview with checkbox and confirmation in ASP.NET