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. |
» 
IE 7
Firefox 2.0
Comment and Contribute
