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:


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.


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.