ERROR: Delete code deletes entire data from database table

Hi RichardSa,

You are passing wrong value(int i = 0;) for deleting records from database. Please pass value of GridView Cell in a Parameters.

I have corrected your code please refer below sample.

Database

I have made use of the following table Customers with the schema as follows. 

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkCustomers" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<hr />
<asp:Button Text="Delete" ID="btnDelete" runat="server" OnClick="Delete" />

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGrid();
    }
}

protected void Delete(object sender, EventArgs e)
{
    foreach (GridViewRow row in gvCustomers.Rows)
    {
        CheckBox chkdelete = (CheckBox)row.FindControl("chkCustomers");
        if (chkdelete.Checked)
        {
            string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId=@Id", con))
                {
                    cmd.Parameters.AddWithValue("@Id", row.Cells[1].Text);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
            string message = "Customer " + row.Cells[1].Text + " has been deleted.";
            ClientScript.RegisterStartupScript(this.GetType(), "alert" , "alert('" + message + "');", true);
        }
    }

    this.BindGrid();
}

private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGrid()
    End If
End Sub

Protected Sub Delete(ByVal sender As Object, ByVal e As EventArgs)
    For Each row As GridViewRow In gvCustomers.Rows
        Dim chkdelete As CheckBox = CType(row.FindControl("chkCustomers"), CheckBox)
        If chkdelete.Checked Then
            Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As SqlConnection = New SqlConnection(conString)
                Using cmd As SqlCommand = New SqlCommand("DELETE FROM Customers WHERE CustomerId=@Id", con)
                    cmd.Parameters.AddWithValue("@Id", row.Cells(1).Text)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
            Dim message As String = "Customer " & row.Cells(1).Text & " has been deleted."
            ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('" & message & "');", True)
        End If
    Next

    Me.BindGrid()
End Sub

Private Sub BindGrid()
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name, Country FROM Customers", con)
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

Screenshot

RichardSa says:

In my Gridview, I have a column named "receipt_no" as DataKeyNames. What if use that, will it still delete the row in the database based on the column name which is not the Primary Key?

Yes it is possible! You can apply the where condition on that column base.