ASP ScriptingJava ScriptingCGI ScriptingHTMLJavaLanguage CERP Education Links

Connection and Command Object

  The Connection Object

     The Connection Object allows ADO to work in a stateless environment. It stores the information required to connect to the data source. The Connection Object is instantiated using the Server . CreateObject statement. As with all objects, you have to use the Set keyword when assigning it to an object variable.

 Set objMyConn = Server.Create0bject("ADODB.Connection")

     Once the object has been instantiated, you can use this to create connections to the database using the Open method. You need to provide it the necessary Connection String or DSN. This attaches the information required to connect to the database (the database, user Id and password) with the Connection Object.

 ObjMYConn.Open("Mydatabase", "user" , "password" )

     Once the connection is open, you can start communicating with the database. When you are finished, you should close the connection using the Close method.

 objMyConn.Close

     Closing the connection does not destroy the Connection Object and it can be opened again., Even if you do not close the connection, ODBC will close the connection after 60 seconds of inactivity. If you are sure that you will not require the Connection Object any more, you can destroy it by setting it to Nothing.

 Set objMyConn=Nothing 

  Data Access

     The Connection Object can be used to retrieve a recordset. However, this would be a very low level' cursor. The cursor is read- only and allows only forward movement through it, which means that you can move from the current record to those after it, not before. This kind of a cursor can be used when you don't wish to update the database and simply display a series of records.
The Connection Object can execute SQL statements or other database commands on the database. When no recordset is returned from this, for example, in delete or update operations, use the following syntax:

 <Connection>.Execute CommandText, RecordsAffected, Options

For a command that returns recordsets, use the following syntax:

Set Recordset = <Connection>.Execute (CommandText, RecordsAffected, Options) 

     CommandText is a string containing the command to be executed on the database. The optional RecordsAffected is a long variable to which the provider returns the number of records that the operation affected. Options, again optional, is a long value that indicates how the provider should evaluate CommandText.

The following example should make this clear: 

<%
Set objMyConn = Server.CreateObjeCt("ADODB.Connection") 
objMyConn.Open "MyDatabase"
SQLString = "DELETE FROM.MyTable WHERE MyField > 500" 
objMyConn.Execute SQLString, lngNumRecords, adCmdText 
%> 

  The Command Object

     The Command Object optimizes performance of parameterised queries and stored procedures. The Command Object is instantiated with the CreateObject method.

Set objMyCommand =Server.CreateObject("ADODB.Command")

     You can connect to a data source using the Command Object with or without a Connection Object. To set its connection over a Connection object, assign the Connection Object to ActiveConnection property of the Command Object:

Set objMyCommand.ActiveConnection = objMyConn

     To connect to a data source without creating a Connection Object, you can assign a DSN or a connection string to the ActiveConnection property. You can also create a DSN at runtime. See the following examples:

Assigning a DSN: 
 objMyCommand.ActiveConnection = "MyDSN" 

 Assigning a connection string:
  objMyCommand.ActiveConnection.= "dsn=MyDSN; database=MyDatabase; uid=UserId; pwd=Password ;"

 Creating a Connection:
 objMyCommand.ActiveConnection = "DRIVER=(Microsoft Access Driver (*.mdb)}; DBQ=C:\Inetpub\MyDatabase.mdb"

 
     When you execute either of these, the Command Object implicitly creates its own Connection Object to connect to the database. Once you are connected to the database, you can start communication with it.

  Data Access

    The Command Object can return read-only cursors, much like the Connection Object. The main use of the Command Object is to access queries and stored procedures within the database. For executing a command that does not return a recordset, use the following syntax:

<Command>.Execute RecordsAffected, Parameters, Options

For executing commands that return recordsets, use the following syntax:

Set recordset = <Command>.Execute (RecordsAffected, Parameters, Options)

     RecordsAffected is a long variable to which the provider returns the number of records that the operation affected. The optional Parameters argument is an array of parameter values passed with an SQL statement. Options, again, optional, is a long value that indicates how the provider should evaluate the CommandText property of the Command object.

  The Parameters Collection

     The Command object has a parameters collection made up of Parameter objects. Using the Refresh method on the Parameters collection retrieves parameter information for the stored procedure specified in the Command Object from the provider. However, this method does not work with all databases (Microsoft Access, for example).
A safer option is to use the CreateParameter method to create Parameter objects with appropriate settings. Use the Append method to add them to the Parameters collection. This lets you set and return parameter values without having to request the database for the parameter information. The Delete method removes Parameter objects from the Parameters collection if necessary.

To create a parameter, user the following syntax:

Set prmMyParameter = <Command>.CreateParameter (Name, Type, Direction, Size, Value)

     All arguments are optional. Name is a string representing the name of the Parameter object. Type is a long value specifying the data type of the Parameter object. Direction is a long value that indicates whether the parameter represents an input parameter, an output parameter, both, or if the parameter is the return value from a stored procedure. Size is a long value specifying the maximum length for the parameter value in characters or bytes. Value is a variant specifying the value for the Parameter object.
It may take some practice before you are fully able to utilize the power of the Command Object. You can start with a few basic queries and eventually store most of your database actions within the database itself. Let us now go on to see the last significant object of ADO-the Recordset Object.