nce you go beyond the basics in ASP?scripting some fancy output?the next thing you usually want to do is access a database and display data from it. This ASP primer will show you how to access data in a database from your ASP page.
Before you begin, you need to get your database ready. You can access any ODBC-compliant database from your ASP pages using ActiveX Data Objects (ADO). This means you can access a Microsoft Access database or a Microsoft SQL Server database or even an Oracle database. For the sake of this article, I will demonstrate how to access a Microsoft Access database.
Next, you need an ASP page from which you can access this database. The ASP page will reside on a Web server machine running either Microsoft Internet Information Server (IIS) on Windows NT, Microsoft Personal Web Server (PWS) on Windows 95/98 or Windows NT workstation. The technique is the same irrespective of which Web server you have.
Create an ODBC DSN on Your Web Server
You can use the ODBC Administrator to create an ODBC Data Source Name (DSN) on your Web server machine to access your database. Choose Start, Settings, Control Panel, and then choose ODBC. This brings up the ODBC Administrator (see Figure 1).
The ODBC Administrator allows you to create and manage ODBC DSNs. There are three kinds of DSNs. A User DSN stores the information in the Windows Registry and is available only to the user logged on the machine. If a different user logs on, or if no user is logged on, then the User DSN will not be available. A System DSN is also stored in the Windows Registry, but is available to all users logged on the machine, and is also available to your ASP page even if no user is logged on the machine. So for our purpose, a System DSN is better.
The third kind of DSN, the File DSN, stores its information physically in a file on the disk. Like the System DSN, it is available to all users logged on the machine, even if no user is logged on. In addition, because it resides physically as a file on the disk, it can be easily transported to another machine. So you can use the DSN from the other machine without having to modify its Registry settings?a distinct advantage when it comes to deploying your ASP pages. You may be developing against a development machine in your home or office and may want to actually deploy your ASP pages at your ISP’s machine. Using a File DSN means that you do not have to tamper with the Registry settings on the ISP’s machine?something your ISP will welcome.
The only drawback with the File DSN is that it is a bit slower than the System DSN and the user name and password are stored in plain text in the DSN file on disk. Anybody with access to the DSN file on disk can read your user name and password by simply opening the file in a text editor like Notepad. So, bottom line, make a choice: System DSN or File DSN. Personally, I prefer a File DSN because it is portable. On an NT server, I can control the access to the file to authorized users only.
To create a new DSN, click Add. In the next screen that pops up, choose the type database you wish to connect to. Choose Microsoft Access driver for an Access database; choose SQL Server for a SQL Server database (see Figure 2). If you are creating a File DSN, choose the Next button and provide a full name and path for your DSN. If you are creating a System DSN, click the Finish button.
You will then be taken to the Driver Manager for the database you selected. In the case of Access, you will need to provide the full path and name of your database. In the case of SQL Server, you will need to supply the Server Name, a login ID and password, and the database within the server you wish to connect to. Follow the instructions and you are done.
For this article, you will create a File DSN to the NWIND.MDB file that ships with Access. Copy the NWIND.MDB to a suitable location on your Web server machine, preferably within the directory structure of your Web site. This makes it easier to access and will not cause any security problems when accessing from the Web page, especially on a Windows NT server.
Generate Your ASP Page to Access the Database
Let’s assume that the ASP page is going to open the database and display all the data from the Customers table in a neat grid format. Use this code to generate your basic ASP page:
Note: I have found it useful to keep this code as a template for all my ASP pages. It includes a large comment section on the top for identifying what this page is, what it is for, who created it, when, and so forth.
Create ADO Objects in Your ASP Page
The simplest way to access a database using ADO uses just two objects: a Connection object to connect to the database, and a Recordset object to obtain the data from the database. This technique is also the fastest because it returns a read-only, forward-scrolling Recordset. In 90 percent of the cases of database access from an ASP page, you only wish to access data from the database and dump the contents on to the browser. For this purpose, a forward-scrolling, read-only Recordset is ideal.
In the section of the template where the ASP preprocessing takes place, use this code to create a Connection Object and a Recordset object:
' -- Declare VariablesDim objConn ' Our Connection ObjectDim objRS ' Our Recordset ObjectDim strSQL ' Our SQL String to access the databaseDim strConnection ' Our Connection string to access the databaseDim i ' a counter variable' -- Create objectsSet objConn = Server.CreateObject("ADODB.Connection")Set objRS = Server.CreateObject("ADODB.Recordset")
At this point, you have an empty Connection object (objConn) and an empty Recordset object (objRS).
Open a Connection to Your Database
Use the Open method of the Connection object to open a connection to the database. The Open method requires up to three arguments. A connection string is the first argument, followed by an optional user ID and an optional password argument. The connection string tells ADO how to connect to the database. If you are using an ODBC DSN, all you need to provide is the name of your DSN. If the DSN holds the user name and password within it, you do not need to supply any more arguments. If not, you will need to include the user ID and password arguments. In the sample, the File DSN itself contains the user ID and password, so you do not need any more arguments. All you need to write is:
Now, the way you specify the DSN Name in the Open method differs based on whether you are using a System DSN or a File DSN. If you are using a System DSN called NWIND, then you would write code like this:
If you are using a File DSN, the code is almost identical, except instead of saying “DSN=”, you code it as “FILEDSN=”. Also, the full path and name of the DSN file needs to be spelled out. For example, if your DSN file is physically located on the Web server machine at “C:WEBSASPPRONWIND.DSN” then your code would be:
If you need to provide the user ID and password, add them as separate arguments:
ObjConn.Open "DSN=NWIND", "USERID", "PASSWORD"
To make your code simpler to modify, place the connection string in a variable and use the variable name instead:
' -- Connection String ValuestrConnection = "FILEDSN=path on my machineNWIND.dsn"' -- Open the ConnectionobjConn.Open strConnection
Populate the Recordset Object with Data
Because you need all the data from the Customers Table, your SQL statement to fetch the data will be “SELECT * FROM CUSTOMERS”. Place this statement in a variable, again to facilitate code maintenance, and then use the Connection object’s Execute method to open a Recordset:
' -- Our SQL StatementstrSQL = "SELECT * FROM CUSTOMERS"' -- Populate our Recordset with dataset objRS = objConn.Execute (strSQL)
At this point, you have a Recordset (objRS) filled with data. That’s all it takes to get data from a database. To recap how you got the data in two simple code fragments, here are the main code statements again:
' -- Open the ConnectionobjConn.Open strConnection' -- Populate our Recordset with dataset objRS = objConn.Execute (strSQL)
Check to See if the Recordset Has Data
Before you proceed with accessing the data from the Recordset, it is always a good idea to check to see if you did indeed get back any data or not. To do so, check the Recordset’s BOF and EOF properties. If they are both true, you got back an empty Recordset with no data. In the sample, you are simply outputting an error message and ending the page with a “Response.end” so that no further code gets processed. In reality, you may want to provide a better error message, perhaps by redirecting the user to a separate error-handling ASP page:
if (objRS.BOF and objRS.EOF) then response.write "No records found" response.endEnd if
Process the Recordset Data
If you do get data back in the database, you can process the data. You can move from record to record within the Recordset by using the Recordset’s MoveNext method. You can access any field in the Recordset by using the Recordset(“fieldname”) syntax, or the Recordset(“fieldnumber”) syntax. In the sample, you are outputting all the columns of the Recordset as columns of a table, and all the records as rows in the table.
Let’s begin by creating an HTML table. In thesection of your ASP page, create an HTML table tag:
You will first output a heading row containing the names of the fields in the Recordset object. Because you do not know the names, you will just iterate through the Fields collection of the Recordset to obtain the field names. In this code, you obtain the field names by using “objRS.Fields(i).Name”:
" For i = 0 to objRS.Fields.Count - 1 Response.Write "
" & objRS.Fields(i).Name & "" Next Response.write ""
You then “walk” through the Recordset outputting the field values. Again, in this code, you obtain the value of a field by using “objRS.Fields(i)”. This defaults to the .Value property of the field. You could have also used the code “objRS(i)” and obtained the same results:
' -- Now output the contents of the Recordset objRS.MoveFirst Do While Not objRS.EOF ' -- output the contents Response.Write "
" For i = 0 to objRS.Fields.Count - 1 Response.Write "" ' -- move to the next record objRS.MoveNext Loop " & objRS.Fields(i) & "" Next Response.write "
Close Your Objects Properly
Once you are done processing data, it is important to close the objects. Doing so not only regains your server resources, but it also disconnects from the database server, thereby improving performance in a multi-user scenario. Close your objects by using the Close method on the object. Physically purge them from memory by setting their values to “Nothing”:
objRS.Close set objRS = Nothing objConn.Close set objConn = Nothing
You should see a screen that looks like Figure 3. I have modified the output by changing the font used for the display (it makes the output look nicer than the default Times New Roman font that the browser uses).
Get a copy of the code here
Now you know the basics of accessing a database. The next article in this series will demonstrate how you can access data based on criteria provided by the user via an HTML form.