Classic ASP – Example of a Basic Database Connection (SQLOLEDB)

I’m revisiting some classic ASP functionality, and in this case I’m reviewing how to set up a simple database connection to a SQL Server database. There are of course several ways to get this to work, and in this case we will use the SQLOLEDB provider.

Classic ASP is Still Used and Maintained

There are many applications built 10+ years ago, or still being built using the classic ASP framework. In my opinion this was a solid and very usable framework. ASP has been supplanted long ago by the ASP.NET framework which qualifies as a full programming language with a vast library of functionality to call on. However ASP is still used and does provide a simple and efficient way to get the job done.

How to Set up the Global.asa Connection String

As a reminder, the connection string for a database connection was set in a file called the global.asa file. The most commonly used method was to set an application-level variable to contain the necessary connection information that each individual ASP page could access.

Here is an example of creating a SQLOLEDB connection string:

Application("ConnectionString") = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=testuser;Initial Catalog=myDatabase;Data Source=myDBServer;Password=myPassword"

Much of this code should be self-explanatory.

1. The provider is the protocol, which in this case is SQLOLEDB. An example of an alternate provider would be an ODBC connection.
2. The User Id is of course the database user that SQL queries will run under.
3. Password is the password of the user that SQL queries will run under.
4. Data Source is the name of the database server that SQL Server is running on.
5. Initial Catalog is the database that will be queried

These points should be relatively familiar with anyone who routinely queries a database from their application since the connection syntax has not changed much over the years.

Example ASP for Reading and Outputting Data

As to the actual code, I will provide an example below in VBscript, which is the default language supported by ASP.

In the following code we set up our variables that we will later be using as well as constructing the stored procedure call that we will be running:

dim objConn
dim objRS1
dim StoredProc1
StoredProc1 = "set nocount on;Exec apknet.dbo.[testStoredProcedure] @testParameter1='24', @testParameter1='78'"

Next we will set up and open the connection to our database, assigning it to the objConn variable we declared earlier. We will use the Application level ConnectionString variable from the Global.asa when opening our database connection.

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.CommandTimeout = 60
objConn.Open application("ConnectionString")

As you can see, ASP requires us to specify the connection type as ADODB and set the CommandTimeout to the number of seconds that the connection object will wait for a response from the database server before cancelling and closing the connection. The default time is 30 seconds so in the code above we have extended this time slightly to 60 seconds.

Following this we need to create our result set object, set our cursor location, and finally open our connection to the database:

objRS2.CursorLocation = 3
objRS2.CursorType = 3 
objRS2.Open StoredProc1, objConn

The CursorLocation value of 3 means adUseClient, which is code for a client-side cursor from a local cursor library. A value of 1 is obsolete and shouldn’t be used, and a value of 2 is most commonly used and is code for using a server-side cursor.

The CursorType value of 3 means  that a static copy of a set of records is created that you can use for data retrieval or generating reports. The default value is 0, which creates a forward-only cursor. A value of -1 codes for no cursor, and options 1 or 2 are not commonly used.

Now we can write the set-up logic for the data that we will write out to the HTML page:

If objRS1.State = 1 then
 If objRS1.BOF and objRS1.EOF Then
 Response.Write "Recordset is empty"
Else
 <table cellspacing="1" border="1">
 <tr bgcolor="BBBBBB">
 <td align="center"><font color="000000">Header1</font></td>

As you can see we check the state of the RecordSet object and if it is empty, we write out a message to that effect. Alternately if the RecordSet object has data we write out a table with header columns for the data that is to be output.

Now comes the time to do the actual outputting of the information from the database onto the HTML page. We do this using a do loop as follows:

do while not objRS1.eof
 %><tr><%
 for i = 0 to 9
 'process the data
 thatfield = trim(objRS1(i))
%><td width="45"
align="center"><%=thatfield%></td><%
next
objRS2.movenext
loop

Here we are doing a very simple loop and within the loop we create another loop to iterate through the columns returned by the data query and output the columns as table cells to the HTML page.

So really that’s all that there is to it. The important point to always remember is to close the open data connection so that it is not left hanging. Hanging connections are not immediately garbage collected and after a certain number are left hanging, the database server may stop responding for a time. Then one needs to manually terminate the hung processes in SQL Server itself, which can be a problem if one is not an administrator.

Here is the code needed to close the RecordSet and connection objects after use:

If objRS1.State = 1 then
 objRS1.Close
End If
Set objRS1 = Nothing
If objConn.State = 1 then
 objConn.Close
End If

This was a simple example of how to query a database using classic ASP.