|
To be able to read database data, the data must first be
loaded into a recordset.
Create an ADO Table Recordset
After an ADO Database Connection has been created, like
demonstrated in the previous chapter, it is possible to create
an ADO Recordset.
Suppose we have a database named "Northwind", we can get
access to the "Customer" table inside the database with the
following lines:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
%>
Create an ADO SQL Recordset
We can also get access to the data in the "Customers" table
using SQL:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn
%>
Extract Data from the Recordset
After an recordset is opened, we can extract data from
recordset.
Suppose we have a database named "Northwind", we can get
access to the "Customer" table inside the database with the
following lines:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn
for each x in rs.fields
response.write(x.name)
response.write(" = ")
response.write(x.value)
next
%>
The ADO Recordset Object
The Recordset Object is used to hold a set of records from a
database table.
Syntax
rs.method
rs.property
The most common Methods and Properties
Methods
Method Description
AddNew Creates a new record
Close Closes a Recordset
Delete Deletes the current record or group of records
MoveFirst Moves to the first record
MoveLast Moves to the last record
MoveNext Moves to the next record
MovePrevious Moves to the previous record
Open Opens a Recordset
Update Saves any changes
Properties
Property Description
BOF Returns true if the current record is before the first
record, otherwise it returns
false
EOF Returns true if the current record is after the last
record, otherwise it returns
false
Fields Contains all of the field objects for the Recordset
Object
RecordCount Returns how many records there are in a Recordset
object
Sort Specifies a comma-separated list of field names the
Recordset is sorted on
ADO Recordset Object
Recordset Object
The Recordset Object is used to hold a set of records from a
database table.
Syntax
objectname.method objectname.property
Methods
Method Description
AddNew
Creates a new record
Cancel
Cancels an execution
CancelBatch
Cancels a batch update. Must happen in batch update mode
CancelUpdate
Cancels any changes. Must be done before an Update
Clone
Creates a copy of a Recordset
Close
Closes a Recordset
CompareBookmarks
Compares two bookmarks
Delete
Deletes a record or a group of records
Find
Searches for a record in a Recordset
GetRows
Copies records into an array
GetString
Returns a Recordset as a string
Move
Moves the position of the current record
MoveFirst
Moves to the first record
MoveLast
Moves to the last record
MoveNext
Moves to the next record
MovePrevious
Moves to the previous record
NextRecordset
Clears the current Recordset and returns the next Recordset
Open
Opens a Recordset
Requery
Updates the data in a Recordset by executing the query again
Resync
Refreshes the data in the current Recordset from the original
database
Save
Saves the Recordset to a file
Seek
Searches the index of a Recordset to locate the row that
matches the
specified values
Supports
Defines whether or not a Recordset supports a specific type of
functionality
Update
Saves any changes of the current record
UpdateBatch
Saves all changes in a Recordset to the database. Used when
working
on a Recordset in batch update mode
Properties
Property Description
AbsolutePage
Specifies on which page the current record is located
AbsolutePosition
Specifies the ordinal position of the current record in a
Recordset
ActiveCommand
Returns the Command Object associated with the Recordset
ActiveConnection
Sets or returns a definition for a connection if the
connection is closed,
or the current Connection object if the connection is open
BOF
Returns true if the current record position is before the
first record,
otherwise false
Bookmark
Sets or returns a bookmark. The bookmark saves the position of
the
current record
CacheSize
Sets or returns the number of records that are cached
CursorLocation
Sets or returns the location of the cursor service
CursorType
Sets or returns the type of cursor
DataMember
Sets or returns the name of the data member referenced by the
DataSource property
DataSource
Specifies an object containing data to be represented as a
Recordset
object
EditMode
Defines the editing status of the current record
EOF
Returns true if the current record position is after the last
record,
otherwise false
Filter Sets or returns a filter for the data in the Recordset
Index Sets or returns the name of the index in effect
LockType Sets or returns the type of locking on records
MarshalOptions Sets or returns which records are to be
transferred back to the server
MaxRecords Sets or returns the maximum number of records to
return to a
Recordset object from a query
PageCount Returns the number of pages in a Recordset object
PageSize Sets or returns how many records are on one page
RecordCount Returns how many records
Methods
The AddNew Method
The AddNew method creates a new record for an updateable
Recordset object.
After you call this method, the new record will be the current
record.
Syntax
recordsetobj.AddNew fieldlist, values
Part Description
fieldlist Optional. A name, or an array of names or
ordinal positions of the fields in the
new record
values Optional. A value, or an array of values for the
fields in the new record
The Cancel Method
The Cancel method cancels the execution of a method call.
Syntax
object.Cancel
The Cancel method terminates different tasks for each object.
The table below shows what task is terminated when the Cancel
method is called:
Object Terminated method
Command Execute
Connection Execute or Open
Record CopyRecord, DeleteRecord, MoveRecord, or Open
Recordset Open
Stream Open
The CancelBatch Method
The CancelBatch method cancels a batch update. Must happen in
batch update mode.
Syntax
recordsetobj.CancelBatch affectrec
Part Description
affectrec Optional. A
value
that specifies how many records this method will affect
The CancelUpdate Method
The CancelUpdate method cancels any changes made to the new
row, current row, or the Fields collection. Must be done
before calling the Update method.
Recordset object
Use this method to cancel any changes made to the current row
or to delete a newly added row.
Record object
Use this method to cancel any changes of Field objects, and to
cancel updates of existing fields.
Syntax
recordsetobj.CancelUpdate
recordobj.Fields.CancelUpdate
The Clone Method
The Clone method creates a copy of a Recordset object and
returns a Recordset object reference.
Syntax
Set rscopy = rsoriginal.Clone(locktype)
Part Description
rscopy The duplicate Recordset object to be created
rsoriginal The Recordset object to be duplicated
locktype Optional. A
value
that specifies the lock type of the original Recordset, or a
read-only Recordset. The valid values are adLockUnspecified or
adLockReadOnly
The Close Method
The Close method is used to close a Connection, a Record, a
Recordset, or a Stream object to free system resources.
When an object is closed, it will not be removed from the
memory. It is possible to change the property settings and
open it again later.
Syntax
object.Close
The CompareBookmarks Method
The CompareBookmarks method compares two bookmarks. This
method returns a
value
that indicates the bookmark's relative values.
Note: The two bookmarks must be within the same Recordset
object, or within a Recordset object and its clone.
Syntax
result = recordsetobj.CompareBookmarks(bookmark1,
bookmark2)
Part Description
bookmark1 The bookmark of the first row
bookmark2 The bookmark of the second row
The Delete Method
The Delete method is used to delete the current record or a
group of records.
Note: To use this method assure that the Recordset object
allows record deletion.
Syntax
recordsetobj.Delete affectrecords
Part Description
affectrecords A
value
that specifies how many records this method will affect.
Default is
adAffectCurrent. Note that the values adAffectAll and
adAffectAllChapters are
not valid arguments to this method
The Find Method
The Find method searches for a record in a Recordset that
satisfies a specified criteria.
Note: A current row position must be set before calling
this method.
Syntax
recordsetobj.Find(criteria, skiprows, direction, start)
Part Description
criteria The column name, comparison operator, and value
to use in the search.
Example: "country='Norway'", "date > #7/22/97#" and "country
like N*"
skiprows Optional. Specifies the row offset from the
current row or start bookmark to
begin the search
direction Optional. A
value
that specifies if the search should begin on the current
record
or on the next record in the direction of the search
start Optional. The starting position for the search
The GetRows Method
The GetRows method copies multiple records into a
two-dimensional array.
Syntax
vararray = recordsetobj.GetRows(rows, start,
fields)
Part Description
rows Optional. The number of rows to retrieve. Default is
adGetRowsRest
(or -1),
which copies the rest of the records in the Recordset If you
omit this argument,
this method will retrieve all the records in the Recordset. If
you specify more
records than are available, this method will return only the
number of available
records
start Optional. What record to start on. It can be a
number or a
BookmarkEnum
value
fields Optional. If you want to specify only the fields
that the GetRows call will return,
it is possible to pass a single field name/number or an array
of field
names/numbers in this argument
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn
'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(2,0)
'This example returns the value of the first
'column in the first two records
response.write(p(0,0))
response.write("<br>")
response.write(p(0,1))
'This example returns the value of the first
'three columns in the first record
response.write(p(0,0))
response.write("<br>")
response.write(p(1,0))
response.write("<br>")
response.write(p(2,0))
rs.close
conn.close
%>
The GetString Method
The GetString method returns a Recordset as a string.
Syntax
Set var = recordsetobj.GetString (format,
num, coldel, rowdel, nullexpr)
Part Description
format Specifies how the Recordset should be converted to
a string. Can be a
StringFormatEnum
value. coldel, rowdel, and nullexpr are
used only if format
is adClipString
num Optional. The number of rows to be converted in the
Recordset
coldel Optional. If specified it is s a column delimiter.
Otherwise it is the tab character
rowdel Optional. If specified it is a row delimiter.
Otherwise it is the carriage return
character
nullexpr Optional. If specified it is an expression used
instead of a null value. Otherwise
an empty string
The Move Method
The Move method moves the current record in a Recordset
object.
Note: This method can be used on all Recordset objects.
Syntax
recordsetobj.Move numrec, start
Part Description
numrec Specifies where to move the current record. This
value must be of data type
Long. Example: If this argument is 3, the current
record moves 3 records
forward. Example: If this argument is -3, the current
record moves 3 records
backward
start Optional. Specifies where to start; can be a String
value, a Variant that
evaluates to a bookmark, or a BookmarkEnum value
The MoveFirst, MoveLast, MoveNext, MovePrevious Method
The MoveFirst method moves to the first record in a
Recordset.
The MoveLast method moves to the last record in a
Recordset.
The MoveNext method moves to the next record in a
Recordset.
The MovePrevious method moves to the previous record in
a Recordset.
Syntax
recordsetobj.MoveFirst
recordsetobj.MoveLast
recordsetobj.MoveNext
recordsetobj.MovePrevious
The NextRecordset Method
The NextRecordset method clears the current Recordset and
returns the next Recordset.
Note: Recordset1 and recordset2 can be the same Recordset
object.
Tip: Use this method to return the result of the next
command, or the next result of a stored procedure that returns
multiple results.
Syntax
Set recordset2 = recordset1.NextRecordset(recordsaffected)
Part Description
recordsaffected Optional. Returns the number of records
affected
The Open Method
The Open method opens a opens a Recordset that holds records
from a table, from a query, or from a saved Recordset.
Tip: Always close the Recordset object (with the Close
method) after using it; to free system resources. Set the
object to Nothing, to completely eliminate it from memory.
Syntax
recordsetobj.Open src, actconn, cursortyp,
locktyp, opt
Part Description
src Optional. Specifies a data source
actconn Optional. Specifies in which connection to open
the Recordset. Can be a
Connection object variable name, or ConnectionString
parameters
cursortyp Optional. Specifies the type of cursor. Can be a
CursorTypeEnum
value.
Default is adOpenForwardOnly
locktyp Optional. Specifies the type of locking. Can be a
LockTypeEnum
value. Default
is adLockReadOnly
opt Optional. Specifies how to evaluate the src
argument if it is not a Command
object, or specifies that the Recordset should be restored
from a file where it
was saved. Can be one or more
CommandTypeEnum
or ExecuteOptionEnum
values. Default is adCmdFile
Example
Create an ADO Table Recordset:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
%>
Create an ADO SQL Recordset:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn
%>
The Requery Method
The Requery method updates the data in a Recordset by
executing the query again.
Tip: Use this method to refresh the contents of a
Recordset.
Syntax
recordsetobj.Requery options
Part Description
options Optional. Specifies how to execute this command.
Can be an
ExecuteOptionEnum
value
The Resync Method
The Resync method refreshes the data in a Recordset from the
original database.
Tip: Use this method to resynchronize the Recordset with
the database. If you want to see changes in the database and
you are using a static or forward-only Recordset this method
is useful.
Note: This method does not re-execute the Recordset, so
new records in the database will be invisible.
Syntax
recordsetobj.Resync affectrecords, resyncvalues
Part Description
affectrecords Optional. Specifies how many records this
method will affect. Can be
an
AffectEnum
value. Default is adAffectAll
resyncvalues Optional. Specifies if underlying values are
overwritten. Can be a
ResyncEnum
value. Default is adResyncAllValues
The Save Method
The Save method saves the Recordset in a file or Stream
object.
Syntax
recordsetobj.Save destination, persistformat
Part Description
destination Optional. Specifies where to save the
Recordset (the path name of a file), or a
reference to a Stream object
persistformat Optional. Specifies the format of the
Recordset (XML or ADTG). Can be a
PersistFormatEnum
value. Default is adPersistADTG (0)
Example
You can save a Recordset in XML format:
<%
set xmlDoc=CreateObject("Microsoft.XMLDOM")
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
'Save the Recordset into a DOM tree
rs.Save xmldoc, 1
%>
The Seek Method
The Seek method searches the index of a Recordset to locate
the row that matches some specified values.
Syntax
recordsetobj.Seek keyvalues, seekoption
Part Description
keyvalues Specifies an array of values
seekoption Specifies the type of comparison to be made
between the columns of the index
and the corresponding keyvalues. Can be a
SeekEnum
value
The Supports Method
The Supports method Defines whether or not a Recordset
supports a specific type of functionality.
This method returns True if the features identified by the
cursoroptions argument are supported, False if not.
Syntax
recordsetobj.Supports(cursoroptions)
Part Description
cursoroptions Specifies what functionality this method
should test for. Can be one or
more
CursorOptionEnum
values
The Update Method
The Update method saves any changes of the current record in a
Recordset.
Note: This method will not work if the Recordset object
does not support updates.
Syntax
recordsetobj.Update fields, values
Part Description
fields Optional. Specifies a single field name, or an
array containing field names or
field positions you wish to update
values Optional. Specifies a single value, or an array
containing values for the field or
fields in the new record
The UpdateBatch Method
The UpdateBatch method saves all changes in a Recordset to the
database. This method is used when you are working on a
Recordset in batch update mode.
If the save operation fails, a run-time error occurs and the
errors are stored in the Errors collection. To locate records
with errors, use the Filter property and the Status property.
Tip: The CancelBatch method
cancels all batch updates.
Note: Not all Recordset objects
supports batch updating.
Syntax
recordsetobj.UpdateBatch affectrecords
Part Description
affectrecords Optional. Specifies which records this
method will affect. Can be an
AffectEnum
value. Note: If you specify the value adAffectGroup it
will
generate an error when there are no visible records in the
current Recordset
Properties
The AbsolutePage Property
The AbsolutePage property specifies on which page the current
record is located. It sets or returns a value from 1 to the
number of pages in the Recordset, or it returns a
PositionEnum
value.
Tip: To get the number of pages in the Recordset, you can
use the PageCount property.
Tip: To divide the Recordset into a series of pages, you
can use the PageSize property.
Note: This property's value is 1 when the current record
is the first record in the Recordset.
Syntax
rsobj.AbsolutePage
The AbsolutePosition Property
The AbsolutePosition property specifies the ordinal position
of the current record in a Recordset. It sets or returns a
value from 1 to the number of records in the Recordset, or it
returns a
PositionEnum
value.
Tip: To get the number of records in the Recordset, you
can use the RecordCount property.
Note: This property's value is 1 when the current record
is the first record in the Recordset.
Syntax
rsobj.AbsolutePosition
The ActiveCommand Property
The ActiveCommand property returns the Command Object
associated with the Recordset.
Note: This property is read-only.
Note: A Null object reference is returned if the Recordset
is not created by a Command Object.
Syntax
rsobj.ActiveCommand
The ActiveConnection Property
The ActiveConnection property tells which Connection Object
the Recordset belongs to.
If the connection is closed, this property sets or returns a
definition for a connection.
If the connection is open this property sets or returns the
current Connection Object.
Syntax
rsobj.ActiveConnection
The BOF and EOF Properties
The BOF property returns True (-1) if the current
record position is before the first record in a Recordset,
otherwise it returns False (0).
The EOF property returns True (-1) if the current
record position is after the last record in a Recordset,
otherwise it returns False (0).
Note: The BOF and EOF properties are set to True if you
open an empty Recordset. RecordCount property is zero.
Note: If a Recordset holds at least one record, the first
record is the current and the BOF and EOF properties are
False.
Syntax
rsobj.BOF
or
rsobj.EOF
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers"
rs.Open sql, conn
%>
<table border="1" width="100%">
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td>
<%Response.Write(x.value)%></td>
<%next rs.MoveNext%>
</tr>
<%loop rs.close conn.close %>
The Bookmark Property
The Bookmark property specifies a bookmark. The bookmark saves
the position of the current record.
To save the bookmark for the current record, assign the value
of the Bookmark property to a variable. To return to the
"bookmarked" record, set the Bookmark property to the value of
that variable.
Note: The Bookmark property is
available only in Recordset objects that support bookmarks.
Syntax
rsobj.Bookmark
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT Companyname, Contactname FROM Customers" rs.Open
sql, conn
rs.MoveFirst 'Store bookmark of the current record
bkmark = rs.Bookmark
rs.MoveLast 'Go to the bookmarked record
rs.Bookmark = bkmark
rs.Close
conn.Close
%>
The CacheSize Property
The CacheSize property sets or returns the number of records
that are cached locally in memory. The value must be greater
than 0. Default is 1.
|