Introduction: In the previous articles i explained How to bind Repeater data control in asp.net(C#, VB)and How to implement Custom paging in Repeater control in asp.net(C#, VB) and Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView in asp.net C# and How to upload, download and delete files from GridView in Asp.net and How to bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in Asp.net.
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.
Note: Book_Id is the Primary key in the table
- In the web.config file create the connectionstring as:
<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:
<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"> </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.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:
<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"> </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.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 As
System.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
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 for more technical
updates."
8 comments
Click here for commentsthank u sir very helpful
ReplyYour welcome..stay connected for more updates like this..
Replythis was very elaborative ... thank you !!!!
Replythanks Ashish Yadav for appreciating this article..keep reading..:)
ReplyHi Lalit,
Replyvery nice article.. I just have few questions regarding repeater control.
I am implementing comments functionality in one my application.
1) I only wants to add button for header/parent row. If user wants to comments on specific record then he has to click on that button. This should be done for the all the parent rows.
For e.g.
RecordNumber1 // Comment Button
Comment1
Comment2
RecordsNumber2 // Comment Button
Comment1
Comment2
2) Also, wants to edit and delete each n every row.
Can you plz help on this ?
Regards,
Rohit Pundlik
Hi Raghav, I liked your way of explaining the concept. I really learned about adding Edit, Delete options to a repeater control in asp.net
ReplyThanks
Hello Sharath..thanks for appreciating my work..i am glad you found this article helpful..stay connected and keep reading..:)
ReplyThank you for this code brother :)
ReplyIf 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..