Update and Delete record in database using ASP.Net DataList in C# and VB.Net

Hi micah,

Check this example. Now please take its reference and correct your code.

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:DataList ID="dlCustomers" runat="server">
    <HeaderTemplate>
        <table>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Country</th>
                <th></th>
                <th></th>
            </tr>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td><asp:Label ID="lblId" Text='<%#Eval("CustomerId") %>' runat="server" ></asp:Label></td>
            <td><asp:Label ID="lblName" Text='<%#Eval("Name") %>' runat="server" ></asp:Label></td>
            <td><asp:Label ID="lblCountry" Text='<%#Eval("Country") %>' runat="server" ></asp:Label></td>
            <td><asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" OnClick="OnEdit" ></asp:LinkButton></td>
            <td>
                <asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" OnClick="OnDelete"
                    OnClientClick="return confirm('Are you sure?')"></asp:LinkButton>
            </td>
        </tr>
    </ItemTemplate>
    <EditItemTemplate>
        <tr>
            <td><asp:Label ID="lblId" Text='<%#Eval("CustomerId") %>' runat="server" ></asp:Label></td>
            <td><asp:TextBox ID="txtName" runat="server" Width="120" Text='<%# Eval("Name") %>' ></asp:TextBox></td>
            <td><asp:TextBox ID="txtCountry" runat="server" Width="120" Text='<%# Eval("Country") %>' ></asp:TextBox></td>
            <td><asp:LinkButton ID="lnkUpdate" runat="server" Text="Update" OnClick="OnUpdate" ></asp:LinkButton></td>
            <td><asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" OnClick="OnCancel" ></asp:LinkButton></td>
        </tr>
    </EditItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:DataList>

Namespaces

C#

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

VB.Net

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

Code

C#

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

protected void OnDelete(object sender, EventArgs e)
{
    DataListItem item = (sender as LinkButton).NamingContainer as DataListItem;
    int id = Convert.ToInt32((item.FindControl("lblId") as Label).Text.Trim());
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "DELETE FROM Customers WHERE CustomerId = @Id";
    cmd.Parameters.AddWithValue("@Id", id);
    InsertUpdateDelete(cmd);
    this.BindDatalist();
}

protected void OnEdit(object sender, EventArgs e)
{
    DataListItem item = (sender as LinkButton).NamingContainer as DataListItem;
    dlCustomers.EditItemIndex = item.ItemIndex;
    this.BindDatalist();
}

protected void OnUpdate(object sender, EventArgs e)
{
    DataListItem item = (sender as LinkButton).NamingContainer as DataListItem;
    int id = Convert.ToInt32((item.FindControl("lblId") as Label).Text.Trim());
    string name = (item.FindControl("txtName") as TextBox).Text;
    string country = (item.FindControl("txtCountry") as TextBox).Text;
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @Id";
    cmd.Parameters.AddWithValue("@Id", id);
    cmd.Parameters.AddWithValue("@Name", name);
    cmd.Parameters.AddWithValue("@Country", country);
    InsertUpdateDelete(cmd);
    dlCustomers.EditItemIndex = -1;
    this.BindDatalist();
}

protected void OnCancel(object sender, EventArgs e)
{
    dlCustomers.EditItemIndex = -1;
    this.BindDatalist();
}

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

private void InsertUpdateDelete(SqlCommand cmd)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

VB.Net

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

Protected Sub OnDelete(ByVal sender As Object, ByVal e As EventArgs)
    Dim item As DataListItem = TryCast((TryCast(sender, LinkButton)).NamingContainer, DataListItem)
    Dim id As Integer = Convert.ToInt32((TryCast(item.FindControl("lblId"), Label)).Text.Trim())
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.CommandText = "DELETE FROM Customers WHERE CustomerId = @Id"
    cmd.Parameters.AddWithValue("@Id", id)
    InsertUpdateDelete(cmd)
    Me.BindDatalist()
End Sub

Protected Sub OnEdit(ByVal sender As Object, ByVal e As EventArgs)
    Dim item As DataListItem = TryCast((TryCast(sender, LinkButton)).NamingContainer, DataListItem)
    dlCustomers.EditItemIndex = item.ItemIndex
    Me.BindDatalist()
End Sub

Protected Sub OnUpdate(ByVal sender As Object, ByVal e As EventArgs)
    Dim item As DataListItem = TryCast((TryCast(sender, LinkButton)).NamingContainer, DataListItem)
    Dim id As Integer = Convert.ToInt32((TryCast(item.FindControl("lblId"), Label)).Text.Trim())
    Dim name As String = TryCast(item.FindControl("txtName"), TextBox).Text
    Dim country As String = TryCast(item.FindControl("txtCountry"), TextBox).Text
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.CommandText = "UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @Id"
    cmd.Parameters.AddWithValue("@Id", id)
    cmd.Parameters.AddWithValue("@Name", name)
    cmd.Parameters.AddWithValue("@Country", country)
    InsertUpdateDelete(cmd)
    dlCustomers.EditItemIndex = -1
    Me.BindDatalist()
End Sub

Protected Sub OnCancel(ByVal sender As Object, ByVal e As EventArgs)
    dlCustomers.EditItemIndex = -1
    Me.BindDatalist()
End Sub

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

Private Sub InsertUpdateDelete(ByVal cmd As SqlCommand)
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Sub

Screenshot