When we first start using ASP.Net, many people first start working with a DataGrid due to its apparent ‘on the surface’ simplicity. Of course as learning progresses, we find out just how powerful it really is. Also, we learn about the DataSet and how to make it the DataSource of the DataGrid. However, many times, other methods of displaying the results in the DataSet are overlooked even though the DataGrid is not exactly what’s needed.
This code sample uses the SQL Managed Provider (Employees table in the Northwind database) to show 2 other ways to display the results from a DataSet (a DataList and assigning the data to a variable then, using a server control’s text property to display the records however we need.
To use this on your local computer, just copy the code below to a new page on your system. Then, change the connection string to your database. If you are using MS Access, just copy the code to the DataBase Code Conversion Tool on this web site.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.2">
<title>3 Ways to Display a DataSet</title>
<script language="VB" runat="server">
Dim sEmps as String
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=NorthWind"
Sub Page_Load(Source as Object, E as EventArgs)
Dim MySQL as string = "Select Employees.FirstName, Employees.LastName, Employees.Title, " & _
"Employees.BirthDate from Employees"
Dim MyConn as New SQLConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"Employees")
''These next 2 lines assign the data in the dataset to the Datalist (MyDataList)
MyDataList.Datasource=ds.Tables("Employees").DefaultView
MyDataList.DataBind()
''These lines assign the data to a variable, which is then assigned to the text property of a literal control(litEmps)
Dim dr As DataRow
For Each dr In ds.Tables("Employees").Rows
sEmps+="<b>" & dr("firstname") & " " & dr("lastname") & " </b> - " & dr("Title") & "<br>"
Next
litEmps.text=sEmps
''then, here we assign the data to a DataGrid, much like the way we did in the example for the DataList:
MyDataGrid.Datasource=ds.Tables("Employees").DefaultView
MyDataGrid.DataBind()
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
<div align="center"><table> <tr>
<td align="Left" valign="Top"><b><i><font Color="#0000FF">Using a DataList:</font></i></b>
<asp:DataList runat="server"
Id="MyDataList"
GridLines="None"
cellpadding="2"
cellspacing="2"
Headerstyle-BackColor="#8080C0"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="8"
Font-Name="Arial"
Font-Bold="false"
Font-Size="8">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "firstname") %>
<%# DataBinder.Eval(Container.DataItem, "lastname") %>
- <b><i><%# DataBinder.Eval(Container.DataItem, "Title") %> </i></b>
</ItemTemplate>
</ASP:DataList>
</td>
<td align="Left" valign="Top"><b><i><font Color="#0000FF">Using a variable:</font></i></b><br>
<asp:Literal ID="litEmps" runat="server"></asp:literal></td>
</tr>
<tr>
<td align="center" valign="Top" Colspan="2"><hr>
<b><i><font Color="#0000FF">Using a DataGid:</font></i></b>
<asp:Datagrid runat="server"
Id="MyDataGrid"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#8080C0"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
Headerstyle-Font-Bold="true"
BackColor="#8080FF"
Font-Name="Arial"
Font-Size="10"
AlternatingItemStyle-BackColor="#CFCFCF"
AlternatingItemStyle-Font-Name="Arial"
AlternatingItemStyle-Font-Size="8"
BorderColor="Black">
</asp:DataGrid>
</td>
</tr>
</table></div>
</Form>
</body>
</html>