Introduction: In this post I will explain you how to bind,
edit, delete and update the Datalist data control in Asp.net.
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:
No comments:
Post a Comment