DataList Control in Asp.net in VB.net
This Article explain the DataList Control and its use in Asp.net program in vb.net language
#Create new table (department)
CREATE TABLE [dbo].[tbdep]
(
[dno]
INT NOT NULL PRIMARY KEY,
[dname] VARCHAR(50) NULL
)
|
*Add Data in the table
#Create new table (designation)
CREATE TABLE [dbo].[tbdsg]
(
[dsgcod]
INT NOT NULL PRIMARY KEY,
[dsgname] VARCHAR(50) NULL
)
|
*Add Data in the table
#Create new table (employee)
CREATE TABLE [dbo].[tbemployee]
(
[empno]
INT NOT NULL PRIMARY KEY,
[ename] VARCHAR(50) NULL,
[eadd] VARCHAR(50) NULL,
[esal] INT NULL,
[edno] INT NULL,
[edsgcod] INT NULL,
CONSTRAINT [FK_tbemployee_tbdep] FOREIGN KEY ([edno]) REFERENCES [tbdep]([dno]),
CONSTRAINT [FK_tbemployee_tbdsg] FOREIGN KEY ([edsgcod]) REFERENCES [tbdsg]([dsgcod])
)
|
*Add Data in the table
#Requirement
Name
|
Add
|
Sal
|
Dept
|
Dsg
|
Query for it
Select ename,eadd,esal,dname,dsgname from tbemployee
a,tbdep b,tbdsg c where a.edno=b.dno and a.edsgcod=c.dsgcod
Making Project in VB.NET and using DataList Control
# New> Project> Visual Basic > Web >
Website empty
*Create ConnectionString
<configuration>
<connectionStrings>
<add name="ps" connectionString="server=./sqlexpress;
database=database1; integrated security=true"/>
</connectionStrings>
|
*Add new webform
Toolbox>Data>DataList
*Now open its coding
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Page.IsPostBack = False Then
List_Bind()
End If
End Sub
Private Sub List_Bind()
Dim qry As String
qry = "Select
ename,eadd,esal,dname,dsgname from tbemployee,tbdep,tbdsg where edno=dno and
edsgcod=dsgcod"
Dim adp As New SqlDataAdapter(qry,
ConfigurationManager.ConnectionStrings("ps").ConnectionString)
Dim ds As New DataSet()
adp.Fill(ds)
DataList1.DataSource = ds
DataList1.DataBind()
End Sub
Protected Sub DataList1_EditCommand(source As Object, e As DataListCommandEventArgs) Handles DataList1.EditCommand
DataList1.EditItemIndex = e.Item.ItemIndex
List_Bind()
End Sub
End Class
|
*Source code
<asp:DataList ID="DataList1" runat="server" RepeatColumns="2">
<ItemTemplate>
<b>Name : </b><%#Eval("ename")%><br />
<b>Address : </b><%#Eval("eadd")%><br />
<b>Salary : </b><%#Eval("esal")%><br />
<b>Depts : </b><%#Eval("dname")%><br />
<b>Dsg : </b><%#Eval("dsgname")%><br />
<asp:LinkButton ID="lk" Text="Edit" CommandName="edit" runat="server" />
</ItemTemplate>
<EditItemTemplate>
<b>Name : </b><asp:TextBox ID="t1" Text='<%#("ename")%>' runat="server" /><br />
<b>Address : </b><asp:TextBox ID="t2" Text='<%#("eadd")%>' runat="server" /><br />
<b>Salary : </b><asp:TextBox ID="t3" Text='<%#("esal")%>' runat="server" /><br />
<b>Depts : </b><asp:DropDownList ID="ddl1" DataTextField="dname" DataValueField="dno" runat="server" /><br />
<b>Dsg : </b><asp:DropDownList ID="ddl2" DataTextField="dsgname" DataValueField="dsgcod" runat="server" /><br />
<asp:LinkButton ID="lk1" Text="Update" CommandName="update" runat="server" />
<asp:LinkButton ID="lk2" Text="Cancel" CommandName="cancel" runat="server" />
</EditItemTemplate>
</asp:DataList>
|
*DataList Properties> Event>Cancel command
Protected Sub DataList1_CancelCommand(source As Object, e As DataListCommandEventArgs) Handles DataList1.CancelCommand
DataList1.EditItemIndex = -1
List_Bind()
End Sub
|
#To bind data with dropdownlist
*DataList Properties> Event> item Data bound
command
Data item bound fires all templates
Protected Sub DataList1_ItemDataBound(sender As Object, e As DataListItemEventArgs) Handles DataList1.ItemDataBound
If e.Item.ItemType = ListItemType.EditItem Then
Dim dep As DropDownList
Dim dsg As DropDownList
dep = CType(e.Item.FindControl("ddl1"), DropDownList)
dsg = CType(e.Item.FindControl("ddl2"), DropDownList)
Dim adp As New SqlDataAdapter("select * from tbdep; select * from
tbdsg",
ConfigurationManager.ConnectionStrings("ps").ConnectionString)
Dim ds As New DataSet()
adp.Fill(ds)
dep.DataSource = ds.Tables(0)
dep.DataBind()
dsg.DataSource = ds.Tables(1)
dep.DataBind()
Dim r As DataRowView
r = e.Item.DataItem
dep.Items.FindByValue(r("edno")).Selected =
True
dsg.Items.FindByValue(r("edsgcod")).Selected =
True
End If
End Sub
End Class
|
*To get same value in drop down list
Dim r As DataRowView
r = e.Item.DataItem
dep.Items.FindByValue(r("edno")).Selected =
True
dsg.Items.FindByValue(r("edsgcod")).Selected =
True
|
*DataList Properties> Event>Update command
Protected Sub DataList1_UpdateCommand(source As Object, e As DataListCommandEventArgs) Handles DataList1.UpdateCommand
Dim empno, esal, edno, edsgcod As Int32
Dim ename, eadd As String
empno = Convert.ToInt32(DataList1.DataKeys(e.Item.ItemIndex))
ename = (CType((e.Item.FindControl("t1")), TextBox)).Text
eadd = (CType((e.Item.FindControl("t2")), TextBox)).Text
esal = Convert.ToInt32((CType((e.Item.FindControl("t3")), TextBox)).Text)
edno = Convert.ToInt32((CType((e.Item.FindControl("ddl1")), DropDownList)).SelectedValue)
edsgcod = Convert.ToInt32((CType((e.Item.FindControl("ddl2")), DropDownList)).SelectedValue)
Dim con As New SqlConnection()
con.ConnectionString = ConfigurationManager.ConnectionStrings("ps").ConnectionString
con.Open()
Dim cmd As New SqlCommand()
cmd.CommandText = "update
tbemployee set ename=@en,eadd=@ed,esal=@es,edno=@dno,edsgcod=@dsgcod where
empno=@eno"
cmd.Connection = con
cmd.Parameters.Add("@eno", SqlDbType.Int).Value = empno
cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value = ename
cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value = eadd
cmd.Parameters.Add("@es", SqlDbType.Int).Value = esal
cmd.Parameters.Add("@dno", SqlDbType.Int).Value = edno
cmd.Parameters.Add("@dsgcod", SqlDbType.Int).Value = edsgcod
cmd.ExecuteNonQuery()
cmd.Dispose()
DataList1.EditItemIndex = -1
List_Bind()
End Sub
|
