how-to #9: connect to a database using Active Server Pages

If you do some reading about using databases with ASP, you soon find that there appear to be numerous different ways of doing so. Do you use a DSN or a database driver or a data provider – and what’s the difference? What is ODBC as opposed to OLE-DB? And why are there so many ways of opening a recordset? I found this all very confusing, and I felt that others might think so too – hence this page.

1. basic principles

If you are intending to read data from a database, or add data to it, you have to do two things:

There are three possible ways of opening a connection.

2. method 1: use a Data Source Name

A DSN is a block of data which is stored on the server and which contains as a minimum three pieces of information: the name of the DSN, the location of the database, and the software driver to use to communicate with the database. This is the easiest way to connect to a database. For these examples, assume we have an Access database called ‘club.mdb’ with a single table called ‘members’. We set up a DSN called ‘ClubMembers’ and once it is set up, the ASP code (in VB Script) to open a connection would look like this:

<%
Dim conConn
‘ create an ADO Connection Object
Set conConn = Server.CreateObject(“ADODB.Connection”)
‘ now open the connection
conConn.Open “DSN=ClubMembers”
%>

We now have an open connection, which we can use to open a recordset if we want to – more about this later.

3. method 2: use an ODBC driver

The big problem with DSNs is that they have to be set up on the server. That’s no problem if you can access the server to do it, but if your site is on a server hosted by an internet service provider, you will almost certainly have to rely on the ISP to set it up for you. Not all of them will do that, or they may restrict the number of DSNs you can have. If that happens, you need a DSN-less connection.

You can do this by using an ODBC driver directly. ODBC is Open Database Connectivity – a platform-independent standard to allow different machines using different operating systems to use the same database. You need to specify the ODBC driver, and that means it must be installed on the server; if it is a common one, like Access or SQL Server, it almost certainly will be, but drivers for less commonly used database engines may not. You also – and this is the big catch – need to know the actual physical path to the database. Assuming our path is ‘d:\documents\databases\club.mdb’ then the code would look like this:

<%
Dim conConn
‘ create an ADO Connection Object
Set conConn = Server.CreateObject(“ADODB.Connection”)
‘ now open the connection
conConn.Open “DRIVER=Microsoft Access Driver;DBQ=d:\documents\databases\club.mdb”
%>

Which does the same as the DSN method.

Important: there is a way round not knowing the physical database path. This is given in the third page dealing with this issue.

4. method 3: use an OLE-DB Data Provider

Data Providers are a better way of connecting to databases since they are designed to be more robust and faster. If there is a provider for the database engine you are connecting to, you should use it in preference to the ODBC driver method. Unfortunately there are fewer OLE-DB data providers than there are ODBC drivers, so you may not be able to use a provider if the database engine is an uncommon one. Note that a data provider is not a driver – the two things are quite different. Like the ODBC driver method, you have to specify the provider and the physical database path, but otherwise the code is similar:

<%
Dim conConn
‘ create an ADO Connection Object
Set conConn = Server.CreateObject(“ADODB.Connection”)
‘ now open the connection
conConn.Open “Data Provider={Microsoft.Jet.OLEDB.4.0 (*.mdb)};Data Source=d:\documents\databases\club.mdb”
%>

Notice the syntax with curly braces and the file extension in brackets. It’s crucial to get this absolutely correct. You can find reference sources for the syntax in many places on the net.

5. some notes on the above

i) It is possible to append additional parameters such as a username and password to the string in each case.

ii) The Open method of the connection object shown above is not the only way of doing this. You can also do:


conConn.ConnectionString=(any of the strings shown above)
conConn.Open

It all depends on what you find most convenient.

iii) Once you have finished with the connection, you should close it and delete it:


conConn.Close
Set conConn = Nothing

Once you have your connection object, you can open a recordset, details of which are given on the next page.