This code sample uses the SQL Server Managed Provider to bind a DataList with a DataReader, showing only certain columns/fields from the table. It uses the Employees table of the Northwind Database. Also, it shows how you can use the Repeatcolumns property and Templates (both Header and Item Templates) to format the data on the page and block or show certain fields in the table.
To use this on your system, all you need to do is copy the code directly to a new page and change the connection string to match your needs on your system. Also, if you have MS Access, instead of SQL Server, be sure to visit our DataBase Code Conversion Tool to convert the SQL Server items to OleDb.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.2">
<title>Populating a DataList with a DataReader</title>
<script language="VB" runat="server">
Dim strConn as String
Sub Page_Load(Source as Object, E as EventArgs)
strConn = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
Dim MySQL as string = "Select FirstName, LastName, Title, BirthDate from Employees Order by LastName"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyDataList.DataSource = objDR
MyDataList.DataBind()
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
<asp:DataList runat="server"
Id="MyDataList"
GridLines="None"
cellpadding="2"
cellspacing="2"
Font-Name="Arial"
Headerstyle-BackColor="#E0E0E0"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Bold="True"
Headerstyle-Font-Size="14"
Headerstyle-HorizontalAlign="Center"
Font-Size="10"
RepeatColumns="3"
Width="100%">
<HeaderTemplate>
Employee Names/Birthdates
</HeaderTemplate>
<ItemTemplate>
<b>Name: </b><%# Container.DataItem("FirstName")%> <%# Container.DataItem("LastName")%><br>
<b>Title: </b><%# Container.DataItem("Title")%><br>
<b>BirthDate: </b><%# formatdatetime(Container.DataItem("BirthDate"),vbshortdate)%><p>
</ItemTemplate>
</ASP:DataList>
</Form>
</body>
</html>