ASP Database Connection

Making an ASP database connection

This is just a note  to save me from having to look it all up again every time.

Connecting to databases with ASP is, for me, a bit of a pain. The problem comes from their flexibility. there seems to be an endless variety of ways to connect to your data. Mostly we are talking about either a System DSN or a DSN-less connection – there are others.

A DSN is a Data Source name. You get to define these on the machine where your data aware program is running. They provide a convenient shortcut to the data where a relatively simple name hides the mucky details of the connection. On the downside, if you move your application to another machine, it will have to have the appropriate DSN defined there as well. Not necessarily a problem but something to be aware of.

A DSN connection may look like this:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.ConnectionString = "DSN=pubs"

A DSN-less connection must be told all the essential information needed to connect to the data source. It still needs suitable drivers to be present on the machine. here are some DSN-less connections;

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection") 
objConn.ConnectionString = "DRIVER={MS SQL-Server};"&_   "UID=sa; PWD=; DATABASE=pubs; SERVER=myMachine"

If you want faster access to your data and you have ADO 2.0 or better, you can also connect directly to the underlying OLEDB object(s). In this case, the second example would be written:

Dim objConn
 Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "Provider=SQLOLEDB;" & _ "Data Source=myMachine; Initial Catalog=pubs;" & _
 "User ID=sa; Password= "

There are significant performance gains to be made in this way. This page on4GuysFromRolla has details.

Or, of course, you can go an buy one of the popular ASP books from Amazon:

Comments are closed.