In this article, We will see how to use Datalist control an VB.NET. We will use Add, Update, Select and Delete command in the datalist.
The database connection string.
The below is my connection string which is stored in web.config file. You can change this connection string according to your SQL server database setting.
<appSettings>
<add key="connect" value="Initial Catalog=Data; Data Source=MCNDESKTOP10; uid=sa; pwd=wintellect"/>
</appSettings>
This is aspx code:-
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server" DataKeyField="StId" Width="100%" BorderColor="Tan"
BorderWidth="1px" BackColor="#ffccff" CellPadding="2" ForeColor="Black"
OnCancelCommand="DataList1_CancelCommand" OnDeleteCommand="DataList1_DeleteCommand" OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand">
<HeaderTemplate>
<table width="100%">
<tr align="left">
<th width="10%">
StId
</th>
<th width="10%">
Name
</th>
<th width="10%">
ClassName
</th>
<th width="10%">
RollNo
</th>
<th width="10%">
EmailId
</th>
<th width="10%">
TotalRecord
</th>
<th>
</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="left">
<td>
<%# DataBinder.Eval(Container.DataItem, "StId") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "Name") %>
</td>
<td>
<%#DataBinder.Eval(Container.DataItem, "ClassName")%>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "RollNo") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "EmailId") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "TotalRecord") %>
</td>
<td>
<asp:LinkButton ID="lnkEdit" runat="server" CommandName="edit"> Edit </asp:LinkButton>
|
<asp:LinkButton ID="lnkDelete" runat="server" CommandName="delete"> Delete </asp:LinkButton>
</td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td>
<asp:TextBox ID="txtStId" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "StId") %>'>
</asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>'>
</asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtClassName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "ClassName") %>'>
</asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtRollNo" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "RollNo") %>'>
</asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtEmailId" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "EmailId") %>'>
</asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtTotalRecord" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "TotalRecord") %>'>
</asp:TextBox>
</td>
<td>
<asp:LinkButton ID="lnkUpdate" runat="server" CommandName="update">Update </asp:LinkButton>
<asp:LinkButton ID="lnkCancel" runat="server" CommandName="cancel">Cancel </asp:LinkButton>
</td>
</tr>
</EditItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
<FooterStyle BackColor="Tan" />
<SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<AlternatingItemStyle BackColor="PaleGoldenrod" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
</asp:DataList>
<table width="100%">
<tr>
<td height="10px">
</td>
</tr>
<tr id="PagingRow" runat="server">
<td colspan="3" style="background-color: #3D3D3D; padding-left: 15px;">
<table width="99%" class="pagingBackground" cellpadding="2" cellspacing="2">
<tr>
<td width="20%">
</td>
<td align="center" width="50">
</td>
<td align="center">
</td>
<td align="center" width="20%">
</td>
</tr>
<tr>
<td width="20%" align="left">
<asp:Label ID="LabelPageFirstRecord" runat="server"></asp:Label>
<asp:Label ID="label2" runat="server" Text="-"></asp:Label>
<asp:Label ID="LabelPageLastRecord" runat="server"></asp:Label>
<asp:Label ID="label1" runat="server" Text="of"></asp:Label>
<asp:Label ID="LabelTotalRecords" runat="server"></asp:Label>
</td>
<td width="50" align="center">
</td>
<td align="center" id="tdPageNumbers" runat="server">
<asp:LinkButton ID="LinkButtonFirst" runat="server" OnClick="LinkButtonFirst_Click" CssClass="PagerLinkStyle">
<img border=0 src='<%=ResolveUrl("Image/First.gif")%>' onmouseover="this.src='<%=ResolveUrl("Image/FirstHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("Image/First.gif")%>';"/>
</asp:LinkButton>
<asp:LinkButton ID="LinkButtonPrevious" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButtonPrevious_Click">
<img border=0 src='<%=ResolveUrl("Image/Previous.gif")%>' onmouseover="this.src='<%=ResolveUrl("Image/PreviousHover.gif")%>';"onmouseout="this.src='<%=ResolveUrl("Image/Previous.gif")%>';"/>
</asp:LinkButton>
<asp:LinkButton ID="LinkButton1" runat="server" ForeColor="White" OnClick="LinkButton1_Click">1</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" ForeColor="White" OnClick="LinkButton1_Click">2</asp:LinkButton>
<asp:LinkButton ID="LinkButton3" runat="server" ForeColor="White" OnClick="LinkButton1_Click">3</asp:LinkButton>
<asp:LinkButton ID="LinkButton4" runat="server" ForeColor="White" OnClick="LinkButton1_Click">4</asp:LinkButton>
<asp:LinkButton ID="LinkButton5" runat="server" ForeColor="White" OnClick="LinkButton1_Click">5</asp:LinkButton>
<asp:LinkButton ID="LinkButtonNext" runat="server" ForeColor="White" OnClick="LinkButtonNext_Click">
<img border="0" src='<%=ResolveUrl("Image/Next.gif")%>' onmouseover="this.src='<%=ResolveUrl("Image/NextHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("Image/Next.gif")%>';"/>
</asp:LinkButton>
<asp:LinkButton ID="LinkButtonLast" runat="server" OnClick="LinkButtonLast_Click" CssClass="PagerLinkStyle">
<img border="0" src='<%=ResolveUrl("Image/Last.gif")%>' onmouseover="this.src='<%=ResolveUrl("Image/LastHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("Image/Last.gif")%>';"/>
</asp:LinkButton>
</td>
<td width="20%" align="right">
</td>
</tr>
<tr>
<td width="20%">
</td>
<td align="center" width="50">
</td>
<td align="center">
</td>
<td align="center" width="20%">
</td>
</tr>
</table>
</td>
</tr>
</table>
<br />
<br />
<table>
<tr>
<td>
<asp:Label ID="lblEmpId" runat="server" Text="StId"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblname" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblClassname" runat="server" Text="ClassName"></asp:Label>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblRollNo" runat="server" Text="RollNo"></asp:Label>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblEmailId" runat="server" Text="EmailId"></asp:Label>
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblTotalRecord" runat="server" Text="TotalRecord"></asp:Label>
<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
</td>
</tr>
</table>
<asp:Button ID="btnsubmit" runat="server" Text="Submit" BackColor="Black" ForeColor="White"
OnClick="btnsubmit_Click" />
<asp:Button ID="btnReset" runat="server" Text="Reset" BackColor="Black" ForeColor="White"
OnClick="btnReset_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" BackColor="Black" ForeColor="White" />
</div>
</form>
</body>
</html>
This is .vb code:-
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
-----------------------------------------------------------------------------------------------------
Partial Class _Default
Inherits System.Web.UI.Page
Private da As SqlDataAdapter
Private con As SqlConnection
Private cmd As New SqlCommand()
Private PageSize As Integer = 5
Private blogsHelper As New BlogsHelper()
Private dataSet As New DataSet()
Private ds As New DataSet()
-----------------------------------------------------------------------------------------------------
Protected Sub DataList1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataList1.Load
If Not Page.IsPostBack Then
GetLatestBlogs()
End If
End Sub
-----------------------------------------------------------------------------------------------------
Public Sub GetLatestBlogs()
Dim blogsHelper As New BlogsHelper()
Dim dataTable As New DataTable()
dataTable = blogsHelper.GetLatestBlogs().Tables(0)
If Not Object.Equals(dataTable, Nothing) Then
If dataTable.Rows.Count > 0 Then
If PagingRow.Visible = True Then
' Setting viewstate for TotalRecords LabelTotalRecords.Text = dataTable.Rows.Count.ToString()
' Setting viewstate for TotalPages
If Integer.Parse(LabelTotalRecords.Text) Mod PageSize > 0 Then
' If page size is not exactly divisible by total records
ViewState("TotalPages") = Integer.Parse(LabelTotalRecords.Text) / PageSize
Else
' If page is exactly divisible by total records
ViewState("TotalPages") = Integer.Parse(LabelTotalRecords.Text) / PageSize - 1
End If
ViewState("dataTable") = dataTable
If Not Object.Equals(dataTable, Nothing) Then
BindList(ManagePaging(dataTable))
End If
Else
BindList(dataTable)
End If
Else
PagingRow.Visible = False
End If
Else
PagingRow.Visible = False
End If
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim con As SqlConnection
con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
con.Open()
Dim cmd As SqlCommand
cmd = New SqlCommand("Insert into StudentRecord (Name,ClassName,RollNo,EmailId,TotalRecord) values('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "')", con)
cmd.ExecuteNonQuery()
con.Close()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As EventArgs)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub LinkButtonFirst_Click(ByVal sender As Object, ByVal e As EventArgs)
CurrentPage = 0
ReloadControl()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub LinkButtonPrevious_Click(ByVal sender As Object, ByVal e As EventArgs)
CurrentPage -= 1
ReloadControl()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub LinkButtonNext_Click(ByVal sender As Object, ByVal e As EventArgs)
CurrentPage += 1
ReloadControl()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub LinkButtonLast_Click(ByVal sender As Object, ByVal e As EventArgs)
CurrentPage = Integer.Parse(ViewState("TotalPages").ToString())
ReloadControl()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim lnkbtn As LinkButton = CType(sender, LinkButton)
CurrentPage = (Integer.Parse(lnkbtn.Text) - 1)
ReloadControl()
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub BindList(ByVal dataTable As DataTable)
Dim photo As String = dataTable.Rows(0)("Photo").ToString()
DataList1.DataSource = dataTable
DataList1.DataBind()
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub BindList(ByVal pageDataSource As PagedDataSource)
DataList1.DataSource = pageDataSource
DataList1.DataBind()
ShowPagingLinks()
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub ReloadControl()
If Not Object.Equals(ViewState("dataTable"), Nothing) Then
BindList(ManagePaging(CType(ViewState("dataTable"), DataTable)))
End If
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub ShowPagingLinks()
If CurrentPage = Integer.Parse(ViewState("TotalPages").ToString()) Then
LinkButtonNext.Enabled = False
LinkButtonLast.Enabled = False
Else
LinkButtonNext.Enabled = True
LinkButtonLast.Enabled = True
End If
If CurrentPage = 0 Then
LinkButtonPrevious.Enabled = False
LinkButtonFirst.Enabled = False
Else
LinkButtonPrevious.Enabled = True
LinkButtonFirst.Enabled = True
End If
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub ShowTotalNumberOfRecords()
Dim i As Integer, j As Integer
If CurrentPage = 0 Then
i = 1
Else
i = (CurrentPage * PageSize)
End If
LabelPageFirstRecord.Text = i.ToString()
If CurrentPage = Integer.Parse(ViewState("TotalPages").ToString()) Then
LabelPageLastRecord.Text = LabelTotalRecords.Text
Else
j = ((CurrentPage + 1) * PageSize)
LabelPageLastRecord.Text = j.ToString()
End If
End Sub
-----------------------------------------------------------------------------------------------------
Private Sub ShowPageNumbers()
Dim startPagenumber As Integer, endPageNumber As Integer
If CurrentPage < 3 Then
startPagenumber = 1
endPageNumber = 5
ElseIf CurrentPage > (Integer.Parse(ViewState("TotalPages").ToString()) - 2) Then
startPagenumber = Integer.Parse(ViewState("TotalPages").ToString()) - 3
endPageNumber = Integer.Parse(ViewState("TotalPages").ToString()) + 1
If startPagenumber = 0 Then
startPagenumber = 1
endPageNumber += 1
End If
Else
startPagenumber = CurrentPage - 1
endPageNumber = CurrentPage + 3
End If
Dim linkButtonNumber As Integer = 1
Dim lnkbtn As LinkButton
For k As Integer = startPagenumber To endPageNumber
lnkbtn = DirectCast(tdPageNumbers.FindControl("LinkButton" + linkButtonNumber.ToString()), LinkButton)
lnkbtn.Text = k.ToString()
linkButtonNumber += 1
Next
For idLoop As Integer = 1 To 5
lnkbtn = DirectCast(tdPageNumbers.FindControl("LinkButton" + idLoop.ToString()), LinkButton)
If Integer.Parse(lnkbtn.Text) = (CurrentPage + 1) Then
lnkbtn.Enabled = False
lnkbtn.CssClass = "PagerLinkSelected"
ElseIf Integer.Parse(lnkbtn.Text) > (Integer.Parse(ViewState("TotalPages").ToString()) + 1)Then
lnkbtn.Visible = False
Else
lnkbtn.Enabled = True
lnkbtn.CssClass = "PagerLinkStyle"
lnkbtn.BackColor = System.Drawing.Color.Empty
End If
Next
End Sub
-----------------------------------------------------------------------------------------------------
Private Function ManagePaging(ByVal dTable As DataTable) As PagedDataSource
Dim pageDataSource As PagedDataSource = New PagedDataSource()
pageDataSource.DataSource = dTable.DefaultView
pageDataSource.AllowPaging = True
pageDataSource.PageSize = PageSize
pageDataSource.CurrentPageIndex = CurrentPage
ShowTotalNumberOfRecords()
ShowPageNumbers()
Return pageDataSource
End Function
-----------------------------------------------------------------------------------------------------
Public Property CurrentPage() As Integer
Get
' look for current page in ViewState
Dim current As Object = Me.ViewState("CurrentPage")
If current Is Nothing Then
Return 0 ' default page index of 0
Else
Return CType(current, Integer)
End If
End Get
Set(ByVal Value As Integer)
Me.ViewState("CurrentPage") = Value
End Set
End Property
-----------------------------------------------------------------------------------------------------
Protected Sub DataList1_DeleteCommand(ByVal source As Object, ByVal e As DataListCommandEventArgs)
con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
cmd.Connection = con
Dim StId As Integer = CInt(DataList1.DataKeys(CInt(e.Item.ItemIndex)))
cmd.CommandText = "Delete from StudentRecord where StId=" & StId
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
DataList1.EditItemIndex = -1
GetLatestBlogs()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub DataList1_CancelCommand(ByVal source As Object, ByVal e As DataListCommandEventArgs
DataList1.EditItemIndex = -1
GetLatestBlogs()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub DataList1_EditCommand(ByVal source As Object, ByVal e As DataListCommandEventArgs)
DataList1.EditItemIndex = e.Item.ItemIndex
GetLatestBlogs()
End Sub
-----------------------------------------------------------------------------------------------------
Protected Sub DataList1_UpdateCommand(ByVal source As Object, ByVal e As DataListCommandEventArgs)
' Get the DataKey value associated with current Item Index.
Dim StId As Integer = CInt(DataList1.DataKeys(CInt(e.Item.ItemIndex)))
' Get updated value entered by user in textbox control for
' Name field.
Dim txtName As TextBox
txtName = DirectCast(e.Item.FindControl("txtName"), TextBox)
Dim txtClassName As TextBox
txtClassName = DirectCast(e.Item.FindControl("txtClassName"), TextBox)
Dim txtRollNo As TextBox
txtRollNo = DirectCast(e.Item.FindControl("txtRollNo"), TextBox)
Dim txtEmailId As TextBox
txtEmailId = DirectCast(e.Item.FindControl("txtEmailId"), TextBox)
Dim txtTotalRecord As TextBox
txtTotalRecord = DirectCast(e.Item.FindControl("txtTotalRecord"), TextBox)
' string variable to store the connection string
' retrieved from the connectionStrings section of web.config
con = New SqlConnection(ConfigurationManager.AppSettings("connect"))
' sql command object initialized with update command text
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text
cmd.Parameters.Add("@ClassName", SqlDbType.VarChar).Value = txtClassName.Text
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = txtRollNo.Text
cmd.Parameters.Add("@EmailId", SqlDbType.VarChar).Value = txtEmailId.Text
cmd.Parameters.Add("@TotalRecord", SqlDbType.VarChar).Value = txtTotalRecord.Text
cmd.Parameters.Add("@StId", SqlDbType.Int).Value = StId
cmd.CommandText = "Update StudentRecord set Name=@Name,ClassName=@ClassName,RollNo=@RollNo,EmailId=@EmailId,TotalRecord=@TotalRecord where StId=" & StId
cmd.Connection = con
cmd.Connection.Open()
cmd.ExecuteNonQuery()
' reset the DataList mode back to its initial state
DataList1.EditItemIndex = -1
GetLatestBlogs()
End Sub
End Class
Output:-
-----------------------------------------------------------------------------------------------------
