|
Before a database can be accessed from a web page, a database
connection has to be established.
Create a DSN-less Database Connection
The easiest way to connect to a database is to use a DSN-less
connection. A DSN-less connection can be used against any
Microsoft Access database on your web site.
If you have a database called "northwind.mdb" located in a web
directory like "c:/webdata/", you can connect to the database
with the following ASP code:
<% set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0" conn.Open "c:/webdata/northwind.mdb"
%>
Note that from the example above, that you have to specify the
Microsoft Access database driver (Provider), and the physical
path to the database on your computer.
Create an ODBC Database Connection
If you have an ODBC database called "northwind" you can
connect to the database with the following ASP code:
<% set conn=Server.CreateObject("ADODB.Connection") conn.Open
"northwind" %>
With an ODBC connection, you can connect to any database, on
any computer in your network, as long as an ODBC connection is
available.
An ODBC Connection to a MS Access Database
Here is how to create a connection to a MS Access Database:
Open the ODBC icon in your Control Panel.
Choose the System DSN tab.
Click on Add in the System DSN tab.
Select the Microsoft Access Driver. Click Finish.
In the next screen, click Select to locate the
database.
Give the database a Data Source Name (DSN).
Click OK.
Note that this configuration has to be done on computer where
your web site is located. If you are running Personal Web
Server (PWS) or Internet Information Server (IIS) on your own
computer, the instructions above will work, but if your web
site is located on a remote server, you have to have physical
access to that server, or ask your web host to do this for
you.
The ADO Connection Object
The ADO Connection Object is used to establish a database
connection.
Syntax
conn.method conn.property
The most common Methods and Properties
Methods
Method Description
Close Closes a connection
Execute Executes a query, statement, procedure or provider
specific text
Open Opens a connection
Properties
Property Description
Mode Sets or returns the provider access permission
Provider Sets or returns the provider name
State Returns a value describing if the connection is open or
closed
Version Returns the ADO version number
ADO Connection Object
Connection Object
The Connection Object is used to create a connection to a data
source.
Syntax
objectname.method
objectname.property
Methods
Method Description
BeginTrans
Begins a new transaction
Cancel
Cancels an execution
Close
Closes a connection
CommitTrans
Saves any changes and ends the current transaction
Execute
Executes a query, statement, procedure or provider specific
text
Open
Opens a connection
OpenSchema
Returns schema information from the provider about the data
source
RollbackTrans
Cancels any changes in the current transaction and ends the
transaction
Properties
Property Description
Attributes
Sets or returns the attributes of a Connection object
CommandTimeout
Sets or returns the number of seconds to wait while attempting
to execute a command
ConnectionString
Sets or returns the details used to create a connection to a
data source
ConnectionTimeout
Sets or returns the number of seconds to wait for a connection
to open
CursorLocation
Sets or returns the location of the cursor service
DefaultDatabase
Sets or returns the default database name
IsolationLevel
Sets or returns the isolation level
Mode
Sets or returns the provider access permission
Provider
Sets or returns the provider name
State
Returns a value describing if the connection is open or closed
Version
Returns the ADO version number
Events
Event Description
BeginTransComplete
Triggered after the BeginTrans operation
CommitTransComplete
Triggered after the CommitTrans operation
ConnectComplete
Triggered after a connection starts
Disconnect
Triggered after a connection ends
ExecuteComplete
Triggered after a command has finished executing
InfoMessage
Triggered if a warning occurs during a ConnectionEvent
operation
RollbackTransComplete
Triggered after the RollbackTrans operation
WillConnect
Triggered before a connection starts
WillExecute
Triggered before a command is executed
Collections
Collection Description
Errors Contains all the Error objects of the Connection object
Properties Contains all the Property objects of the Connection
object
Methods
The BeginTrans, CommitTrans, and RollbackTrans Methods
The BeginTrans method begins a new transaction. When
calling this method within an open transaction starts a new,
nested transaction. This method can also be called as a
function which return the nested level of the transaction.
The CommitTrans method saves any changes and ends the
current transaction.
The RollbackTrans method cancels any changes in the
current transaction and ends the transaction.
These methods can be used with the Connection object to save
or cancel changes made to the data source.
Note: Not all providers support transactions.
Note: These methods are not available on a client-side
Connection object.
Syntax
level = BeginTrans()
connobj.BeginTrans
connobj.CommitTrans
connobj.RollbackTrans
The Cancel Method
The Cancel method cancels the execution of a method call.
Syntax
object.Cancel
The Cancel method terminates different tasks for each object.
The table below shows what task is terminated when the Cancel
method is called:
Object Terminated method
Command Execute
Connection Execute or Open
Record CopyRecord, DeleteRecord, MoveRecord, or Open
Recordset Open
Stream Open
The Close Method
The Close method is used to close a Connection, a Record, a
Recordset, or a Stream object to free system resources.
When an object is closed, it will not be removed from the
memory. It is possible to change the property settings and
open it again later.
Syntax
object.Close
The Execute Method
The Execute method executes a query, SQL statement, procedure,
or provider-specific text in text.
Syntax
Set rs = connection.Execute(text, recaffected,
options)
Part Description
text The SQL statement, table name, procedure, url, or
provider-specific text to
execute
recaffected Optional. The number of records affected
options Optional. Sets how the provider should evaluate
the text argument. Can be one or more
CommandTypeEnum
or ExecuteOptionEnum
values
Example
The Open Method
The Open method opens a connection to a data source.
Syntax
connection.Open
connectionstring, userid, psword, options
Part Description
connectionstring Optional. Information about the
connection. See
ConnectionString
properties for details
userid Optional. A user name for the connection
psword Optional. A password for the connection
options Optional. A
value
that sets whether this method should return after or
before the connection is established
Example
A DSN-less connection:
<% set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb" %>
An ODBC Database Connection:
<% set conn=Server.CreateObject("ADODB.Connection")
conn.Open "northwind" %>
The OpenSchema Method
The OpenSchema method returns a Recordset object with schema
information from the provider about the data source.
Syntax
Set rs = connection.OpenSchema(type,
criteria, schemaid)
Part Description
type The
type
of schema query to run
criteria Optional. An array of query constraints for each
type option
schemaid Required if type is set to
adSchemaProviderSpecific
Properties
The Attributes Property
The Attributes property sets or returns the attributes of an
object.
Note: When setting multiple attributes, it is possible to
sum the values.
Syntax
object.Attributes
Object Description of the Attributes Property
Connection The Attributes property is read/write. The value
can be one or more
XactAttributeEnum
values. Default is 0
Parameter The Attributes property is read/write. The value can
be one or more
ParameterAttributesEnum
values. Default is adParamSigned
Field The Attributes property is normally read-only. The value
can be one or more
FieldAttributeEnum
values
Property The Attributes property is read-only. The value can
be one or more
PropertyAttributesEnum
values
The CommandTimeout Property
The CommandTimeout property sets or returns the number of
seconds to wait while attempting to execute a command, before
canceling the attempt and generate an error. Default is 30.
Syntax
object.CommandTimeout
The ConnectionString Property
The ConnectionString property sets or returns the details used
to create a connection to a data source.
Note: You can not use both the Provider and File Name
arguments.
Syntax
connobj.ConnectionString = arg1=value; arg2=value; etc;
The ConnectionString property has these arguments:
Argument Description
Provider The provider to use for the connection
File Name A provider-specific file that contains connection
information
Remote Provider The provider to use when opening a client-side
connection
Remote Server A path name of the server to use when opening a
client-side connection
url An absolute URL identifying a resource, such as a file or
directory
The ConnectionTimeout Property
The ConnectionTimeout property sets or returns the number of
seconds to wait for a connection to open, before canceling the
attempt and generate an error. Default is 15 seconds.
Syntax
connobj.ConnectionTimeout
The CursorLocation Property
The CursorLocation property sets or returns the location of
the cursor service. Can take a
CursorLocationEnum
value.
A cursor is used to:
· control record navigation
· control the visibility of changes in the database
· control the updatability of data
Note: A Recordset inherits this setting from the
associated Connection.
Note: This property is read-only on an open Recordset, and
read/write on a Connection or on a closed Recordset.
Syntax
connobj.CurserLocation rsobj.CurserLocation
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM Customers"
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic
rs.Open sql, conn
%>
The DefaultDatabase Property
The DefaultDatabase property sets or returns the default
database name for a specific Connection object.
Syntax
connobj.DefaultDatabase
The IsolationLevel Property
The IsolationLevel property sets or returns the isolation
level of a Connection object.
The value can be an
IsolationLevelEnum
value. Default is adXactChaos.
Note: The IsolationLevel settings will not work until next
time BeginTrans is called.
Syntax
connobj.IsolationLevel
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.IsolationLevel = adXactIsolated
conn.Open "northwind"
%>
The Mode Property
The Mode property sets or returns the permissions for
modifying data in a Connection, Record, or Stream object.
Can take one of the
ConnectModeEnum
values.
· Connection object - Default is adModeUnknown
· Record object - Default is adModeRead
· Stream object - Default is adReadOnly or adModeUnknown
Note: This property can be set only when the Connection
object is closed.
Syntax
object.Mode
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Mode = adModeReadWrite
conn.Open "northwind"
%>
The Provider Property
The Provider property sets or returns the provider name of a
Connection object. Default is MSDASQL (Microsoft OLE DB
provider for ODBC).
This property can also be set by the ConnectionString property
or the ConnectionString argument of the Open method.
The Provider property is read/write when the connection is
closed. The setting takes effect when you open the Connection
object or access the Properties collection of the Connection
object.
Syntax
connobj.Provider
Example
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
%>
The State Property
The State property returns a value that describes if the
object is open, closed, connecting, executing or retrieving
data. The value returns an
ObjectStateEnum
value. Default is adStateClosed.
The State property can have a combination of values. If a
statement is executing, this property will have a combined
value of adStateOpen and adStateExecuting.
Syntax
object.State
The Version Property
The Version property returns the ADO version number.
Syntax
version = connobj.Version
Events
The BeginTransComplete Event
The BeginTransComplete event is triggered after the BeginTrans
operation.
Syntax
BeginTransComplete translevel, objerror, status, conn
Part Description
translevel The new transaction level of the BeginTrans
that caused this event
objerror An Error object that describes the error that
occurred if status is set to
adStatusErrorsOccurred
status An
EventStatusEnum
value. This parameter is set to adStatusOK if the
operation that caused the event was successful, or to
adStatusErrorsOccurred if
it failed. To avoid subsequent notifications set this
parameter to
adStatusUnwantedEvent before the event returns
conn The Connection object that triggered this event
The CommitTransComplete Event
The CommitTransComplete event is triggered after the
CommitTrans operation.
Syntax
CommitTransComplete objerror, status, conn
Part Description
objerror An Error object that describes the error that
occurred if the value of status is
adStatusErrorsOccurred
status An
EventStatusEnum
value. This parameter is set to adStatusOK if the
operation that caused the event was successful, or to
adStatusErrorsOccurred if
it failed. To avoid subsequent notifications set this
parameter to
adStatusUnwantedEvent before the event returns
conn The Connection object that triggered this event
The ConnectComplete Event
The ConnectComplete event is triggered after a connection
starts.
Syntax
ConnectComplete objerror, status, conn
Part Description
objerror An Error object that describes the error that
occurred if the value of status is
adStatusErrorsOccurred
status An
EventStatusEnum
value. Returns always adStatusOK
conn The Connection object that triggered this event
The Disconnect Event
The Disconnect event is triggered after a connection ends.
Syntax
Disconnect status, conn
Part Description
status An
EventStatusEnum
value. Returns always adStatusOK
conn The Connection object that triggered this event
The ExecuteComplete Event
The ExecuteComplete event is triggered after a command has
finished executing.
Syntax
ExecuteComplete recaffected, objerror, status, comm,
rs, conn
Part Description
recaffected The number of records affected by the command
objerror An Error object that describes the error if
status is set to
adStatusErrorsOccurred
status An
EventStatusEnum
value. This parameter is set to adStatusOK if the
operation that caused the event was successful, or to
adStatusErrorsOccurred if
it failed. To avoid notifications set this parameter to
adStatusUnwantedEvent
before the event returns
comm The Command object that was executed
rs A Recordset object that is the result of the executed
command
conn A Connection object. The connection over which the
operation was executed
The InfoMessage Event
The InfoMessage event is triggered if a warning occurs in a
ConnectionEvent operation.
Syntax
InfoMessage objerror, status, conn
Part Description
objerror An Error object that contains any errors that are
returned
status An
EventStatusEnum
value. If a warning occurs, status is set to adStatusOK
and the objerror contains the warning. To avoid
notifications set this parameter
to adStatusUnwantedEvent before the event returns
conn A Connection object. The connection for which the
warning occured
The RollbackTransComplete Event
The RollbackTransComplete event is triggered after the
RollbackTrans operation.
Syntax
RollbackTransComplete objerror, status, conn
Part Description
objerror An Error object that describes the error if
status is set to
adStatusErrorsOccurred
status An
EventStatusEnum
value. This parameter is set to adStatusOK if the
operation that caused the event was successful, or to
adStatusErrorsOccurred if
it failed. To avoid subsequent notifications set this
parameter to
adStatusUnwantedEvent before the event returns
conn The Connection object that triggered this event
The WillConnect Event
The WillConnect event is triggered before a connection starts.
Syntax
WillConnect ConnectionString, userid, psword, opt,
stat, con
Part Description
ConnectionString Holds the connection information for the
connection
userid Holds a user name for the connection
psword Holds a password for the connection
opt Specifies how the provider should evaluate the
ConnectionString. Your
only option is adAsyncOpen
stat An
EventStatusEnum
value. Default is adStatusOK. It is set to
adStatusCantDeny if the event can not cancel the operation. To
avoid
notifications set this parameter to adStatusUnwantedEvent
before the
event returns. Set this parameter to adStatusCancel to request
the
connection operation that caused cancellation
con The Connection object for which this event
notification applies
The WillExecute Event
The WillExecute event is triggered before a command is
executed.
Syntax
WillExecute src, cursortyp, locktyp, opt, stat, com, rs,
con
Part Description
src Holds a SQL command or a procedure name
cursortyp The
cursor type
for the Recordset to open
locktyp The
lock type
for the Recordset to open
opt A value that indicates options that can be used to
execute the command or open
the Recordset
stat An
EventStatusEnum
value. It may be adStatusCantDeny or adStatusOK when
this event is called
com The Command object for which this event notification
applies
rs The Recordset object for which this event notification
applies
con The Connection object for which this event
notification applies
|