Introduction to Database Manipulation with ADO

Tuesday May 30th 2000 by Benoy Jose
Share:

The average surfer today demands personalized, customized, interactive Web pages that change content on user request. Learn how to fill that demand by using ADO to connect to any ODBC compliant database.

The advent of the internet has transformed the world into a global market. With more and more people hooking on to the internet the expectations of speed and efficiency are far increasing. Gone are the days when surfers browsed through static HTML pages. The average surfer today demands personalized web pages i.e. customized and interactive web pages that change content depending on user request. This requires the storage of non pertinent information which includes information on users and information required by them in databases and its delivery in customized modes. Dynamic content can be offered to users in numerous ways such as CGI with Perl, ASP, Servlets, ColdFusion, JSP, Livewire etc.

Microsoft combined ActiveX Data Objects (ADO) with their proprietary scripting language VBScript to offer a simple yet powerful solution to the problem. ADO can be used to connect to any ODBC compliant database. This is a positive feature as most of the popular databases are ODBC compliant so all we need is a ODBC driver for the corresponding Database. ADO can also be used to connect and retrieve data from spreadsheet files like Excel and text or other plain format data files. ASP-ADO is a perfect solution for small and medium sized web sites owing to its cost effectiveness and minimum development cycle time.

ADO resides on top of the OLE DB which takes care of the medium through which the database is to be connected. This leaves the developer to just bother about how to use the ADO. Issues such as multiple connections, reusable connections and performance bottlenecks are automatically taken care of when ADO 2.0 is used in conjunction with IIS 4.0.

Relationship of ADO to ASP and ODBC
(Click for full size image)

The ADO Interface

ADO is built upon another layer called the OLE-DB. ODBC is one popular OLE-DB provider. ADO capabilities can vary depending upon the OLE-DB provider. Ado Capabilities can also vary widely depending upon the ODBC driver used. For example most ODBC drivers do not support all the cursor types, (cursor types are discussed later).

Exploring the ADO

The ADO Object model consists of seven objects namely:

The Connection, Command and Recordset objects each have a collection of properties. The Connection object also maintains a collection of Error objects detailing the errors we encounter while making database connections. Each member of this collection is a Field object which in turn contains information about individual fields in the Recordset.
However the Connection, RecordSet and Command objects are the most commonly used objects for interaction with databases. The different objects are used depending on the complexity of the data manipulation.

The Connection object is used to establish a connection to a database and can be used for simple database queries like select, insert, delete, update etc. It can be also used for other complex manipulations like stored procedures, but they are better done by other objects.

The RecordSet object is used for updating records, querying multiple tables and fields. Unlike the Connection object, the RecordSet gives more information on the transaction like the number of records updated, records deleted etc.

The Command object is capable of running stored procedures and SQL statements with multiple and complex parameters. 

The Field object represents an individual field in the RecordSet. It is generally used in conjunction with the RecordSet object for more flexibility in programming.

The Parameter object represents a parameter in a SQL stored procedure.

The Error object represents the ADO errors.

The Property object provides properties of different data providers like MS-Access, SQL Server etc.

Establishing a Connection

To make a connection to a database a data source (DSN) corresponding to the database used is needed. The connection object then uses this data source to connect to the database and retrieve data. There are three types of data sources: User DSN, System DSN and File DSN. The DSN provides connectivity to the database through a specified ODBC driver. (An ODBC driver corresponding to the database used is to be installed on the web server).

File DSN is preferred over the other two as it stores the data source information in a separate file which can be ported to a different machine easily.

To open a connection to the database an instance of the Connection object is required, then subsequent manipulations can be done using the Connection object.

Click here for code example 1.

The first statement gets an instance of the Connection object. The open method of the Connection object is used to call the DSN. The location of the DSN could be any directory on the server provided the full path is specified. If the database requires authentication then the username and password need to be supplied.

Click here for code example 2.

The File DSN may alternatively be placed in a session variable so that a change in the path of the DSN is reflected in all the pages without changing the code.

The conn.Execute statement executes a SQL query; if the query were a select statement that returned records then it is assigned to a RecordSet object and subsequent manipulations are done on it.

Click here for code example 3.

The conn.Close closes the connection to the database. It is a good practice to close database connections after use.

The Execute method has two optional parameters. The RecordsAffected parameter returns the number of records affected by the SQL query. The Options parameter indicates the type of SQL statement. The constant used with the Options parameter helps the ADO in interpreting the database call and executing it efficiently. The constants used are adCMDText to specify that it is a textual command, adCMDTable to indicate a table, adCMDStoredProc to indicate its a stored procedure and adCMDUnknown if the type is unknown. AdCMDUnknown is taken as default if this parameter is not specified.

Connection Object methods and Properties:

Methods:

Open Opens a new connection
Close Closes the connection
Execute Executes a SQL query or a stored procedure
BeginTrans Begins a new transactions
CommitTrans Saves any changes made and ends the transaction
RollbackTrans Cancels any changes made and ends the transaction
OpenSchema Provides information on database schema like tables, rows, columns etc.

Properties:

Attributes Controls whether to begin a new transaction when a existing one ends
CommandTimeout Time to wait when executing a command before terminating the attempt and returning the error
ConnectionString Information used to create a connection to the data source
ConnectionTimeout Time to wait when making a connection before terminating the attempt.
Provider Sets or returns the name of the provider.

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> 
                    

The Command Object

The Command object represents a command. The ResultSet and the Connection objects execute SQL commands which are command strings. Instead of the Command string a Command object can be used. The Command string can be anything from a simple SQL command to a complex stored procedure. But this does not make the Command object anything special as the RecordSet can do all this too. The Command object is best used for complex stored procedures with in parameters and out parameters. The Command object can be used to execute bulk operations and manipulate the structure of the database.

Click here for code example 5.

Command object methods and properties:

The first step is to specify the ActiveConnection we want to use as specified in line 3. Once we have established the active connection we can use the Command object in the same way as the Connection object. We don't need to explicitly open the data source or close it after use, we can use the execute method directly as in line 6.

The different properties of the Command object help us specify some parameters outside the Execute method to get more control over the operation; like in line 4 we have specified the Command text and in line 5 we have indicated the Command Type as a stored Procedure. We can also specify the CommandTimeout property. This determines how long ADO has to wait for the command to execute. The default value is 30. If the value is set to 0 ADO will wait indefinitely until the command executes.

The command object has methods like CreateParameter which creates a new parameter object in the Parameters collection and an Execute method that runs the SQL statement or stored procedure specified in the CommandText.

Parameters Object:

If a SQL query requires parameters then it can be supplied by adding them to the Command objects' parameters collection or by creating them on the fly as we execute the SQL query.

myCommand.Execute RecordsAffected, Parameters

An array of parameters can be passed to the Parameters object while the query is being executed.

myCommand.Execute Array (parameter1, parameter2 etc...)

Instead of passing parameters on the fly we can hold the parameters in the parameters collection. For this the parameter collection has methods like Append, Delete, Refresh, and properties like Count, Item etc. To add a new parameter an instance of the Parameter is made and its property values are set and finally the Append method of the Command object is called to append the parameters to it like it is done in line 5.

The parameters are not limited to stored procedures, they can also be used in SQL statements in Access where parameters are indicated by being placed in square brackets within the statement. This can be used as an alternate for SQL statements in which the 'where' clause changes dynamically.

We need to specify Type and Size for the parameters used in the Parameter object. For String values it is the size and for numeric values the precision needs to be specified.

The Property Object:

There are two kinds of properties: Built-in properties and Dynamic properties. The Built-in properties are those defined by ADO for objects as they are created. These are part of the object itself and can be used to set or get some property to the object. These are available irrespective of the provider being used.

The Dynamic properties are additional properties specifically provided by the provider.

Error Collection:

The Errors collection holds all of the Error objects for a specific Connection. Generally, if an ADO operation encounters errors, one or more Error objects are automatically added to the Errors collection. More detailed error messages can be obtained from these error objects instead of vague Runtime errors. The Errors collection has a clear method to clear all the errors. It also has two properties. The count property returns the number of error objects in the collection and the item property is used to retrieve the contents of the error objects in the collection. Two types of error objects are thrown depending on the type of error. Errors are returned by the data source whereas Err objects are created at compile time or runtime by VBScript or by an automation object like ADO.

Apart from critical errors that stop execution some properties and methods return warnings to the Errors collection . Warnings don't stop execution, but that could be due to small problems like data conversion, which could affect the precision of the result.

The Error object has properties like Description for detailed description , Source for the source of the error, Number for the ADO error code number etc.

Click here for code example 6.

Bottomline:

Ado is the easiest way to provide dynamic content on the Internet. It is best suited for small and medium sized web sites as the development time and costs are minimum compared to other languages. Moreover its default support on Windows NT makes it an ideal choice for companies intending to do business on the net. ASP and ADO seamlessly combine with other Microsoft tools like the Siteserver, MTS and Commerce server making it scaleable and robust.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved