ASP ADO Source Code Example

<%@ language=VBScript %>
<% option explicit %>

<%
Const cDSN = "Driver=SQL Server;Server=130.66.76.169;Database=Pubs;UID=student;pwd=student"

Dim rs 'Recordset
Dim mySQL 'SQL Statement
Dim i
Dim s
%>

<html>
<head>
<title>ASP/ADO Listing Records</title>
</head>
<body bgcolor="#FFFFFF" text="#000000">

<h2>Authors</h2>
<p>Click on the AU_ID links to edit records</p>
<p><a href="AspAdo2.asp?AUID=NEW&ACTION=NEW">Add a New Author</a></p>

<%
'Create a recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Specify the information we want
mySQL = "Select au_id, au_lname, au_fname From Authors Order By au_lname, au_fname"

'Open a firehose cursor (connection created with recordset)
rs.ActiveConnection = cDSN
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseServer
rs.Source = mySQL
rs.Open

'Now use the standard code to knock out the recordset to a table.
Response.Write("<table width='100%' Border='1'>")

'Write out Column Names
Response.Write("<tr>")
For i = 0 to rs.Fields.Count - 1
Response.Write("<td>")
S = Trim(cStr(rs.Fields(i).Name & ""))
Response.Write("<b>" & S & "</b>")
Response.Write("</td>")
Next

Response.Write("</tr>")

'Write out all rows values
While not rs.EOF
    Response.Write("<tr>")
    For i = 0 to rs.Fields.Count - 1
        Response.Write("<td>")
        S = Trim(cStr(rs.Fields(i).Value & ""))
        If len(S) = 0 Then S = " "
        If i = 0 then
            Response.Write("<a href=AspADO2.asp?AUID=" & S & ">" & s & "</a>")
        else
            Response.Write(S)
        end if
        Response.Write("</td>")
    Next
    Response.Write("</tr>")
    rs.Movenext
Wend

Response.Write("</table>")

'Don't forget to close and dispose your recordset objects
rs.Close
set rs = Nothing

%>
<p><strong><small>View Source</small></strong></p>
</body>
</html>