Connection Object

One key function of the connection object of ActiveX Data Object component is to manipulate a connection session with a data source. The object created by the Server.CreateObject method is only a blank placeholder used for representing a unique session with a data source. In order to use the connection object, the open method of the connection object can be used to open a unique connection to a particular data source specified in the parameters of the method. Besides, the connection object can also be used to execute simple command with limitation.

ConnectionObject.Open Method

ConnectionObect.Open method for connection object is a method used to open a physical connection to a specified data source through the specified object instance. The connection information of the connection object. method can be set either through the inline parameter "ConnectionString" or through the specified ConnectionObject.ConnectionString property.

Syntax:

ConnectionObjectName.Open ConnectionString, UserID, Password, Options;

Or in VBScript. Imply

ConnectionObjectName.Open ConnectionString, UserID, Password, Options

Or in JScript. Imply

ConnectionObjectName.Open ConnectionString, UserID, Password, Options;

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

ConnectionString

The optional parameter "ConnectionString" is used to specify the string value of the connection information. The parameter "ConnectionString" is also defined as the property of a connection object which can be used to set and return the value of ConnectionString. The optional parameter "ConnectionString" is a series of argument = value statements separated by semicolons. Although ADO only supports five types of arguments, any other arguments will also be passed to the provider directly by the ADO without carrying out any process. The five valid arguments of parameter "ConnectionString" or the "ConnectionString Property" can be

Argument	Description
Provider=	To specify the name of a provider to use for the connection
File Name=	To specify the name of a provider-specific file, e.g. a persisted data source object, containing preset connection information.
Remote Provider=	To specify the name of a provider to use for the connection when opening a client-side connection for Remote Data Service only.
Remote Server=	To specify the path name of the server to use for the connection when opening a client-side connection for Remote Data Service only.
URL=	To specify an absolute URL as the connection string to identify a resource, such as a file or directory, for the connection.e

UserID

The optional parameter "UserID" is a string value used to specify the user name used when establishing the specified connection.

Password

The optional parameter "Password" is a string value used to specify the password used when establishing the specified connection.

Options

The optional parameter "Options" is a "ConnectOptionEnum" value used to specify whether the method should return synchronously after the connection is established or asynchronously before the connection is established. The value of parameter "Options" or the "ConnectOptionEnum" value can be

Constant	Value	Description
adAsyncConnect	16	Opens the connection asynchronously. The ConnectComplete event may be used to determine when the connection is available.
adConnectUnspecified	-1	Default. Opens the connection synchronously.

Remarks:

The ConnectionObect.Open Method is used to establish the physical connection to a data source manually. A conncection object is in live only when the connection object is connected to a data source. Commands can then be issued against the specified data source after the ConnectionObect.Open Method completes successfully.

The ConnectionObect.Open Method automatically inherits the value of the ConnectionObect.ConnectionString Property if the value of the ConnectionString parameter is not set to override the current connection properties. And therefore the ConnectionString parameter is only an optional parameter which can be set by the ConnectionObect.ConnectionString Property before calling the connectionObect.Open Method. However, the ConnectionObect.ConnectionString Property also automatically inherits the ConnectionString parameter of the ConnectionObect.Open Method if the value of the ConnectionString parameter is set.

Besides, the contents of ConnectionObect.ConnectionString Property may also be altered by the provider after the connection is completed successfully. e.g. the ADO-defined argument names are mapped equivalent to corresponding equivalents for the specific provider.

Since the File Name argument will cause ADO to load the associated provider, both the Provider and File Name arguments cannot be set at the same time in the ConnectionString.

Only the last instance of any argument is used by ADO, other duplicates of an argument in the ConnectionString property are ignored.

Similarly, both the user and password information in the ConnectionString property or parameter can also be override by the optional user and password parameters accordingly.

The ConnectionObect.Open Method will occupy some system resources to maintain the connection object in live, the ConnectionObect.Close Method can be used to free any associated system resources to keep the connection object in live. However the ConnectionObect.Close Method does not remove the specified connection object from the memory, and the specified connection object can be reused by the ConnectionObect.Open Method with the same or altered property setting. The only way to eliminate the specified connection object from memory is to set the specified connection object variable to Nothing.

The Remote Provider and Remote Server arguments used in the ConnectionString can only be used on a client-side Connection object for Remote Data Service Usage.

For remote data service usage, when the connection is a client-side Connection object, the ConnectionObect.Open Method does not actually establish a physical connection to the data source, only the property setting of the specified connection are set accordingly and a physical connection can then be established automatically when a Recordset object is opened on the specified connection object.

When URL is used in the ConnectionString argument, URLs using the http scheme will automatically invoke the Microsoft OLE DB Provider for Internet Publishing.

Although there is a default ADO provider, MSDASQL, for Windows 2000/32bit, Windows XP/32bit, Windows 2003 Server/32bit, Windows Vista/32bit, Windows Vista Service Pack 1 or later/32bit and 64bit and Windows versions after Windows Vista/32bit and 64bit, the provider name is better specified in the connection string for readability and reliability.

Examples:

Example of using the Open method to open a physical connection to a specified data source through the specified connection object instance.

ASP VBScript command:

<script runat="server" language="VBScript">
Dim connObjName, connStr
Set connObjName = CreateObject("ADODB.Connection")
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
connObjName.Open connStr
</script>

HTML web page outputt>HTML web page output
Example of using the Open method to open a physical connection to a specified data source through the specified connection object instance.

ASP JScript command:

<script runat="server" language="JScript">
var connObjName, connStr;
connObjName = Server.CreateObject("ADODB.Connection");
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
connObjName.Open(connStr);
</script>

HTML web page output
Examples of typical Database Connection Strings.

ASP command for Microsoft Access without DSN:

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\temp1\dbfile.mdb"
%>

ASP command for Microsoft Access OLE DB :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connconnObjName.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
%>

ASP command for Microsoft Access with File DSN :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "FILEDSN=ADSN"
%>

ASP command for Microsoft Access with DSN and no User ID/Password :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "DSNname"
%>

ASP command for Microsoft Access with DSN and User ID/Password :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "DSNname", "username","password"
%>

ASP command for Microsoft Access without DSN and using a physical path as a reference :

<%
Set Set connObjName = Server.CreateObject("ADODB.Connection")
connStr="DRIVER={MICROSOFT Access Driver (*.mdb)};"
connStr=ConnStr & "DBQ=d:\temp1\dbfile.mdb"
connObjName.Open connStr
%>

ASP command for Microsoft Access without DSN and using a Server.MapPath as a reference :

<% /> SetSet connObjName = Server.CreateObject("ADODB.Connection")
connStr="DRIVER={MICROSOFT Access Driver (*.mdb)};"
connStr=ConnStr & "DBQ=" & Server.MapPath("/temp1/dbfile.mdb")
connObjName.Open connStr
%>

ASP command for Microsoft SQL Server OLE DB:

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=sqldbfile"
%>

ASP command for Microsoft SQL Server with DSN :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "DSN=sqldbdsn;UID=username;PWD=password;DATABASE=sqldbfile"
%>

ASP command for Microsoft SQL Server without DSN :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.Open "DRIVER={SQL Server};SERVER=ServerName;UID=username;PWD=password;DATABASE=sqldbfile"
%>

ASP command for Microsoft Visual FoxPro :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connStr = "Driver=Microsoft Visual Foxpro Driver;UID=userid;SourceType=DBC;SourceDB=d:\temp1\dbvfoxprofile.dbc"
connObjName.Open connStr
%>

ASP command for Oracle by ODBC with DSN :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.cursorlocation=3
connObjName.Open "DSN=test;UID=name;PWD=pass"
%>

ASP command for Oracle OLE DB :

<%
Set connObjName = Server.CreateObject("ADODB.Connection")
connObjName.cursorlocation=3
connStr="PROVIDER=MSDAORA.1;Password=pass;User ID=name;DATA SOURCE=data.world"
connObjName.Open connStr
%>

ConnectionObject.Close Method

ConnectionObect.Close method for connection object is a method used to disconnect or close the opened physical connection to a specified data source related to the specified object instance. Those active objects related to or associated with the connection will also be affected accordingly. The ConnectionObect.Close method can only be used to free any associated system resources, the Connection object itself is still saved in the memory and can be reopened with the same property setting or reused again by changing its property settings. The Connection object can be eliminated from memory by setting the object variable to Nothing after closing the related connection object.

Syntax:

ConnectionObjectName.Close

Or in VBScript. Imply

ConnectionObjectName.Close

Or in JScript. Imply

ConnectionObjectName.Close;

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

Remarks:

The ConnectionObect.Close Method is used to close the physical connection to a data source manually, therefore any active Recordset objects associated with the specified connection will also be closed.

But the Command object associated with the specified Connection object will persist, only the ActiveConnection property of the Command object will be set to Nothing as the connection is closed. And any provider-defined parameters of the Parameters collection of the associated Command object will be cleared also because these parameters become outdated.

Since a closed Connection Object is just a place holder only, the Connection object should be opened again before the specified connection is in live. Therefore calling methods that require a connection object in live, an open connection to a data source, will generate an error when the referred connection object is closed.

When closing a Connection object, the open Recordset objects on the connection rolls back any pending changes in all of the Recordset object.

When a transaction is in progress, closing a Connection object related to the transaction by the Close method explicitly will generate an error. If a Connection object falls out of scope while a transaction is in progress, ADO automatically rolls back the transaction.

Examples:

Example of using the Open method to open a physical connection to a specified data source through the specified connection object instance.

ASP VBScript command:

<script runat="server" language="VBScript">
Dim connObSet connObjName = CreateObject("ADODB.Connection")
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
connObjName.Open connStr
connObjName.Close
</script>

HTML web page outputt>HTML web page output
Example of using the Open method to open a physical connection to a specified data source through the specified connection object instance.

ASP JScript command:

<script runat="server" language="JScript">
var connObjName, connStr;
connObjName = Server.CreateObject("ADODB.Connection");
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
connObjName.Open(connStr);
connObjName.Close;
</script>

HTML web page output

ConnectionObject.Execute Method

ConnectionObect.Execute method for connection object is a method used to execute the specified query, SQL statement, stored procedure, or provider-specific text referred to the specified connection object.

Syntax:

Set RecordsetObjectName = ConnectionObjectName.Execute (CommandText, RecordsAffected, Options)

Or in VBScript. Imply

Set RecordsetObjectName = ConnectionObjectName.Execute (CommandText, RecordsAffected, Options)

Or in JScript. Imply

Set RecordsetObjectName = ConnectionObjectName.Execute (CommandText, RecordsAffected, Options);

Parameters:

RecordsetObjectName

The parameter "RecordsetObjectName" is used to specify the name of the instance of the Recordset Object returned.

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

CommandText

The parameter "CommandText" is a string value used to specify SQL statement, stored procedure, or provider-specific text to be executed referred to the specified connection object. However, table names can only be used if the provider is SQL aware and ADO will automatically prepend the standard SQL Select syntax to form and pass "SELECT * FROM Customers" as a Transacti-SQL statement to the provider.

RecordsAffected

The optional parameter "RecordsAffected" is a long variable used to specify the number of records which returned by the provider the the operation affected related to the specified Connection Object.

Options

The optional parameter "Options" is a long variable used to specify how the provider should evaluate the CommandText argument related to the specified Connection Object. The value of parameter "Options" can be a bitmask of one or more CommandTypeEnum or ExecuteOptionEnum values.

The possible value of CommandTypeEnum are

Constant	Value	Description
adCmdUnspecified	-1	Does not specify the command type argument.
adCmdText	1	Evaluates CommandText as a textual definition of a command or stored procedure call..
adCmdTable	2	Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc	4	Evaluates CommandText as a stored procedure name.
adCmdUnknown	8	Default. Indicates that the type of command in the CommandText property is not known. When the type of command is not known, ADO will make several attempts to interpret the CommandText. CommandText is interpreted as a textual definition of a command or stored procedure call. This is the same behavior as adCmdText. CommandText is the name of a stored procedure. This is the same behavior as adCmdStoredProc. CommandText is interpreted as the name of a table. All columns are returned by an internally generated SQL query. This is the same behavior as adCmdTable.
adCmdFile	256	Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect	512	Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect. This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.

Do not use the CommandTypeEnum values of adCmdFile or adCmdTableDirect with Execute. These values can only be used as options with the Open Method (ADO Recordset) and Requery Method methods of a Recordset.

The possible value of ExecuteOptionEnum are

Constant	Value	Description
adAsyncExecute	0x10	Indicates that the command should execute asynchronously. This value cannot be combined with the CommandTypeEnum value adCmdTableDirect.
adAsyncFetch	0x20	Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be retrieved asynchronously.
adAsyncFetchNonBlocking	0x40	Indicates that the main thread never blocks while retrieving. If the requested row has not been retrieved, the current row automatically moves to the end of the file. If you open a Recordset from a Stream containing a persistently stored Recordset, adAsyncFetchNonBlocking will not have an effect; the operation will be synchronous and blocking. adAsynchFetchNonBlocking has no effect when the adCmdTableDirect option is used to open the Recordset.
adExecuteNoRecords	0x80	Indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data). If any rows are retrieved, they are discarded and not returned. adExecuteNoRecords can only be passed as an optional parameter to the Command or Connection Execute method.
adExecuteStream	0x400	Indicates that the results of a command execution should be returned as a stream. adExecuteStream can only be passed as an optional parameter to the Command Execute method.
adExecuteRecord	0x800	Indicates that the CommandText is a command or stored procedure that returns a single row which should be returned as a Record object.
adOptionUnspecified	-1	Indicates that the command is unspecified.

Note Use the ExecuteOptionEnum value adExecuteNoRecords to improve performance by minimizing internal processing and for applications that you are porting from Visual Basic 6.0.

Do not use adExecuteStream with the Execute method of a Connection object.

Return:

Recordset object

The ConnectionObect.Execute method returns a Recordset object of the specified data source through the specified connection object.

Remarks:

The ConnectionObect.Execute method can execute whatever query specified in the CommandText argument related to the specified connection. If the CommandText argument specifies a row-returning query, any results that the execution generates are stored in a new Recordset object. If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.

The returned Recordset object is always a read-only, forward-only cursor. A Recordset object with more functionality can only be obtained by creating a Recordset object with the desired property settings, and use the Recordset object's Open Method method to execute the query and return the desired cursor type.

The contents of the CommandText argument are specific to the provider and can be standard SQL syntax or any special command format that the provider supports.

An ExecuteComplete event will be issued when this operation concludes.

URLs using the http scheme will automatically invoke the Microsoft OLE DB Provider for Internet Publishing.

Examples:

Example of using the Execute method to to execute the specified table through the specified open connection object instance.

ASP VBScript command:

<script runat="server" language="VBScript">
Dim connObjName, connStr, RecordSetObjectName
Set connObjName = CreateObject("ADODB.Connection")
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
connObjName.Open connStr
Set RecordSetObjectName=connObjName.execute("table1")
response.write RecordSetObjectName(0)
connObjName.Close
</script>

HTML web page outputt>HTML web page output

test
Example of using the Execute method to to execute the specified table through the specified open connection object instance.

ASP JScript command:

<script runat="server" language="JScript">
var connObjName, connStr;
connObjName = Server.CreateObject("ADODB.Connection");
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
connObjName.Open(connStr);
RecordSetObjectName=connObjName.execute("table1");
Response.Write( RecordSetObjectName(0));
connObjName.Close;
</script>

HTML web page output

test

ConnectionObject.Cancel Method

ConnectionObect.Cancel method for connection object is a method used to cancel the execution of a pending asynchronous method call invoked with the adAsyncConnect option by the related connection object.

Syntax:

ConnectionObjectName.Cancel

Or in VBScript. Imply

ConnectionObjectName.Cancel

Or in JScript. Imply

ConnectionObjectName.Cancel;

Parameters:

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

Remarks:

The ConnectionObect.Cancel Method is used to terminate execution of an asynchronous method call invoked with the adAsyncConnect, adAsyncExecute, or adAsyncFetch option. That is the last asynchronous call using the Execute or Open method on the Connection object is terminated

Examples:

Example of using the Open method to cancel the execution of a pending asynchronous method call invoked with the adAsyncConnect option by the related connection object..

ASP VBScript command:

<script runat="server" language="VBScript">
Dim connObSet connObjName = CreateObject("ADODB.Connection")
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
connObjName.Open connStr,,,16
connObjName.Cancel
Response.Write (connObjName.State)
</script>

HTML web page outputt>HTML web page output

0
Example of using the Open method to cancel the execution of a pending asynchronous method call invoked with the adAsyncConnect option by the related connection object.

ASP JScript command:

<script runat="server" language="JScript">
var connObjName, connStr;
connObjName = Server.CreateObject("ADODB.Connection");
connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
connObjName.Open(connStr,"","",16);
connObjName.Cancel;
Response.Write (connObjName.State);
</script>

HTML web page output

0