How to Bind, Edit, Delete and Update in Datalist in Asp.net? - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon