Recordset Object

Recordset

A recordset is a set of records, that's it. Databases store tables, and each table stores records. So a recordset is just some, or all, of those records.

A record is just a collection of related information. For example, if you are storing information about customers, you probably want the following:

  • First Name
  • Last Name
  • Address
  • State
  • Zip Code
  • Email
  • etc...

Each individual piece of information in a record is termed a field. So in the example above, each bullet is a field.

A key is how we identify individual records.

DSN String - Data Source Name String

In order to work with a Database you need to know two things:

  1. What Database are your working with (Access, SQL Server, Oracle, Sybase, etc...)
  2. Where is the Database located at

"DSN-based" Connection set up through the ODBC Control Panel:

var strDSN = "DSN=YourDSNname"

"DSN-less" Connection:

For SQL Server:
var strDSN = "Driver={SQL Server}; Server=YourServer; Database=YourDB;
                        UID=YourUID; PWD=YourPWD;"
                           |                          |
                     User ID             Password

For Access Database:
var strDSN = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=YourDB;"

YourDB is the full pathname of your Access Database.

Database Connection - Create & Open a Connection to a Database

var conn = Server.CreateObject("ADODB.Connection");

conn.Open(strDSN);

Recordset Object:

Recordset.Open (Source, ActiveConnection, CursorType, LockType, Options)

Can be a table, SQL statement, etc..    What database connection are you going to use?
                         |         ______________|
                         |         |
rsCust.Open("pif", conn, adOpenDynamic, adLockOptimistic, adCmdTable);
                         |                                                                                    |
           Name of the Table            What is the 1st parameter - table, SQL statement, etc...?

CursorType Property - recordset types

  • adOpenForwardOnly - gives you an updateable, non-scrollable recordset.
  • adOpenKeyset - gives you a scrollable keyset recordset
  • adOpenDynamic - gives you a scrollable, fully dynamic recordset
  • adOpenStatic - gives you a read only, scrollable recordset

LockType Property

  • adLockReadOnly - gives a read only recordset, where no updating is allowed
  • adLockPessimistic - gives an updateable recordset, that locks the records as soon as you start editing
  • adLockOptimistic -gives an updateable recordset, where the lock is only placed just before you try and update the record.
  • adLockBatchOptimistic - gives optimistic locking for batch updates

Options - specify how the Source property is interpreted, and can be one of the following:

  • adCmdText - to indicate that Source holds command text, for example, a SQL command
  • adCmdTable - to indicate that Source holds the name of a table
  • adCmdStoredProc - to indicate that Source holds the name of a stored procedure or query
  • adCmdUnknown - to indicate that Source holds an unknown type.