How to Bind,save,edit,update,delete records from DataList in asp.net (C#,VB.Net)

How to Bind,save,edit,update,delete records from DataList in asp.net (C#,VB.Net)

Introduction: In this article i am going to explain with example How to bind, save,  edit, update, cancel and delete records/items/data from DataList in asp.net with both the C# and VB.Net language and using stored procedures and Sql server as a back end database.

Bind, save, edit, update, delete example in DataList in asp.net
Click on the image to enlarge
Description: Basically you will learn the following through this article.
  • How to perform the operations like Bind, save, edit, update, cancel and delete data from DataList data control using stored procedures and Sql server as back end database
  • How to upload image file through file upload control and show book image in DataList control.
In previous articles i explained How to Bind DataList using SqlDataAdapter and DataTable in asp.net and Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and How to bind Repeater data control and Bind, edit, update and delete data in Repeater and Upload,download,delete image files from the GridView and create crystal reports in visual studio 2010

Implementation: Let's create an asp.net application to see the DataList's  operations in action.
  • First of all create a database in Sql server e.g.  "Book_DB" and create a table with the columns and data type as shown below and name it "Book_Tb".

Column Name
Data Type
Book_ID
Int(Primary Key. So set Is identity=true)
Title
varchar(100)
Author
varchar(100)
Publisher
varchar(100)
Price
Int
ImagePath
varchar(500)

Now we will create stored procedures to bind , save, update and delete the book records from the Sql server database table.
  • Create a stored procedure to save the Book records.
CREATE PROCEDURE SaveBookRecords_Sp
                (
                                @Title                  varchar(100),
                                @Author                             varchar(100),
                                @Publisher  varchar(100),
                                @Price     int,
                                @ImagePath     varchar(500)
                )             
AS
BEGIN
                insert into tbBook (Title,Author,Publisher,Price,ImagePath)
    values (@Title,@Author,@Publisher,@Price,@ImagePath)
END

  • Create a stored procedure to fetch the Book records and bind in DataList
CREATE PROCEDURE BindBookRecords_Sp
AS
BEGIN
                select * from tbBook
END

  • Create a stored procedure to update the book records.
CREATE PROCEDURE UpdateBookRecords_Sp
                (
                                @Book_ID          int,
                                @Title                  varchar(100),
                                @Author                             varchar(100),
                                @Publisher  varchar(100),
                                @Price     int                     
                )             
AS
BEGIN
                update tbBook set Title=@Title,Author=@Author,Publisher=@Publisher,Price=@Price
    where Book_ID=@Book_ID
END

  • Create a stored procedure to delete the book records. 
CREATE PROCEDURE DeleteBookRecords_Sp
                (
                                @Book_ID          int
                )             
AS
BEGIN
                delete from tbBook where Book_Id=@Book_ID
END
  • Now in the web.config file create the connection string to connect the asp.net application with the Sql server database.
<connectionStrings>
    <add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=Book_DB;Integrated Security=True"/>
  </connectionStrings>

Note: Replace the Data Source and Initial catalog (i.e. Database name) as per your application.
  • Now in the <Body> tag of the design page e.g. default.aspx, design the page as shown below:
Source Code:

<fieldset style="width:550px">
    <legend>Bind,Save,Edit,Update,Delete in DataList</legend>  
    <table style="width:100%">
    <tr><td width="25%">Title</td><td>
        <asp:TextBox ID="txtTitle" runat="server"></asp:TextBox></td></tr>
    <tr><td>Author</td><td>
        <asp:TextBox ID="txtAuthor" runat="server"></asp:TextBox></td></tr>
    <tr><td>Publisher</td><td>
        <asp:TextBox ID="txtPublisher" runat="server"></asp:TextBox></td></tr>
    <tr><td>Price</td><td>
        <asp:TextBox ID="txtPrice" runat="server"></asp:TextBox></td></tr>
        <tr><td>Upload Image</td><td>
            <asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
    <tr><td></td>
       <td> <asp:Button ID="btnSubmit" runat="server" Text="Save"
            onclick="btnSubmit_Click" /></td></tr>
    </table>
   
        <asp:DataList ID="dtlBooks" runat="server" RepeatColumns="2"
            oncancelcommand="dtlBooks_CancelCommand" oneditcommand="dtlBooks_EditCommand"
            onupdatecommand="dtlBooks_UpdateCommand"   ondeletecommand="dtlBooks_DeleteCommand" DataKeyField="Book_ID"
            BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px"
            CellPadding="4" GridLines="Both">
            <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
            <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
            <ItemStyle BackColor="White" ForeColor="#003399" />
        <ItemTemplate>
        <table>
        <tr>
        <td><img src='<%#Eval("ImagePath") %>' width="90px" height="90px" alt="Book Image" /></td><td><b>Title : </b><%#Eval("Title") %><br />
        <b>Author : </b><%#Eval("Author") %><br />
        <b>Publisher : </b><%#Eval("Publisher") %><br />
        <b>Price : </b><%#Eval("Price") %><br /></td>
        </tr>
        </table>
        <asp:LinkButton ID="lnkEdit" runat="server" Text="Edit" CommandName="Edit"></asp:LinkButton>
        <asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete selected records')"></asp:LinkButton>
        </ItemTemplate>
        <EditItemTemplate>       
        <b>Title : </b><asp:TextBox id="txtEditTitle" runat="server" Text='<%#Eval("Title") %>'></asp:TextBox><br />
        <b>Author : </b><asp:TextBox id="txtEditAuthor" runat="server" Text='<%#Eval("Author") %>'></asp:TextBox><br />
        <b>Publisher : </b><asp:TextBox id="txtEditPublisher" runat="server" Text='<%#Eval("Publisher") %>'></asp:TextBox><br />
        <b>Price : </b><asp:TextBox id="txtEditPrice" runat="server" Text='<%#Eval("Price") %>'></asp:TextBox><br />
         <asp:LinkButton ID="lnkUpdate" runat="server" Text="Update" CommandName="Update"></asp:LinkButton>
        <asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel"></asp:LinkButton>     
        </EditItemTemplate>
            <SelectedItemStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
        </asp:DataList>   
         </fieldset>

Note: Create a folder in the root directory of the project and name it " BookImages" to store and fetch the book images

C#.NET Code to Bind,save,edit,update,delete records from DataList:
  • In the code behind file (.aspx.cs) write the code as:
First include the following required namespaces and write the code:

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

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {       
      if (!Page.IsPostBack)
     {
            BindDataList();
     }
    }
    protected void BindDataList()
    {
        SqlDataAdapter adp = new SqlDataAdapter();
        DataTable dt = new DataTable();
        try
        {
            adp = new SqlDataAdapter("BindBookRecords_Sp", con);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dtlBooks.DataSource = dt;
                dtlBooks.DataBind();
            }
            else
            {
                dtlBooks.DataSource = dt;
                dtlBooks.DataBind();
            }
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            con.Close();
            dt.Clear();
            dt.Dispose();
        }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string filePath = string.Empty;
        SqlCommand cmd = new SqlCommand();
        string ImgPath=string.Empty;
        string DbImgPath=string.Empty;
        try
        {
            cmd = new SqlCommand("SaveBookRecords_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Title", txtTitle.Text.Trim());
            cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());
            cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());
            cmd.Parameters.AddWithValue("@Price", Convert.ToInt32(txtPrice.Text));

            if (FileUpload1.HasFile)
            {
                ImgPath = (Server.MapPath("~/BookImages/") + Guid.NewGuid() + FileUpload1.FileName);
                FileUpload1.SaveAs(ImgPath);

                DbImgPath = ImgPath.Substring(ImgPath.LastIndexOf("\\"));
                DbImgPath = DbImgPath.Insert(0, "BookImages");
                cmd.Parameters.AddWithValue("@ImagePath", DbImgPath);
            }
            else
            {
                cmd.Parameters.AddWithValue("@ImagePath", string.Empty);
            }          

            con.Open();
            cmd.ExecuteNonQuery();
            BindDataList();
            ClearControls();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been saved successfully');", true);

        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            con.Close();
            cmd.Dispose();
        }
    }
    protected void dtlBooks_CancelCommand(object source, DataListCommandEventArgs e)
    {
        dtlBooks.EditItemIndex = -1;
        BindDataList();
    }
    protected void dtlBooks_EditCommand(object source, DataListCommandEventArgs e)
    {
        dtlBooks.EditItemIndex = e.Item.ItemIndex;
        BindDataList();
    }
    protected void dtlBooks_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        Int32 bookId,prc;
        string tit = string.Empty;
        string aut = string.Empty;
        string pub = string.Empty;       
        SqlCommand cmd = new SqlCommand();
        try
        {
            tit = ((TextBox)(e.Item.FindControl("txtEditTitle"))).Text;
            aut = ((TextBox)(e.Item.FindControl("txtEditAuthor"))).Text;
            pub = ((TextBox)(e.Item.FindControl("txtEditPublisher"))).Text;
            prc = Convert.ToInt32(((TextBox)(e.Item.FindControl("txtEditPrice"))).Text);
            bookId = Convert.ToInt32(dtlBooks.DataKeys[e.Item.ItemIndex]);           
            con.Open();
            cmd = new SqlCommand("UpdateBookRecords_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Book_ID", bookId);
            cmd.Parameters.AddWithValue("@Title", tit);
            cmd.Parameters.AddWithValue("@Author", aut);
            cmd.Parameters.AddWithValue("@Publisher", pub);
            cmd.Parameters.AddWithValue("@Price", prc);
            cmd.ExecuteNonQuery();        
            dtlBooks.EditItemIndex = -1;
            BindDataList();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been updated successfully');", true);
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            cmd.Dispose();
            con.Close();
            tit=string.Empty;
            aut=string.Empty;
            pub = string.Empty;
        }
      }
    protected void dtlBooks_DeleteCommand(object source, DataListCommandEventArgs e)
    {
        Int32 bookId;
        SqlCommand cmd = new SqlCommand();
        try
        {           
            bookId = Convert.ToInt32(dtlBooks.DataKeys[e.Item.ItemIndex]);
            cmd = new SqlCommand("DeleteBookRecords_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Book_ID", bookId);
            con.Open();
            cmd.ExecuteNonQuery();                       
            BindDataList();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Book record has been deleted successfully');", true);
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! Error occured : " + ex.Message.ToString() + "');", true);
        }
        finally
        {
            con.Close();
            cmd.Dispose();
        }
    }
   
    private void ClearControls()
    {
        txtTitle.Text = string.Empty;
        txtAuthor.Text = string.Empty;
        txtPublisher.Text = string.Empty;
        txtPrice.Text = string.Empty;
        txtTitle.Focus();
    }


VB.NET Code to Bind,save,edit,update,delete records from DataList:
  • Design the page as shown in Source code section above but replace the line <asp:Button ID="btnSubmit" runat="server" Text="Save" onclick="btnSubmit_Click" />
with the <asp:Button ID="btnSubmit" runat="server" Text="Save" /> and also remove the oncancelcommand="dtlBooks_CancelCommand" oneditcommand="dtlBooks_EditCommand"   onupdatecommand="dtlBooks_UpdateCommand"   ondeletecommand="dtlBooks_DeleteCommand" from the DataList Source code.
  • Then in the code behind file ( .aspx.vb) write the code as:
First import the following required namespaces:

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

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            BindDataList()
        End If
    End Sub
    Protected Sub BindDataList()
        Dim adp As New SqlDataAdapter()
        Dim dt As New DataTable()
        Try
            adp = New SqlDataAdapter("BindBookRecords_Sp", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                dtlBooks.DataSource = dt
                dtlBooks.DataBind()
            Else
                dtlBooks.DataSource = dt
                dtlBooks.DataBind()
            End If
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured : " & ex.Message.ToString() & "');", True)
        Finally
            con.Close()
            dt.Clear()
            dt.Dispose()
        End Try
    End Sub

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim filePath As String = String.Empty
        Dim cmd As New SqlCommand()
        Dim ImgPath As String = String.Empty
        Dim DbImgPath As String = String.Empty
        Try
            cmd = New SqlCommand("SaveBookRecords_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Title", txtTitle.Text.Trim())
            cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim())
            cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim())
            cmd.Parameters.AddWithValue("@Price", Convert.ToInt32(txtPrice.Text))

            If FileUpload1.HasFile Then
                ImgPath = (Server.MapPath("~/BookImages/") + Convert.ToString(Guid.NewGuid()) + FileUpload1.FileName)
                FileUpload1.SaveAs(ImgPath)

                DbImgPath = ImgPath.Substring(ImgPath.LastIndexOf("\"))
                DbImgPath = DbImgPath.Insert(0, "BookImages")
                cmd.Parameters.AddWithValue("@ImagePath", DbImgPath)
            Else
                cmd.Parameters.AddWithValue("@ImagePath", String.Empty)
            End If 
            con.Open()
            cmd.ExecuteNonQuery()
            BindDataList()
            ClearControls()
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Book record has been saved successfully');", True)
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured : " & ex.Message.ToString() & "');", True)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    Protected Sub dtlBooks_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dtlBooks.EditCommand
        dtlBooks.EditItemIndex = e.Item.ItemIndex
        BindDataList()
    End Sub

    Protected Sub dtlBooks_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dtlBooks.UpdateCommand
        Dim bookId As Int32, prc As Int32
        Dim tit As String = String.Empty
        Dim aut As String = String.Empty
        Dim pub As String = String.Empty
        Dim cmd As New SqlCommand()
        Try
            tit = DirectCast(e.Item.FindControl("txtEditTitle"), TextBox).Text
            aut = DirectCast(e.Item.FindControl("txtEditAuthor"), TextBox).Text
            pub = DirectCast(e.Item.FindControl("txtEditPublisher"), TextBox).Text
            prc = Convert.ToInt32(DirectCast(e.Item.FindControl("txtEditPrice"), TextBox).Text)
            bookId = Convert.ToInt32(dtlBooks.DataKeys(e.Item.ItemIndex))
            con.Open()
            cmd = New SqlCommand("UpdateBookRecords_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Book_ID", bookId)
            cmd.Parameters.AddWithValue("@Title", tit)
            cmd.Parameters.AddWithValue("@Author", aut)
            cmd.Parameters.AddWithValue("@Publisher", pub)
            cmd.Parameters.AddWithValue("@Price", prc)
            cmd.ExecuteNonQuery()
            dtlBooks.EditItemIndex = -1
            BindDataList()
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Book record has been updated successfully');", True)
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured : " & ex.Message.ToString() & "');", True)
        Finally
            cmd.Dispose()
            con.Close()
            tit = String.Empty
            aut = String.Empty
            pub = String.Empty
        End Try
    End Sub

    Protected Sub dtlBooks_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dtlBooks.CancelCommand
        dtlBooks.EditItemIndex = -1
        BindDataList()
    End Sub

    Protected Sub dtlBooks_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles dtlBooks.DeleteCommand
        Dim bookId As Int32
        Dim cmd As New SqlCommand()
        Try
            bookId = Convert.ToInt32(dtlBooks.DataKeys(e.Item.ItemIndex))
            cmd = New SqlCommand("DeleteBookRecords_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Book_ID", bookId)
            con.Open()
            cmd.ExecuteNonQuery()
            BindDataList()
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Book record has been deleted successfully');", True)
        Catch ex As Exception
            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! Error occured : " & ex.Message.ToString() & "');", True)
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    Private Sub ClearControls()
        txtTitle.Text = String.Empty
        txtAuthor.Text = String.Empty
        txtPublisher.Text = String.Empty
        txtPrice.Text = String.Empty
        txtTitle.Focus()
    End Sub

Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Read More
Next
« Prev Post
Previous
Next Post »

21 comments

Click here for comments
Unknown
admin
September 18, 2013 ×

It will be better for us if we get the video of whole procedure you written.Because its hard to understand what you did instruct there.

Reply
avatar
Lalit Raghuvanshi
admin
September 18, 2013 ×

Hello Tutul chakma..thanks for your suggestion..i will also try to make video of my tutorials..so keep reading

Reply
avatar
Unknown
admin
September 21, 2013 ×

Please create a downloadable code for the above operation as its little hard to follow.....

Reply
avatar
Lalit Raghuvanshi
admin
September 21, 2013 ×

Hello Nitesh yadav..thanks for your suggestion. i will create downloadable code as soon as possible..so keep reading

Reply
avatar
Lalit Raghuvanshi
admin
September 23, 2013 ×

thanks Lam Pham Quoc for appreciating..keep reading :)

Reply
avatar
Muhammad Asif
admin
September 30, 2013 ×

Hello Image not Shown. ImagePath not save in DataBase

Reply
avatar
Lalit Raghuvanshi
admin
September 30, 2013 ×

Hello Muhammad..this code is completely tested and working..i suggest your to recheck your code once again and try once mote.. Also check the code by debugging...if still you face error then let me know..i will help you to solve your issue

Reply
avatar
prasanna
admin
October 30, 2013 ×

good! But image not stored in database so,i want to create folder for store image

Reply
avatar
Lalit Raghuvanshi
admin
October 30, 2013 ×

Hello Prasanna..i will create an article for saving the image in database as per your requirement and publish very soon.so stay connected and keep reading for more useful updates..:)

Reply
avatar
prasanna
admin
November 05, 2013 ×

Thank You ! Lalit.... But in this artical how can i use image, if i try to store image(png,jpg), it shown "Oops!! Error occured : Input string was not in a correct format" how can i clear this error...!

Reply
avatar
Anonymous
admin
December 04, 2013 ×

Hello Lalit Raghuvanshi can you help with my ASP.net VB assignment?

Reply
avatar
Unknown
admin
December 20, 2013 ×

Thank you so much sir.
I am very glad for using this article.

Reply
avatar
Unknown
admin
December 20, 2013 ×

Give me answer of Response.Execute()

Reply
avatar
Lalit Raghuvanshi
admin
December 22, 2013 ×

your welcome kadir Ansari..keep reading for more useful articles like this ..:)

Reply
avatar
Lalit Raghuvanshi
admin
December 22, 2013 ×

There is no Response.Execute() in asp.net..i think you are asking for Server.Execute(). ?

Reply
avatar
Santosh Kumar Singh
admin
February 26, 2014 ×

Thanks sir ji..........

Reply
avatar
Lalit Raghuvanshi
admin
February 26, 2014 ×

Your welcome santosh..stay connected and keep reading for more useful updates like this one..:)

Reply
avatar
Unknown
admin
March 07, 2014 ×

Thank You ! Lalit ji....

Reply
avatar
Lalit Raghuvanshi
admin
March 08, 2014 ×

Thanks Alekhya Kusuma..it is always nice to hear that my article helped anyone..Stay connected and keep reading for more useful updates like this one..:)

Reply
avatar
Anonymous
admin
March 11, 2014 ×

Hello. Do you know how to update image + info on database without create a new info? I can do INSERT, SELECT and DELETE but not UPDATE.

I'm using VS 2012, C# and SqlCe

Reply
avatar
vicky
admin
March 03, 2020 ×

nice sir
can you provide me some how to delete a particular row in the datalist name father_name etc. submit button

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

Subscribe to: Post Comments (Atom)