Edit, Update and Delete records using GridView and SqlDataSource in Asp.Net

Here in this article, I am going to show you step-by-step, how to edit, update and delete records in a database using GridView and SqLDataSource control in Asp.Net.

The database, that I'll be using here is SQL Server. I'll bind the GridView control to an SQL Server table, using SqlDataSource.

Create Table in SQL Server

I am assuming, you have SQL Server installed in your computer.

We'll create a table in SQL Server. The table is dbo.EmployeeDetails. You can find the "create table" script along with the dummy data here.

Create a webpage in Asp.Net

Open Microsoft Visual Studio and select File -> New -> Web Site from the top menu. Add a new website. Follow these steps to add the controls.

1) In the "default.aspx" page, add GridView and SqlDataSource controls. These controls are in the Toolbox. Open "Toolbox". You can find the "toolbox icon" in icon bar or press "Ctrl+Alt+X" buttons together.

2) In the "Toolbox", find the "Data" tab and click it. This will open a list of data controls. Find "GridView" and SqlDataSource, drag and drop the controls in Default.aspx page.

gridview and sqldatasource in asp.net

3) Now we need to attach GridView control to SqlDataSource, so we can show employee details in the grid. In "default.aspx" page, select the "Design" tab. Its at the left bottom. You will see the GridView and SqlDataSource control.

4) Click SqlDataSource, you will see a small right "arrow". Click it and select Configure Data Source.

configure sqldatasource in asp.net

5) In the "Configure Data Source" window, click New Connection button. In the "Add Connection" window, you'll have to set a connection to SQL Server database. Select Server name (the data source) and the database to connect.

6) Click the "Test Connection" button to check if it has connected to the specified database. Click OK.

7) It will bring you back to "Configure Data Source" window. Click Next. Select "EmployeeDetails" table and choose the columns you want the GridView. Columns like, EmpID, EmpName, Mobile and Email.

At this stage, the SqlDataSource control in your web page will look like this. It only has the SelectCommand property with the "select" query.

<div>
    <asp:GridView ID="GridView1" runat="server"></asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
        SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]">

    </asp:SqlDataSource>
</div>

➡️ bind sqldatasource to a repeater control

8) Now let's attach SqlDataSource to the GridView control. Again, click the "Design" tab to go to the design mode.

9) Click the grid and it will show a small "right arrow". Click it and choose the Data Source, SqlDataSource1. You can select all the options like "Enable paging", sorting etc.

10) Come back to "Source" window and you will see that the "GridView" control has the data source and the columns.

<div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="EmpID" HeaderText="EmpID" SortExpression="EmpID" />
            <asp:BoundField DataField="EmpName" HeaderText="EmpName" SortExpression="EmpName" />
            <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
        </Columns>
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
        SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]">

    </asp:SqlDataSource>
</div>

Run the application. It should show employee details in the grid.

What we have done till now is, we have dragged and dropped a GridView control along with SqlDataSource control. We then set a connection to an SQL Sever database table using SqlDataSource. And finally, attached the GridView to SqlDataSource.

Now, let's see how to add Edit, Update and Delete options to the GridView, so we can do some manipulation to the table.

GridView Edit option

To make the GridView data editable, we need to add few properties to the GridView control.

Add AutoGenerateEditButton and DataKeyNames properties to the GridView.

I have highlighted the attributes.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AutoGenerateColumns="False" 
    DataSourceID="SqlDataSource1"
    AutoGenerateEditButton="true"
    DataKeyNames="EmpID">

    <Columns>
        <asp:BoundField DataField="EmpID" HeaderText="EmpID" SortExpression="EmpID" />
        <asp:BoundField DataField="EmpName" HeaderText="EmpName" SortExpression="EmpName" />
        <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
        <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
    SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]">

</asp:SqlDataSource>

Run the application.

You'll see that the grid now has the Edit button in the first column. Clicking the "Edit" button (its a link button) in any row, will show you the Update Cancel buttons.

edit update option in gridview

You can "Cancel" editing. However, at this stage you cannot update the record. It will throw an error.

Server Error in '/vb' Application.
Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.

We'll have to explicitly add the "update" command.

GridView Update option

In <asp:SqlDataSource />, add the "UpdateCommand" property, which will have an SQL "Update" command or a procedure to update the selected record. Add two parameters (or fields) inside <UpdateParameters> property.

I have highlighted the attribute.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
        SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]"
        UpdateCommand="Update dbo.EmployeeDetails SET Mobile = @Mobile, Email = @Email 
            WHERE EmpID = @EmpId">
</asp:SqlDataSource>

Run the application. Click the "Edit" button in any row, it will show row data in textboxes. Change the details and click the "Update" link button.

Limit Edit/Update of columns

I am sure you do not want to change the "ID" of a particular record, since it is a unique id. Therefore, you can limit the number of columns or fields you want to edit.

To do this, add ReadOnly = "true" attribute (or simply ReadOnly) to the "Columns" in GridView you want to restrict. For example,

I have highlighted the attributes.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AutoGenerateColumns="False" 
    DataSourceID="SqlDataSource1"
    AutoGenerateEditButton="true"
    DataKeyNames="EmpID">

    <Columns>
        <asp:BoundField DataField="EmpID" HeaderText="EmpID" SortExpression="EmpID" ReadOnly />
        <asp:BoundField DataField="EmpName" HeaderText="EmpName" SortExpression="EmpName" ReadOnly />
        <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
        <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
    </Columns>
</asp:GridView>

Run the application. Click the "Edit" button you will see that the last two columns are only editable.

GridView Delete option

You can delete records in a database table from the GridView control. All you have to do is, add AutoGenerateDeleteButton attribute and set its value as "true".

I have highlighted the attribute.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource1"
        AutoGenerateEditButton="true"
        AutoGenerateDeleteButton="true"
        DataKeyNames="EmpID">

In <asp:SqlDataSource>, add the "DeleteCommand" property.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
        SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]"
        UpdateCommand="Update dbo.EmployeeDetails SET Mobile = @Mobile, Email = @Email 
            WHERE EmpID = @EmpId"
        DeleteCommand="DELETE FROM EmployeeDetails WHERE EmpID = @EmpID">

Run the application. You will see a "Delete" link button is added along with "Edit" button. Clicking the "Delete" button will remove the record from the employee details table, "with out any warning".

The Complete Code

The markup with scripts looks like this. We have the GridView control with the datasource attached to SqlDataSource control. Each element has a set of unique attributes.

<div>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource1"
        AutoGenerateEditButton="true"
        AutoGenerateDeleteButton="true"
        DataKeyNames="EmpID">

        <Columns>
            <asp:BoundField DataField="EmpID" HeaderText="EmpID" SortExpression="EmpID" ReadOnly />
            <asp:BoundField DataField="EmpName" HeaderText="EmpName" SortExpression="EmpName" ReadOnly />
            <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
        </Columns>

    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
        SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]"
        UpdateCommand="Update dbo.EmployeeDetails SET Mobile = @Mobile, Email = @Email 
            WHERE EmpID = @EmpId"
        DeleteCommand="DELETE FROM EmployeeDetails WHERE EmpID = @EmpID">

    </asp:SqlDataSource>
</div>

➡️ Bind SQL Server stored procedure to SqlDataSource and GridView controls

Confirm before Delete records from GridView

This is important.

The delete command in the above code, will delete the record without any warning. How about setting an option, where you get a warning before it removes the record from the database table. Yes, it is doable.

In the above code, I have added AutoGenerateDeleteButton attribute in the GridView. Remove it or set the value as "false". We do not want an "automatic" delete button here. We'll add a button, which will prompt a message, a confirmation like, Yes or No, before deleting the data.

In the "Column" section of the GridView, add a <asp:LinkButton /> control. This control will have a "User Defined" script (for confirmation). It will pop-up a message, asking for permission to erase the data.

The code looks like this. I have highlighted the properties.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
    AutoGenerateColumns="False" 
    DataSourceID="SqlDataSource1"
    AutoGenerateEditButton="true"
    AutoGenerateDeleteButton="false"     <!--do not generate delete button automatically. you can remove this attribute.-->
    DataKeyNames="EmpID">

    <Columns>
        <asp:BoundField DataField="EmpID" HeaderText="EmpID" SortExpression="EmpID" ReadOnly />
        <asp:BoundField DataField="EmpName" HeaderText="EmpName" SortExpression="EmpName" ReadOnly />
        <asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" />
        <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />

        <asp:TemplateField>
            <ItemTemplate>
                 <!--add a link button to delete record after a confirmation.-->
                <asp:LinkButton ID="LinkButton1" Runat="server" 
                    OnClientClick="return confirm('Are you sure you?');" 
                    CommandName="Delete">Delete
                </asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>

    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ENCODEDNAConnectionString5 %>" 
    SelectCommand="SELECT [EmpID], [EmpName], [Mobile], [Email] FROM [EmployeeDetails]"
    UpdateCommand="Update dbo.EmployeeDetails SET Mobile = @Mobile, Email = @Email 
        WHERE EmpID = @EmpId"
    DeleteCommand="DELETE FROM EmployeeDetails WHERE EmpID = @EmpID">

</asp:SqlDataSource>

Run the application. You will see, a "Delete" link button is added in the last column. Clicking the Delete button will open a dialog box (or a message box) asking for a confirmation.

That's it. Let me know if you have any doubt.

← PreviousNext →