How to use the ADO Recordset to query the records?

Normally, we use the ADO Recordset to query the result and we have few ways to do it:

-Using Connection to execute a SQL command to return a set of record:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = conn.execute("SELECT * FROM User") 'Use the SQL to get the record from the User table.

%>

- Using the Connection to direct open the table by using table name:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = conn.execute("User") 'Use the Table Name to get the record from the table

%>

- Create a new RecordSet by using Server.CreateObject:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "User",conn,1,3 'Using Open method to get the record form the table.

%>

The Syntax of Open Method is

Recordset.Open Source, ActiveConnection, Cursortype, Locktype, Options

Source

The table name, SQL command, Command Object

ActiveConnection

An Exist connection, that connects to your database

CursorType

0 - adOpenForwardOnly (Default) 'cannot being update the record and priview the record in forward only

1 - adOpenKeyset 'Can be Add new, edit and delete; move to any record;

2 - adOpenDynamic 'Can be Add new, edit and delete; move to any record; Jet(Access Database Engine) still not support this cursortype;

3 - adOpenStatic 'Can be move to any record; but cannot modify the record

LockType

1 - adLockReadOnly 'Read only recordset

2 - adLockPessimistic 'Normally direct lock the information source.

3 - adLockOptimistic 'Normally will lock the records when call the Update method

4 - adLockBatchOptimistic 'only use when doing the batch processing.