An ADO Connection and Recordset Example

The same example from before will be used to illustrate the flexibility of the Recordset.

Opening a Recordset

________________________________________________________

1 <%
2 Dim ServerPath
3 ServerPath =Server.MapPath(".")
4 'Declare a variable to hold your database file location
5 Dim CONST_DB_FILE
6 'Remember to specify the exact database
7 CONST_DB_FILE = ServerPath & "\..\..\..\databases\my_demo.mdb"
8 'Declare main connection object
9 Dim cn
10 Dim rs
11 Dim sql
12 'Create an ADO database connection object
13 set cn=Server.CreateObject("ADODB.Connection")|
14 'Create an ADO RecordSet Object
15 Set rs= Server.CreateObject("ADODB.RecordSet")
16 'open the database connection
17 cn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & _ CONST_DB_FILE & ";uid=;pwd=;"
18 sql = "Select * from employee_data"
19 rs.Open sql,cn
20 %>

________________________________________________________

A few lines have been added to the original example. In Line 15, the Recordset Object is created and assigned to the variable rs. The datasource connection is opened as was in the earlier examples. In the next step, you open the datasource with a SQL statement and a connection object. At this point, the Recordset contains all of the data from the employee_data table.

The next example will output the data in the same manner as before, but this time uses some of the Recordset’s properties to display more information about the Recordset.

________________________________________________________

1 <%'Begin recordset_access_master.asp
2 'Find the current path of the web server
3 ServerPath =Server.MapPath (".")
4 'Declare a variable to hold your database file location
5 Dim CONST_DB_FILE
6 'Set the variable to the proper location
7 'Remember to specify exact database
8 CONST_DB_FILE = ServerPath & "\..\..\..\databases\my_demo.mdb"
9 'Declare main connection object
10 Dim cn
11 'Declare main Recordset Object
12 Dim rs
13 'The following Subroutine will setup cn as an object if it is not already one
14 'The subroutine then opens the connection to your datasource
15 Sub OpenDb
16 if Not(IsObject(cn)) then ' if you have never opened the db before then open it
17 'Create an ADO database connection object
18 Set cn=Server.CreateObject ("ADODB.Connection")
19 'Open the database connection object
20 cn.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" &_
21 CONST_DB_FILE & ";uid=;pwd=;"
22 Else 'do nothing
23 End if
24 End sub
25 Sub OpenRs(SQLString)
26 Opendb
27 if Not(IsObject(rs)) then ' if you have never assigned rs an object
28 Set rs=Server.CreateObject("ADODB.RecordSet")
29 end if
30 rs.Open SQLString,cn,1,1
31 end sub
32 %>

________________________________________________________

The original access master file has been modified to include some additional objects and functions. The OpenRs function allows you to open a Recordset by calling a function with a SQL String. In Line 26 you verify that the database is open before trying to access any data with the Recordset Object. In Line 27, you are checking whether the Recordset Object exists. If the object does not exist, or has never been assigned to an ADO Recordset Object, then you should assign it to an ADO Recordset object.

In Line 30, the datasource connection is opened with the SQL String that was passed into the function. The second parameter to be passed to the Recordset object is the actual datasource connection already opened from Line 26. The third parameter allows you to open this Recordset with the cursor type as adKeySet. The fourth parameter sets the lock type to adLockReadOnly.

The combination of the third and fourth parameters let you take advantage of the Recordset’s additional features. Please review the following example:

________________________________________________________

1 <!-- Begin test_recordset_access_master.asp -->
2 <!--#INCLUDE VIRTUAL="/examples/asp/recordset_access_master.asp"-->
3 <html>
4 <head><title></title>
5 <body bgcolor=#ffffff>
6 <P>The code produces the following results on the sample database
7 <TABLE border=3 cellPadding=5 cellSpacing=5 width="80%" style="LEFT: 10px; TOP: 716px">
8 <tr>
9 <TD>Employee Number</TD>
10 <TD>First Name</TD>
11 <TD>Last Name</TD>
12 <TD>Title</TD>
13 </tr>
14 <%
15 Openrs "Select * from employee_data"
16 while not rs.EOF
17 Response.Write "<tr><td>"& rs("Employee_Number") & _
18 "</td><td>" & rs("First_Name") &_
19 "</td><td>" & rs("Last_Name") & _
20 "</td><td>" & rs("title") & "</td></tr>"
21 rs.movenext
22 wend
23 %>
24 </TABLE>
25 </P>
26 <P>This Recordset's Record Count is: <%=rs.RecordCount%> Records.<BR>
27 This Recordset's Page Count is: <%=rs.PageCount%> Pages.<BR>
28 The Recordset's Page Size is: <%= rs.PageSize%> Records.
29 </P></BODY></HTML>

________________________________________________________

Not much of the code from the Access Test ASP has changed. To get the data into your Recordset, you will need to make a single function call. This is illustrated in Line 15. From this point, you can access the new Recordset in the same fashion you did earlier. You are ready to access additional information about the Recordset. Some of this can be seen in Lines 26-29. You will be able to see:

The ADO Recordset provides in-depth details from the information delivered to your customers. The Recordset allows you to do many more advanced topics beyond the scope of this guide. For more information about ADO and its objects, please refer to the Microsoft Developer’s Network, scripting section.