ADO Connections:

 by Tom Muck

*Note: This article has been updated for Dreamweaver MX and is available at www.communitymx.com

UltraDev provides several methods for an ASP application to connect to a database:

• Application server -- using DSN
• Local machine -- using DSN
• Application server -- using custom connection string (OLE DB or DSNLess)
• Local machine -- using custom connection string (OLE DB or DSNLess)
• Application server using Server.Mappath in a connection string (OLE DB or DSNLess)

The Local Machine settings allow you to connect on your local computer through the local machine, whereas the Application Server settings allow you to connect through the application server that is set up in your Site Definition dialog box under the Application Server tab. When you define the site for the first time, the URL Prefix has to be set up properly in order to for the database connections to work properly:

The URL Prefix can be your FTP host, an HTTP address, or a local network connection. It can even be your own local machine with a http://localhost/foldername URL Prefix.

UltraDev provides an interface for getting the connection string into your page. The interface is the Custom Connection String dialog box:

You can access this dialog box two different ways:

Go to the Modify >> Connections menu or

Click the Define button on the Recordset, Command, Insert, Update, or Delete dialog boxes.

The following connection strings are some of the more popular ADO connection strings used in ASP applications. Please note that in the Connection dialogue box, the connection strings will be placed on one line in the Connection String textfield (or the ADO Connection string dialog box if you are using UltraDev 1).

ODBC DSNLess Connections:

MS Access ODBC DSNless connection

Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\dbname.mdb;Uid=Admin;Pwd=pass;

dBase ODBC DSNless connection

Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\somepath\dbname.dbf;

Oracle ODBC DSNless connection

Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=admin;Pwd=pass;

MS SQL Server DSNless connection

Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass;

MS Text Driver DSNless connection

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security Info=False;

Visual Foxpro DSNless connection

Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\somepath\dbname.dbc;Exclusive=No;

MySQL DSNless connection

driver={mysql}; database=yourdatabase;server=yourserver;uid=username;pwd=password;option=16386;

OLE DB Connections:

MS Access OLE DB connection

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\dbname.mdb;User Id=admin;Password=pass;

Oracle OLE DB connection

Provider=OraOLEDB.Oracle;Data Source=dbname;User Id=admin;Password=pass;

MS SQL Server OLE DB connection

Provider=SQLOLEDB;Data Source=machineName;Initial Catalog=dbname;User ID=sa;Password=pass;

MS SQL Server OLE DB connection using an IP address

Provider=SQLOLEDB; Data Source=xx.xx.xx.xx,1433; Network Library=DBMSSOCN; Initial Catalog=dbname;User ID=sa;Password=pass;

MS Text Driver OLE DB connection

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\yourpath;Extended Properties='text;FMT=Delimited'"

If you don't know the drive path to the database after uploading, you can download a server behavior from http://www.basic-ultradev.com/extensions/getdatabasepath.mxp and put it on a blank page and fill in your database name. Upload it to the server and browse the page. The connection string for DSNless connection will be in the browser window and you can cut and paste into UltraDev.

Alternatively, you can simply place some ASP code into your HTML code window:

<%=Server.MapPath("/")%>

This will give you the root folder for your website on the server. Then you can use the information to create a path string for your database to use in the ADO Connection String. It's much better to find out your path ahead of time than to use the Server.Mappath method. Server.Mappath has to create the path to the database each time your page is viewed. It's the same principle as looking up something in the index of a book. If you know the page number, it's quicker to go directly to the page rather than looking it up each time.

Using a Password Protected Access Database

When your Access database has a password on it, you have to change the way you compose your connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somepath\mydatabase.mdb;Jet
OLEDB:Database Password=pass;

You may have to specify the system OLEDB database file in the string as well. This one should work in Windows 2000:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\somepath\mydatabase.mdb;Persist Security Info=False;Jet OLEDB:System database=C:\Program Files\Common Files\System\System.mdw;Jet OLEDB:Database Password=pass;

And this one should work in Windows NT 4:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\somepath\mydatabase.mdb;Persist Security Info=False;Jet OLEDB:System database=C:\winnt\system32\System.mdw;Jet OLEDB:Database Password=pass;

For more information on creating OLE DB connection strings, check out the MS technote at http://support.microsoft.com/support/kb/articles/Q264/6/91.ASP

Using Server.Mappath in UltraDev 4

Beginning with UltraDev 4, the use of Server.Mappath is allowed in connection strings. The requirements are as follows:

• You have to specify "Application Server" when defining the connection. Local connections aren't supported.
• You have to specify the path to the root of the Web server or virtual directory in your connection string.
• Make sure you put quotes around the first part of your connection string.

A typical DSNLess connection to an Access database using Server.Mappath in VBScript looks like this. Please note that this is ONE LINE of text in your Custom Connection string dialog box:

"Driver={Microsoft Access Driver (*.mdb)};Dbq=" & Server.Mappath("\somepath\dbname.mdb") & ";Uid=Admin;Pwd=pass;"

A typical OLEDB connection string for MS Access is as follows:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\somepath\dbname.mdb") & ";User Id=admin;Password=pass;"

Note that if you are using ASP JScript, you should change the & signs to + signs, like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.Mappath("\somepath\dbname.mdb") + ";User Id=admin;Password=pass;"

The format of the string is the same as the strings that were listed previously that used database locations. When using Server.Mappath, the Server.Mappath statement is concatenated to the string, and actually takes the place of the physical location of the database in the string, like this:

instead of:

"string info; database path; user info;"

you are writing

"string info; " + database path + "; user info;"

Notice that the string will look the same to your ASP server after the Server.Mappath function is evaluated and the path to the database is found.

If you are using UltraDev 1, DON'T use the Server.MapPath statement in a connection string in UltraDev -- you won't be able to use the recordset from the Data Bindings palette. You can hand code the connection string, but the recordset won't show up in the Data Bindings inspector and you won't be able to use any of the automated features of UltraDev.

Closing an ADO connection

A general rule of programming in ASP is to destroy any objects as soon as you can. This includes recordsets, command objects, e-mail components, and any other object that you explicitly create. This frees up server resources more quickly than waiting for IIS to destroy the objects.

Closing an ADO connection in UltraDev 1

You may notice that UltraDev 1 does not close recordset connections explicitly, but rather relies on the session timeout to close the database connection. There is some controversy over this, as it is a general rule of programming to destroy any object explicitly. You can add the following lines to the bottom of your ASP file to make sure that the database connection is closed and destroyed properly:

We have an extension available that will write this code automatically for you, at

http://www.basic-ultradev.com/extensions/ASPRecordsetClose.mxp

Closing an ADO connection in UltraDev 4

UltraDev 4 closes all connections at the bottom of the page. If you are doing a re-direct somewhere on the page, you should manually move the recordset close information to a point above the redirect. UltraDev 4 doesn't explicitly destroy the ADO object, but you can hand-code this at the bottom of the page after the recordset is closed (as in the examples above) or use our Server Behavior, which is available at

http://www.basic-ultradev.com/extensions/Destroy Recordset.mxp

Tom Muck