How to use DataList Contro in VB.NET

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.
  • 12096
 

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>
                            &nbsp;
                        </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>
                        &nbsp;|&nbsp;
                        <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">
                                &nbsp;
                                <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 [email protected],[email protected],[email protected],[email protected],[email protected] 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:-

-----------------------------------------------------------------------------------------------------
 datalist.bmp


Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.