How to Bind, Edit, Delete and Update in Datalist in Asp.net?


Introduction: In this post I will explain you how to bind, edit, delete and update the Datalist data control in Asp.net.
Datalist

Description:
 I have created a table name STUDENT_DETAIL.
STUDENT_ID
int
STUDENT_NAME
varchar(50)
STUDENT_ADDRESS
varchar(50)
STUDENT_CLASS
varchar(50)

STUDENT_ID is primary key.
Now open the Visual Studio>Go to File>New>Website. Add the Connectionstring in web.config file of website.
<configuration>
       <connectionStrings>
    <add name="connection" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>
       </connectionStrings>
       <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>


After that add new web form to website, drag and drop the Datalist Data control from Toolbox.
<asp:DataList ID="dlstudent" runat="server" DataKeyField="STUDENT_ID"
            ondeletecommand="dlstudent_DeleteCommand"
            oneditcommand="dlstudent_EditCommand" oncancelcommand="dlstudent_CancelCommand"
            onupdatecommand="dlstudent_UpdateCommand">
            <HeaderStyle Font-Bold="True" BorderColor="Black" />
            <HeaderTemplate>
             <table border="1"><tr style="background-color:Blue;color:White;">
             <td><b>Student Name</b> </td>
             <td>Student Address</td>
             <td>Student Class</td>
             </tr>
            </HeaderTemplate>
            <ItemTemplate>
            <tr style="font-style:italic;">
              <td align="center"><asp:Label ID="lblname" runat="server"
                    Text='<%# Eval("STUDENT_NAME") %>'></asp:Label></td>
                <td align="center"><asp:Label ID="lbladdress" runat="server"
                    Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label></td>
               <td align="center"><asp:Label ID="lblclass" runat="server"
                    Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label></td>
                    <td><asp:LinkButton ID="lnkedit" Text="Edit" CommandName="edit" runat="server" /></td>
                    <td><asp:LinkButton ID="lnkdelete" Text="Delete" CommandName="Delete" runat="server" /></td></tr>
            </ItemTemplate>
            <EditItemTemplate>
            <table>
            <tr><td><asp:TextBox ID="txtstudentname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:TextBox></td></tr>
          <tr> <td><asp:TextBox ID="txtstudentaddress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:TextBox></td></tr>
         <tr> <td><asp:TextBox ID="txtstudentclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:TextBox></td></tr>
            <td><asp:LinkButton ID="lnkupdate" Text="Update" CommandName="Update" runat="server" /></td>
            <tr><td><asp:LinkButton ID="lnkcancel" Text="Cancel" CommandName="Cancel" runat="server" /></td></tr>
            </table>
            </EditItemTemplate>
                       </asp:DataList>
                                  <table border="1">
             <tr style="color:Red;"><td>
                 <asp:Label ID="lblmessage" runat="server" Text=""></asp:Label></td></tr></table>
Now go to .aspx.cs page and add namespace.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        if (!IsPostBack)
        {
            BindDatalist();
        }
    }
    private void BindDatalist()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Select * from STUDENT_DETAIL", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dlstudent.DataSource = dt;
                dlstudent.DataBind();
            }
            else
            {
                dlstudent.DataSource = null;
                dlstudent.DataBind();
                lblmessage.Text = "No Data Found";
            }
        }
        catch (Exception ex)
        {
        }
    }
    protected void dlstudent_EditCommand(object source, DataListCommandEventArgs e)
    {
        dlstudent.EditItemIndex = e.Item.ItemIndex;
        BindDatalist();
    }
    protected void dlstudent_DeleteCommand(object source, DataListCommandEventArgs e)
    {
        try
        {
            int STUDENT_ID = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex]);
            string delete = "Delete from STUDENT_DETAIL where STUDENT_ID=" + STUDENT_ID;
            SqlCommand cmd = new SqlCommand(delete, con);
            cmd.ExecuteNonQuery();
            BindDatalist();
        }
        catch (Exception ex)
        {
        }
    }
    protected void dlstudent_CancelCommand(object source, DataListCommandEventArgs e)
    {
        dlstudent.EditItemIndex = -1;
        BindDatalist();
    }
    protected void dlstudent_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        try
        {
            int STUDENT_ID = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex]);
            TextBox txtname = (TextBox)e.Item.FindControl("txtstudentname");
            TextBox txtaddress = (TextBox)e.Item.FindControl("txtstudentaddress");
            TextBox txtclass = (TextBox)e.Item.FindControl("txtstudentclass");
            string Update = "Update STUDENT_DETAIL set STUDENT_NAME='" + txtname.Text.Trim() + "',STUDENT_ADDRESS='" + txtaddress.Text.Trim() + "',STUDENT_CLASS='" + txtclass.Text.Trim() + "' where STUDENT_ID =" + STUDENT_ID;
            SqlCommand cmd = new SqlCommand(Update, con);
            cmd.ExecuteNonQuery();
            dlstudent.EditItemIndex = -1;
            BindDatalist();
        }
        catch (Exception ex)
        {
        }
    }

In VB

Add namespace to .aspx.vb page.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        If Not IsPostBack Then
            BindDatalist()
        End If
    End Sub
    Private Sub BindDatalist()
        Dim adp As New SqlDataAdapter("Select * from STUDENT_DETAIL", con)
        Dim dt As New DataTable()
        adp.Fill(dt)
        If dt.Rows.Count > 0 Then
            dlstudent.DataSource = dt
            dlstudent.DataBind()
        Else
            dlstudent.DataSource = Nothing
            dlstudent.DataBind()
            lblmessage.Text = "No Data Found"
        End If
    End Sub

    Protected Sub dlstudent_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dlstudent.UpdateCommand
        Try
            Dim STUDENT_ID As Integer = Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex))
            Dim txtname As TextBox = DirectCast(e.Item.FindControl("txtstudentname"), TextBox)
            Dim txtaddress As TextBox = DirectCast(e.Item.FindControl("txtstudentaddress"), TextBox)
            Dim txtclass As TextBox = DirectCast(e.Item.FindControl("txtstudentclass"), TextBox)
            Dim Update As String = "Update STUDENT_DETAIL set STUDENT_NAME='" & txtname.Text.Trim() & "',STUDENT_ADDRESS='" & txtaddress.Text.Trim() & "',STUDENT_CLASS='" & txtclass.Text.Trim() & "' where STUDENT_ID =" & STUDENT_ID
            Dim cmd As New SqlCommand(Update, con)
            cmd.ExecuteNonQuery()
            dlstudent.EditItemIndex = -1
            BindDatalist()
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub dlstudent_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dlstudent.EditCommand
        dlstudent.EditItemIndex = e.Item.ItemIndex
        BindDatalist()
    End Sub

    Protected Sub dlstudent_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dlstudent.DeleteCommand
        Try
            Dim STUDENT_ID As Integer = Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex))
            Dim delete As String = "Delete from STUDENT_DETAIL where STUDENT_ID=" & STUDENT_ID
            Dim cmd As New SqlCommand(delete, con)
            cmd.ExecuteNonQuery()
            BindDatalist()
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub dlstudent_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dlstudent.CancelCommand
        dlstudent.EditItemIndex = -1
        BindDatalist()
    End Sub

Now debug the project and check the result.


Related Articles on Datalist:

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget