Opening a database
To be able to communicate with a database, we need a database connection.
ADO contains the Connection object to connect to a database.
After you create a Connection object,
you open a database with the Open method. In order to
open the right database, you need to supply this method with a so called ConnectionString.
Creating a ConnectionString
The ConnectionString specifies the database type, the location of the database
and the database name. If needed, you can also specify a usersname and password.
You can also provide a usersname and password separately with the Open
method. When you do this, the values in the ConnectionString are ignored.
A ConnectionString typically looks like this:
SQL Server
Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=MyDB;User Id=mr;Password=uhm;
MS Access
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\databases\MyDB.mdb;
For more information read the article
The fastest database connection, or check the
ADO Connection String Samples
for a list with ConnectionStrings for different databases.
Sample
The sample below opens a database and checks if the operation succeeded.
The ConnectionString has been saved in the Application object,
so it can be reused between different pages. Otherwise you would have to rewrite all the pages
when you switch to another database.
openconn.asp
<%
Option Explicit
Dim objConn
Dim strResult
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("ConnString")
If objConn.State = adStateClosed Then
strResult = "The database hasn't opened! There is something wrong."
Else
strResult = "The database has opened, let's close it again"
objConn.Close
End If
Set objConn = Nothing
%>
<HTML>
<BODY>
<%
Response.Write strResult
%>
</BODY>
</HTML>
The sample above uses the adStateClosed constant. This is a constant
that's important to ADO. We can't simply use this constant, however. It has to be
defined first. There are three ways to do this:
-
Define yourself with
Const adStateClosed = 0
-
Include the file adovbs.inc into your ASP page.
You can copy this file from C:\Program Files\Common Files\System\ado to your website.
You can then include it with the following line of code:
<!--#include virtual="/adovbs.inc"-->
-
By referencing the ADO Type Library in global.asa. This loads the constants into
the entire application, so you don't need to use either of the previous methods anywhere.
You can reference the Type Library by adding the following code to global.asa:
<!-- METADATA TYPE="TypeLib" File="C:\Program Files\Common Files\System\ado\msado15.dll" -->
|
