Connecting and Displaying to an SQL Database

Introduction. One of the major starting points for all work with SQL Server databases is the Northwind database. It is a fairly classic full featured customer/product/order type of database. We will be working with the Customers table that has most of the obvious fields.

One thing to remember when working with SQL Server is that the administrative tools mislead you to believe that your regular NT/2000 network authentications will be sufficient for working within SQL Server. This is not true. We needed to create separate SQL Server security in order to be able to use it for connecting from ASPs.

Now that I am done with that diatribe against the foibles of Microsoft, it is understandable that SQL Server should have extensive security that goes above and beyond what one normally uses for network operations. It's just that one would hope the documentation wouldn't mislead you to believe that NT/2000 authentications will take care of at least your basic security needs.

Connecting to a Database Using an ASP. There are two major ways to connect to a database from an ASP.

System DSN Connections

DSN-Less ADO Driver Connections

We will focus on the DSN-Less types of connections because these are what I have been able to implement on this server. The DSN or Data Source Name connections appear to be a bit cleaner, but it seems you need to have more direct access to certain things on the server in order to implement them. If you are running Windows 2000 or have direct access to the web server you are more likely to want to use this approach.

DSN-Less Connections. I will present some code snippets that you can modify to fit your own webs. The snippets assume that you have an SQL Server database with name DatabaseName on the database server. The table within this database that you want to work with is called TableName. We will work an example in the next web page for this week.

First we must develop the usual HTML that signals the server to use the ASP.dll. We also have a statement that tells the server to include some information from elsewhere on the server.

<%@ Language=VBScript %>
<% Option Explicit %>


The first two statements should be familiar. If you've worked with C++ you have included header files and libraries. The third statement has a similar purpose. The included file has a number of things in it that are useful for connecting to databases. It also includes the "definitions" of some ADO constants that we will use as parameters. These are similar to the VB constants you've probably used in the past such as vbOkOnly, vbYesNo, vbYes and vbNo.

The adovbs.inc file is automatically included on the server when the IIS = Internet Information Server is installed. It is in the C:\Program Files\Common Files\System\ado\ directory on the server. It needs to be copied to your root web by the server administrator. What I have done is e-mailed it to you as an attachment and you should upload it to your root directory..

If you are more than a user on the server the nature of this #include command is likely to change so that you can access the file differently. Unfortunately, we are all just little, nearly totally unimportant users, on the faculty server.

The next snippet of code has to do with establishing the connection.

' Open a connection to our SQL Server database
' We will use DSN-less connection
Dim connDatabaseName
Set connDatabaseName = Server.CreateObject("ADODB.Connection")

connDatabaseName.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=DatabaseName; UID=someUserID; PWD=somePassword"

connDatabaseName.Open

This is actually just four lines of code due to the _ line continuation character combination. Remember you must have a space in front of the underscore in order for it to work.

You first need to dimension a name or your connection object.

Then you set this variable to an ADODB type of connection.

Then you must specify the driver and the the server. You don't need to specify the path to the database when using the SQL Server driver like you do when working with Access.

You need to supply your UID and PWD.

Then you actually open the connection.

Now that the connection is open you need to open the particular recordset. This is done with a set of code similar to the following.

' Instantiate a recordset object and retrieve the info from the tablename table

Dim rsTableName

Set rsTableName = Server.CreateObject("ADODB.Recordset")

rsTableName.Open "TableName", connDatabaseName, , , adCmdTable

These three lines of code are quickly described in the following bullets.

You need to dimension a name for your recordset object.

Then you create the recordset object

Then you open it based on the connection you developed above. We will describe more about the rsTableName.Open command later in this page.

The last thing you need to do after implementing these connections is to make sure you close them after you are done. This ensures a much cleaner operating environment and is very important.

' Clean up the ADO objects

rsTableName.Close
Set rsTableName = Nothing

connDatabaseName.Close
Set connDatabaseName = Nothing

You really do need to close the recordset first, then the connection and set each of them to Nothing.

The Open Method for the Recordset Object. We have just made use of the objRS.Open command in the previous set of code. I probably need to say a little bit more about it. In general the Open method will be of the form

objRecordsetInstance.Open Source, ActiveConnection, CursorType, LockType, Options

This is why the command above has so many commas. By not filling in any values for certain parameters the default values are used.

The Source is likely to be some table or even the results of an SQL query.

The ActiveConnection is already opened connection.

The CursorType can be

adCmdTable - for a forward only moving cursor or fire hose cursor

adOpenStatic - this scrollable cursor able to make use of all move commands in VBScript.

adOpenKeyset - another scrollable cursor with slightly more involved features.

adOpenDynamic - the last scrollable cursor with the highest level features.

The LockType can be

adLockReadOnly - records are read only and can't be modified

adLockPessimistic - records are locked immediately when someone tries to edit, no one else can.

adLockOptimistic - records are locked only at time of an update

adLockBatchOptimistic - records aren't locked until a batch update occurs.

You need to include the adovbs.inc in order to make use of these constants. The defaults are for the forward only cursor and the read only lock. This is only a brief survey which we will elaborate on when needed.

Remember we will be using SQL for our database developments and not doing our database interactions based on VB.

Connect and Display Customers in Northwind. Since you have essentially seen all of this code when working with Access databases you should copy it into a file called ConnectToNorthwind.asp and upload it to your cisdev account.