The first code block in the following example is typical of a type of ASP application that uses ADO to read and manipulate a recordset returned from a single SQL query. It uses the ADO Recordset object to read the data records returned from the Northwind sample database provided with Microsoft Access. The code will be contained in a file with an .asp file extension.
[Visual Basic]
< %@LANGUAGE=VBSCRIPT% >
<!
This ASP example uses ADO to read records from a database and print two
fields from all returned records to an ASP page. Connection to the Northwind database is through an ODBC system data source (DSN.
>
<html>
<body>
<%
dim ADOconn, ADOrs, sqlstr
sqlstr="SELECT * FROM Employees;"
set ADOconn = Server.CreateObject("ADODB.Connection")
ADOconn.Open "DSN = Test"
set ADOrs = ADOconn.execute(sqlstr)
if ADOrs.BOF and ADOrs.EOF then ' Query didn't return any records.
Response.Write("No Records.")
else
ADOrs.MoveFirst
Do While Not ADOrs.EOF
Response.Write(ADOrs("FirstName") & " " _
& ADOrs("LastName") & "<br>")
ADOrs.MoveNext
Loop
Response.Write("<p>End of data.")
end if
ADOrs.close
set ADOrs = nothing
%>
</body>
</html>
The following example illustrates the minimum changes required to convert the previous example into an ASP.NET application. Most changes are necessary to comply with the new Visual Basic syntax. This file can be renamed with an .aspx file extension and will run with ASP.NET. Modified lines of code are shown in bold. Notice that the <%@ Page > directive with the aspcompat=true attribute was added on the first line.
[Visual Basic]
< %@Page aspcompat=true Language = VB%>
<!
This example uses ADO to read records from a database and print two
fields from all records in the database to an ASP.NET page.
The database is located on the server and connection is through an ODBC system data source (DSN.
>
<html>
<body>
<%
dim objConn, rs, sqlstr
sqlstr="SELECT * FROM Employees;"
objConn = Server.CreateObject("ADODB.Connection") ' Set removed.
objConn.Open("DSN=TEST") ' Parentheses added.
rs = objConn.execute(sqlstr) ' Set statement removed.
Response.Write("<p>ADO Test</p>")
if rs.BOF and rs.EOF then ' Query didn't return any records.
Response.Write("No Records")
else
rs.MoveFirst
Do While Not rs.EOF
'Specify Value property.
Response.Write(rs("FirstName").Value _
& " " & rs("LastName").Value & "<br>")
rs.MoveNext
Loop
Response.Write("<p>End of data")
end if
rs.close
rs = nothing ' Set statement removed.
%>
The next example is an ASP.NET application that uses ADO.NET to read records from the same Northwind database as the previous example. The output generated by this code is equivalent to the output of the previous example, and has been modified to conform to the ASP.NET code block convention.
The example creates an ADO.NET DataSet object, which in this case contains a data table that can be used in much the same way as an ADO recordset. Note that a data set can be composed of one or more collections of DataTables, DataRelations, and Constraints that make up a memory-resident database, so ADO.NET data sets are much more flexible than ADO recordsets.
In order to use ADO.NET, the System.Data and System.Data.OleDb namespaces need to be imported. If the data source is a SQL Server database, import the System.Data.SqlClient namespace instead of System.Data.OleDb. For more information about using ADO and the SQL .NET Data Provider's connection object, see Managing Connections.
[Visual Basic]
< %@Import Namespace="System.Data"%>
< %@Import Namespace="System.Data.OleDb"%>
<!
This example uses ADO.NET to read records from a database and print two
fields from all returned records to an ASP.NET page. The database
is located on the local server.
>
<html>
<Script Language=VB Runat=Server>
Sub Page_Load(Sender As Object, e As EventArgs)
Dim MyConnection As OleDbConnection
Dim MyCommand As OleDbDataAdapter
dim MyDataset As DataSet
dim MyTable As DataTable
dim loop1, numrows As Integer
dim sqlstr As String
sqlstr = "SELECT * FROM Employees;"
' Create a connection to the data source.
MyConnection = New OleDbConnection("Provider=SQLOLEDB;" _
& "server=localhost;"Integrated Security=SSPI;" _
& "Initial Catalog=Northwind")
' Create a Command object with the SQL statement.
MyCommand = New OleDbDataAdapter(sqlstr, MyConnection)
' Fill a DataSet with data returned from the database.
MyDataset = New DataSet
MyCommand.Fill(MyDataset)
' Create a new DataTable object and assign to it
' the new table in the Tables collection.
MyTable = New DataTable
MyTable = MyDataset.Tables(0)
' Find how many rows are in the Rows collection
' of the new DataTable object.
numrows = MyTable.Rows.Count
If numrows = 0 then
Response.Write("<p>No records.</p>")
Else
Response.Write("<p>" & Cstr(numrows) & " records found.</p>")
For loop1 = 0 To numrows - 1
' Print the values of the two columns in the Columns
' collection for each row.
Response.Write(MyTable.Rows(loop1).Item("FirstName") _
& " " & MyTable.Rows(loop1).Item("LastName") & "<br>")
Next loop1
End If
Response.Write("<p>End of data.</p>")
End Sub
</Script>
</html>
In situations where a database query (or even a multi-table join query) returns a single recordset, a single DataTable (MyTable in this example) can be used in much the same way as an ADO recordset.
Refer to "NET FRAMEWORK SDK Documentation"