Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted May 30, 2000

Introduction to Database Manipulation with ADO - Page 4

By Benoy Jose

RecordSet

In the earlier example the data returned from a SQL query was assigned to a RecordSet even though the RecordSet object was not created. The RecordSet object was explicitly created when the conn.Execute statement was executed. Alternatively a RecordSet object can be explicitly initialized and the additional flexibility of the RecordSet used. A RecordSet contains a collection of records with the first record as the current record.

Click for code example 4.

The RecordSet object has a few extra optional parameters to help the programmer get more control over how the data is accessible to different users and how data in the RecordSet can be manipulated. The first parameter is the type of cursor used. The cursor defines the types of manipulations that can be done on an open RecordSet.

There are four types of cursors:

  • adOpenForwardOnly: This allows only forward movement in the RecordSet
  • adOpenKeyset: This allows both forward and backward movement in the RecordSet. It also reflects delete and update operations made on the records by other users
  • adOpenDynamic: This allows both forward and backward movement in the RecordSet. It reflects the operations done on the records by other users.
  • AdOpenStatic: This allows both forward and backward movement in the RecordSet. It does not reflect any operations done on the records by other users.

If omitted the adOpenForwardOnly is taken as default cursor.

The other optional parameter is the locking type. The locking type tells the database how to deal with situations where more than one user tries to manipulate a record.

The options are:

  • adLockReadOnly: The records are read only.
  • adLockPessimistic: The records are locked immediately upon editing.
  • adLockOptimistic: The record is locked when the RecordSet's Update method is called.
  • adLockBatchOptimistic: The records are batch updated.

If omitted adLockReadOnly is taken as the default locking type.

The RecordSet object has a number of methods that help navigate through the RecordSet.

Move NumofRecords Moves the specified number of records forward or backward
MoveFirst Moves to the first record
MoveNext Moves to the next record
MovePrevious Moves to the previous record
MoveLast Moves to the last record
Close Closes the recordset
Open Opens the cursor on the recordset

Properties:

  • BOF - Indicates that the current position is before the first record.
  • EOF - Indicates that the position is after the last record.

Apart from the above methods and parameters the RecordSet has many additional methods like AddNew to add a new record, Update to save changes to a record, UpdateBatch to save changes to all the records when the RecordSet is in the batch-update mode and so on. Most of the other methods are for special cases.

Displaying the results:

The RecordSet object has a Fields Collection that can be used to manipulate the columns of a table. E.g. RS.Fields.Count gives the number of columns in the table. The data in a particular column in a record can be accessed using RS("ColumnName") or RS(0).

<HTML>
<BODY>
<%
Set conn=Server.CreateObject("ADODB.Connection")
Set RS   =Server.CreateObject("ADODB.RecordSet")
conn.Open "FILEDSN= C:\myAspDir\testDsn.dsn","username","password"
RS.Open "SELECT * FROM MYTABLE WHERE MYRECORDNO>0", conn
%>
<TABLE>
<TR>
 <% FOR i=0 to RS.Fields.Count -1   %>
 <TH><% = RS(i).Name%></TH>
 </TR>
   <% WHILE NOT RS.EOF %>
  <TR>
  <% FOR i=0 TO RS.Fields.Count - 1%>
  <TD><% = RS(i) %></TD>
  <% Next%>
 </TR>
  <%
   RS.MoveNext
  WEND
   RS.Close
  conn.Close
  %>
</TABLE>
</BODY>
</HTML>


MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM