HOW TO BIND, EDIT, UPDATE AND DELETE DATA IN REPEATER IN ASP.NET(C#, VB)

Introduction: 
Now in this article i am going to explain with example How to bind, save/insert, edit, update and delete in Repeater data control from Sql Sever as a back end database using both C# and VB.Net languages. 

Implementation: Let's create an asp.net application to understand the operations in repeater data control.

First of all we need to create the data base in sql server.So create a database and name it "MyDataBase" or whatever as per your application requirement.

•   Create a table in Sql server as shown below and name it "Book_Details"

Note: Book_Id is the Primary key in the table

• In the web.config file create the connectionstring as:

<connectionStrings>

                                <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>

                </connectionStrings>

Note: Replace the Data source and initial catalog(database name) as per your application.

C#.NET Code to bind, edit, update and delete data in Repeater from sql server in asp.net 

• In the design page(.aspx) place four textbox, a button control and a repeater control as:

  <fieldset style="width:314px">

    <legend>Enter Book Details</legend>

    <table>

  <tr>

 <td>Book Name</td>

<td><asp:TextBox ID="txtBookName" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>Author Name</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></td>

<td><asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /></td>

</tr>

<tr>

<td colspan="2"><asp:Repeater ID="reptBook" runat="server"

        onitemcommand="rept_ItemCommand">

        <HeaderTemplate>

    

        <table style=" border:1px solid #c1650f; width:300px" cellpadding="0">

<tr style="background-color:#c1650f; color:White">

<td colspan="2">

<b><center>Book Details</center></b>

</td>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr style="background-color:#EBEFF0">

<td>

<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f; width:300px" >

<tr>

<td>

<b>Book Name:</b>

<asp:Label ID="lblBookName" runat="server" Text='<%#Eval("Book_Name") %>'/>

 <asp:TextBox ID="txtBookName" runat="server" Text='<%#Eval("Book_Name") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Author:</b>

<asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("Author") %>'/>

<asp:TextBox ID="txtAuthor" runat="server" Text='<%#Eval("Author") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Publisher:</b>

<asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("Publisher") %>'/>

<asp:TextBox ID="txtPublisher" runat="server" Text='<%#Eval("Publisher") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Price:</b>

<asp:Label ID="lblPrice" runat="server" Text='<%#Eval("Price") %>'/>

<asp:TextBox ID="txtPrice" runat="server" Text='<%#Eval("Price") %>' Visible="false"></asp:TextBox>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<td>

<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f;border-bottom:1px solid #c1650f; width:300px" >

<tr>

   <td>

        <asp:LinkButton ID="lnkEdit" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="edit">Edit</asp:LinkButton>

            <asp:LinkButton ID="lnkDelete" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="delete" onclientclick="return confirm('Are you sure you want to delete?')">Delete</asp:LinkButton>

            <asp:LinkButton ID="lnkUpdate" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="update" Visible="false">Update</asp:LinkButton>

            <asp:LinkButton ID="lnkCancel" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="cancel" Visible="false">Cancel</asp:LinkButton>

   </td>

</tr>

</table>

</td>

</tr>

<tr>

<td colspan="2">&nbsp;</td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

        </asp:Repeater></td>       

  </tr>  

    <tr>

  <td colspan="2"> <asp:Label ID="lblPageStatus" runat="server" Text=""></asp:Label></td>

   </tr>

  </table>

    </fieldset>

•  In the code behind file(.aspx.cs) write the code as:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString());

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindRepeater();

        }

    }

    protected void btnSubmit_Click(object sender, EventArgs e)

    {

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        SqlCommand cmd = new SqlCommand("insert into Book_Details(Book_Name,Author,Publisher,Price) values(@Book_Name,@Author,@Publisher,@Price)", con);

        cmd.Parameters.AddWithValue("@Book_Name", txtBookName.Text.Trim());

        cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim());

        cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim());

        cmd.Parameters.AddWithValue("@Price", txtPrice.Text.Trim());    

        cmd.ExecuteNonQuery();

        con.Close();

        Clear_Controls();

        BindRepeater();

    }

    private void Clear_Controls()

    {

        txtBookName.Text = string.Empty;

        txtAuthor.Text = string.Empty;

        txtPublisher.Text = string.Empty;

        txtPrice.Text = string.Empty;

        txtBookName.Focus();

    }

    protected void BindRepeater()

    {       

        SqlCommand cmd = new SqlCommand("Select * from Book_Details", con);

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        DataSet ds = new DataSet();

        SqlDataAdapter adp = new SqlDataAdapter(cmd);  

        adp.Fill(ds);

        reptBook.DataSource = ds;

        reptBook.DataBind();

        con.Close();    

    }

    protected void rept_ItemCommand(object source, RepeaterCommandEventArgs e)

    {

        if (e.CommandName == "edit")

        {

            ((Label)e.Item.FindControl("lblBookName")).Visible = false;

            ((Label)e.Item.FindControl("lblAuthor")).Visible = false;

            ((Label)e.Item.FindControl("lblPublisher")).Visible = false;

            ((Label)e.Item.FindControl("lblPrice")).Visible = false;

            ((TextBox)e.Item.FindControl("txtBookName")).Visible = true;

            ((TextBox)e.Item.FindControl("txtAuthor")).Visible = true;

            ((TextBox)e.Item.FindControl("txtPublisher")).Visible = true;

            ((TextBox)e.Item.FindControl("txtPrice")).Visible = true;

            ((LinkButton)e.Item.FindControl("lnkEdit")).Visible = false;

            ((LinkButton)e.Item.FindControl("lnkDelete")).Visible = false;

            ((LinkButton)e.Item.FindControl("lnkUpdate")).Visible = true;

            ((LinkButton)e.Item.FindControl("lnkCancel")).Visible = true;

        }

        if (e.CommandName == "update")

        {

            string bookName = ((TextBox)e.Item.FindControl("txtBookName")).Text;

            string author = ((TextBox)e.Item.FindControl("txtAuthor")).Text;

            string pub = ((TextBox)e.Item.FindControl("txtPublisher")).Text;

            string price = ((TextBox)e.Item.FindControl("txtPrice")).Text;

            SqlDataAdapter adp = new SqlDataAdapter("Update Book_Details set Book_Name= @Book_Name, Author=@Author,Publisher=@Publisher,Price=@Price where Book_Id = @Book_Id", con);

            adp.SelectCommand.Parameters.AddWithValue("@Book_Name", bookName);

            adp.SelectCommand.Parameters.AddWithValue("@Author", author);

            adp.SelectCommand.Parameters.AddWithValue("@Publisher", pub);

            adp.SelectCommand.Parameters.AddWithValue("@Price", price);

            adp.SelectCommand.Parameters.AddWithValue("@Book_Id", e.CommandArgument);

            DataSet ds = new DataSet();

            adp.Fill(ds);

            BindRepeater();  

        }

        if(e.CommandName =="delete")

        {

            if (con.State == ConnectionState.Closed)

            {

                con.Open();

            }

            SqlCommand cmd = new SqlCommand("delete from Book_Details where Book_Id = @Book_Id", con);

            cmd.Parameters.AddWithValue("@Book_Id", e.CommandArgument);

            cmd.ExecuteNonQuery();

            cmd.Dispose();         

            BindRepeater();

        }

        if (e.CommandName == "cancel")

        {

            ((Label)e.Item.FindControl("lblBookName")).Visible = true;

            ((Label)e.Item.FindControl("lblAuthor")).Visible = true;

            ((Label)e.Item.FindControl("lblPublisher")).Visible = true;

            ((Label)e.Item.FindControl("lblPrice")).Visible = true;

            ((TextBox)e.Item.FindControl("txtBookName")).Visible = false;

            ((TextBox)e.Item.FindControl("txtAuthor")).Visible = false;

            ((TextBox)e.Item.FindControl("txtPublisher")).Visible = false;

            ((TextBox)e.Item.FindControl("txtPrice")).Visible = false;

            ((LinkButton)e.Item.FindControl("lnkEdit")).Visible = true;

            ((LinkButton)e.Item.FindControl("lnkDelete")).Visible = true;

            ((LinkButton)e.Item.FindControl("lnkUpdate")).Visible = false;

            ((LinkButton)e.Item.FindControl("lnkCancel")).Visible = false;

        }

    }

VB.NET Code to bind, edit, update and delete data in Repeater from sql server in asp.net

• In the design page(.aspx) place four textbox, a button control and a repeater control as:

 <fieldset style="width:314px">

    <legend>Enter Book Details</legend>

    <table>

  <tr> 

<td>Book Name</td>

<td><asp:TextBox ID="txtBookName" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>Author Name</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></td>

<td><asp:Button ID="btnSubmit" runat="server" Text="Submit" /></td>

</tr>

<tr>

<td colspan="2"><asp:Repeater ID="reptBook" runat="server"

        onitemcommand="rept_ItemCommand">

        <HeaderTemplate>

    

        <table style=" border:1px solid #c1650f; width:300px" cellpadding="0">

<tr style="background-color:#c1650f; color:White">

<td colspan="2">

<b><center>Book Details</center></b>

</td>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr style="background-color:#EBEFF0">

<td>

<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f; width:300px" >

<tr>

<td>

<b>Book Name:</b>

<asp:Label ID="lblBookName" runat="server" Text='<%#Eval("Book_Name") %>'/>

 <asp:TextBox ID="txtBookName" runat="server" Text='<%#Eval("Book_Name") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Author:</b>

<asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("Author") %>'/>

<asp:TextBox ID="txtAuthor" runat="server" Text='<%#Eval("Author") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Publisher:</b>

<asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("Publisher") %>'/>

<asp:TextBox ID="txtPublisher" runat="server" Text='<%#Eval("Publisher") %>' Visible="false"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<b>Price:</b>

<asp:Label ID="lblPrice" runat="server" Text='<%#Eval("Price") %>'/>

<asp:TextBox ID="txtPrice" runat="server" Text='<%#Eval("Price") %>' Visible="false"></asp:TextBox>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<td>

<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f;border-bottom:1px solid #c1650f; width:300px" >

<tr>

   <td>

        <asp:LinkButton ID="lnkEdit" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="edit">Edit</asp:LinkButton>

            <asp:LinkButton ID="lnkDelete" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="delete" onclientclick="return confirm('Are you sure you want to delete?')">Delete</asp:LinkButton>

            <asp:LinkButton ID="lnkUpdate" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="update" Visible="false">Update</asp:LinkButton>

            <asp:LinkButton ID="lnkCancel" runat="server" CommandArgument='<%#Eval("Book_Id") %>'CommandName="cancel" Visible="false">Cancel</asp:LinkButton>

   </td>

</tr>

</table>

</td>

</tr>

<tr>

<td colspan="2">&nbsp;</td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

        </asp:Repeater></td>       

  </tr>  

    <tr>

  <td colspan="2"> <asp:Label ID="lblPageStatus" runat="server" Text=""></asp:Label></td>

   </tr>

  </table>

    </fieldset>

• In the code behind file(.aspx.vb) write the code as:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

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

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            BindRepeater()

        End If

    End Sub

    Protected Sub BindRepeater()

        Dim cmd As New SqlCommand("Select * from Book_Details", con)

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        Dim ds As New DataSet()

        Dim adp As New SqlDataAdapter(cmd)

        adp.Fill(ds)

        reptBook.DataSource = ds

        reptBook.DataBind()

        con.Close()

    End Sub

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        Dim cmd As New SqlCommand("insert into Book_Details(Book_Name,Author,Publisher,Price) values(@Book_Name,@Author,@Publisher,@Price)", con)

        cmd.Parameters.AddWithValue("@Book_Name", txtBookName.Text.Trim())

        cmd.Parameters.AddWithValue("@Author", txtAuthor.Text.Trim())

        cmd.Parameters.AddWithValue("@Publisher", txtPublisher.Text.Trim())

        cmd.Parameters.AddWithValue("@Price", txtPrice.Text.Trim())

        cmd.ExecuteNonQuery()

        con.Close()

        Clear_Controls()

        BindRepeater()

    End Sub

    Private Sub Clear_Controls()

        txtBookName.Text = String.Empty

        txtAuthor.Text = String.Empty

        txtPublisher.Text = String.Empty

        txtPrice.Text = String.Empty

        txtBookName.Focus()

    End Sub

    Protected Sub rept_ItemCommand(ByVal source As Object, ByVal e AsSystem.Web.UI.WebControls.RepeaterCommandEventArgs) Handles reptBook.ItemCommand

        If e.CommandName = "edit" Then

            DirectCast(e.Item.FindControl("lblBookName"), Label).Visible = False

            DirectCast(e.Item.FindControl("lblAuthor"), Label).Visible = False

            DirectCast(e.Item.FindControl("lblPublisher"), Label).Visible = False

            DirectCast(e.Item.FindControl("lblPrice"), Label).Visible = False

            DirectCast(e.Item.FindControl("txtBookName"), TextBox).Visible = True

            DirectCast(e.Item.FindControl("txtAuthor"), TextBox).Visible = True

            DirectCast(e.Item.FindControl("txtPublisher"), TextBox).Visible = True

            DirectCast(e.Item.FindControl("txtPrice"), TextBox).Visible = True

            DirectCast(e.Item.FindControl("lnkEdit"), LinkButton).Visible = False

            DirectCast(e.Item.FindControl("lnkDelete"), LinkButton).Visible = False

            DirectCast(e.Item.FindControl("lnkUpdate"), LinkButton).Visible = True

            DirectCast(e.Item.FindControl("lnkCancel"), LinkButton).Visible = True

        End If

        If e.CommandName = "update" Then

            Dim bookName As String = DirectCast(e.Item.FindControl("txtBookName"), TextBox).Text

            Dim author As String = DirectCast(e.Item.FindControl("txtAuthor"), TextBox).Text

            Dim pub As String = DirectCast(e.Item.FindControl("txtPublisher"), TextBox).Text

            Dim price As String = DirectCast(e.Item.FindControl("txtPrice"), TextBox).Text

            Dim adp As New SqlDataAdapter("Update Book_Details set Book_Name= @Book_Name, Author=@Author,Publisher=@Publisher,Price=@Price where Book_Id = @Book_Id", con)

            adp.SelectCommand.Parameters.AddWithValue("@Book_Name", bookName)

            adp.SelectCommand.Parameters.AddWithValue("@Author", author)

            adp.SelectCommand.Parameters.AddWithValue("@Publisher", pub)

            adp.SelectCommand.Parameters.AddWithValue("@Price", price)

            adp.SelectCommand.Parameters.AddWithValue("@Book_Id", e.CommandArgument)

            Dim ds As New DataSet()

            adp.Fill(ds)

            BindRepeater()

        End If

        If e.CommandName = "delete" Then

            If con.State = ConnectionState.Closed Then

                con.Open()

            End If

            Dim cmd As New SqlCommand("delete from Book_Details where Book_Id = @Book_Id", con)

            cmd.Parameters.AddWithValue("@Book_Id", e.CommandArgument)

            cmd.ExecuteNonQuery()

            cmd.Dispose()

            BindRepeater()

        End If

        If e.CommandName = "cancel" Then

            DirectCast(e.Item.FindControl("lblBookName"), Label).Visible = True

            DirectCast(e.Item.FindControl("lblAuthor"), Label).Visible = True

            DirectCast(e.Item.FindControl("lblPublisher"), Label).Visible = True

            DirectCast(e.Item.FindControl("lblPrice"), Label).Visible = True

            DirectCast(e.Item.FindControl("txtBookName"), TextBox).Visible = False

            DirectCast(e.Item.FindControl("txtAuthor"), TextBox).Visible = False

            DirectCast(e.Item.FindControl("txtPublisher"), TextBox).Visible = False

            DirectCast(e.Item.FindControl("txtPrice"), TextBox).Visible = False

            DirectCast(e.Item.FindControl("lnkEdit"), LinkButton).Visible = True

            DirectCast(e.Item.FindControl("lnkDelete"), LinkButton).Visible = True

            DirectCast(e.Item.FindControl("lnkUpdate"), LinkButton).Visible = False

            DirectCast(e.Item.FindControl("lnkCancel"), LinkButton).Visible = False

        End If

    End Sub