Insert Update Edit Delete record in GridView using SqlDataSource ASP.NET

In this example i am going to describe how to Insert record or edit or delete record in GridView 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

		add to del.icio.us saved by 0 users

If you like this post than Join or Follow this site