This sample shows how to populate a DropDownList from a Database, and then, displaying the entire record details, based on the DropDownList choice which was made. We are using the OleDb Data Provider, in this case, using the MS Access database, Northwind.
There are two ways shown here, on how to display the details. The first way is merely by populating a DataGrid. This is just the ‘down and dirty’ method of choosing and displaying.
The second method requires getting your hands ‘dirty’ somewhat (sorry for all the ‘dirt’ references), by assigning the variables when the DataReader is being read, and then manually creating the form in which the data is displayed.
Also, you will notice that there is a ‘Helper Function’called ‘FixDR’ which guards against Null data in the database. Without this function, an error would be raised when trying to create a variable from null data.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script language="VB" Runat="server">
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("\data\northwind.mdb") & ";"
Dim sEmployeeID, sLastName, sFirstName, sTitle, sTitleOfCourtesy, sBirthDate, sHireDate as String
Dim sAddress, sCity, sRegion, sPostalCode, sCountry, sHomePhone, sExt as String
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
Dim MySQL as string = "Select [LastName] from Employees"
Dim MyConn as New OleDBConnection(strConn)
Dim objDR as OleDBDataReader
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
ddlEmp.DataSource = objDR
ddlEmp.DataTextField="LastName"
ddlEmp.DataBind()
MyConn.Close()
ddlEmp.items.insert(0,"")
end if
End Sub
Sub doGrid()
if ddlEmp.SelectedItem.Text <>"" then
Dim MySQL as string = "Select EmployeeID, LastName, FirstName, Title, TitleofCourtesy, " & _
"BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension " & _
"from Employees where LastName = ''" & _
ddlEmp.SelectedItem.Text & "''"
Dim MyConn as New OleDBConnection(strConn)
Dim objDR as OleDBDataReader
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
empGrid.DataSource = objDR
empGrid.DataBind
MyConn.Close
End If
End Sub
Sub doRest(Source as Object, E as EventArgs)
ph1.visible="True"
Dim MySQL as string = "Select * from Employees where LastName = ''" & _
ddlEmp.SelectedItem.Text & "''"
if ddlEmp.SelectedItem.Text <>"" then
Dim MyConn as New OleDBConnection(strConn)
Dim objDR as OleDBDataReader
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read
sEmployeeID = FixDR(objDR("EmployeeID"))
sLastName = FixDR(objDR("LastName"))
sFirstName = FixDR(objDR("FirstName"))
sTitle = FixDR(objDR("Title"))
sTitleOfCourtesy = FixDR(objDR("TitleOfCourtesy"))
sBirthDate = FixDR(objDR("BirthDate"))
sHireDate = FixDR(objDR("HireDate"))
sCity = FixDR(objDR("City"))
sRegion = FixDR(objDR("Region"))
sPostalCode = FixDR(objDR("PostalCode"))
sCountry = FixDR(objDR("Country"))
sHomePhone = FixDR(objDR("HomePhone"))
sExt = FixDR(objDR("Extension"))
End While
Page.DataBind()
End If
doGrid
End Sub
Function FixDR(sItem) as String
if Not sItem is System.DBNull.Value then FixDR=sItem
End Function
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 3.1">
<title>Master-Detail with DropDownList</title>
</head>
<body>
<form id="form1" Runat="server">
Choose a Last Name from the list:
<asp:DropDownList id="ddlEmp"
AutoPostBack="True"
OnSelectedIndexChanged="doRest"
Runat="server"/><br>
<asp:Datagrid Runat="server"
Id="EmpGrid"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#8080FF"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="8"
BackColor="#C8E1FB"
Font-Name="Arial"
Font-Size="8"
BorderColor="Black">
</asp:DataGrid>
<asp:placeholder ID="ph1" Visible="False" Runat="server">
<b>Emp ID:</b> <%# sEmployeeID %><br>
<b>LastName:</b> <%# sLastName %><br>
<b>FirstName:</b> <%# sFirstName %><br>
<b>Title: </b><%# sTitle %><br>
<b>Title of Courtesy:</b> <%# sTitleOfCourtesy %><br>
<b>BirthDate: </b><%# sBirthDate %><br>
<b>HireDate: </b><%# sHireDate %><br>
<b>Address: </b><%# sAddress %><br>
<b>City:</b> <%# sCity %><br>
<b>Region: </b><%# sRegion %><br>
<b>Postal Code:</b> <%# sPostalCode %><br>
<b>Country:</b> <%# sCountry %><br>
<b>Home Phone: </b><%# sHomePhone %><br>
<b>Extension:</b> <%# sExt %>
</asp:placeholder>
</form>
</body>
</html>
