In this article I will explain with an example, how to
Insert,
Update,
Edit and
Delete record in
GridView using
SqlDataSource control in
ASP.Net.
Database
I have made use of the following table Customers with the schema as follow.
![Insert Update Edit Delete record in GridView using SqlDataSource in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The following
HTML Markup consists of:
GridView – For displaying data.
Columns
The
GridView consists of two
BoundField Columns and one
CommandField.
CommandField - For displaying buttons like
Edit,
Delete, or
Select for each row in the
GridView.
TextBox – For capturing user input.
SqlDataSource
SelectCommand – For retrieves Data from Table.
InsertCommand – Insert Value from Table.
UpdateCommand – Update Value from Table.
DeleteCommand – Delete Value from Table.
The
SqlDataSource also has a
InsertParameter,
UpdateParameter,
DeleteParameter. Here
InsertParameter,
UpdateParameter,
DeleteParameter of type
ControlParameter is used as the value of an
ASP.Net control i.e.
TextBox needs to be passed as parameter.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1"
DataKeyNames="CustomerId" OnRowDataBound="OnRowDataBound" EmptyDataText="No records has been added.">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
<asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true"
ItemStyle-Width="100" />
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
<td style="width: 150px">Name:<br />
<asp:TextBox ID="txtName" runat="server" Width="140" />
</td>
<td style="width: 150px">Country:<br />
<asp:TextBox ID="txtCountry" runat="server" Width="140" />
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:constr%>"
SelectCommand="SELECT CustomerId, Name, Country FROM Customers" InsertCommand="INSERT INTO Customers VALUES (@Name, @Country)"
UpdateCommand="UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"
DeleteCommand="DELETE FROM Customers WHERE CustomerId = @CustomerId">
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="txtName" Type="String" />
<asp:ControlParameter Name="Country" ControlID="txtCountry" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Country" Type="String" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
Populating GridView from Database
1. Define and set the Connection String to the database in
Web.Config file.
<connectionStrings>
<add name= "constr" connectionString= "Data Source=.\SQLExpress;Initial Catalog=Samples;integrated security=true" />
</connectionStrings>
2. Apply the Connection String to
SqlDataSource. Here constr is the name of the Connection String setting in
Web.Config file.
ConnectionString="<%$ConnectionStrings:constr %>"
DataSourceID="SqlDataSource1"
4. Set the Select Command.
SelectCommand="SELECT CustomerId, Name, Country FROM Customers"
Following is the GridView containing records.
![Insert Update Edit Delete record in GridView using SqlDataSource in ASP.Net]()
For the GridView I have set EmptyDataText to display message when no records are present.
![Insert Update Edit Delete record in GridView using SqlDataSource in ASP.Net]()
Inserting records to GridView using SqlDataSource
1. Set the InsertCommand.
InsertCommand="INSERT INTO Customers VALUES (@Name, @Country)"
2. Set the Insert Parameters. ControlParameters are used in order to get the Parameter values directly from the TextBoxes. The ControlID property is set to the ID of the respective TextBoxes.
For example, the value of @Name Parameter is fetched from
txtName TextBox.
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="txtName" Type="String" />
<asp:ControlParameter Name="Country" ControlID="txtCountry" Type="String" />
</InsertParameters>
3. There’s a Click event handler assigned to the
Add Button. When the Button is clicked, the Insert function of the
SqlDataSource is executed which then inserts the records to the database and binds the
GridView again to display the newly inserted record.
C#
protected void Insert(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
VB.Net
Protected Sub Insert(sender As Object, e As EventArgs)
SqlDataSource1.Insert()
End Sub
Editing and Updating GridView records using SqlDataSource
1. Set the UpdateCommand. Here you simply need to set parameter names same as their corresponding column fields.
For example, if the column name is Name its corresponding parameter name will be @Name. Once this is done the
SqlDataSource will automatically pick values from respective TextBoxes inside the
GridView row to be edited.
UpdateCommand="UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId"
2. Set the Update Parameters with name and data type.
<UpdateParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Country" Type="String" />
</UpdateParameters>
Updating GridView records
![Insert Update Edit Delete record in GridView using SqlDataSource in ASP.Net]()
Deleting GridView records using SqlDataSource
1. Set the DeleteCommand.
DeleteCommand="DELETE FROM Customers WHERE CustomerId = @CustomerId">
2. Set the Delete Parameters with name and data type. Here the
CustomerId field will automatically be picked from the
DataKeyNames property of the
GridView row to be deleted.
<DeleteParameters>
<asp:Parameter Name="CustomerId" Type="Int32" />
</DeleteParameters>
3. In order to display a Confirmation Message when deleting row, the
Delete Button is first reference in the
OnRowDataBound event handler and a
JavaScript Confirm function is set to its Client Side Click event handler.
C#
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && gvCustomers.EditIndex != e.Row.RowIndex)
{
(e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
}
}
VB.Net
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow AndAlso gvCustomers.EditIndex <> e.Row.RowIndex Then
TryCast(e.Row.Cells(2).Controls(2), LinkButton).Attributes("onclick") = "return confirm('Do you want to delete this row?');"
End If
End Sub
Deleting GridView records
![Insert Update Edit Delete record in GridView using SqlDataSource in ASP.Net]()
Downloads
