Web Databases

It all depends on the data concerned, what tool you used to create the database, whether you're expecting ten hits per day or ten million, and whether your visitors are just reading from the database or will be writing to it too. Even so, a large proportion of these enquiries come from people who simply want to display items held in an Access database, and doing this is easy.

Access is perfectly okay for a read-only site with a fairly low hit rate, but not if your site gets very busy, or if there are multiple concurrent writes. We're going to look at how to drag data from an Access database, using Microsoft's IIS (Internet Information Server) running ASP hosted on an NT server (or Personal Web Server running on Windows 9x or NTW). The majority of you should therefore have all the software required, and be able to run these examples directly on your machine. In the next few months we'll go through the same exercise using Perl on a Linux server.

So you've got this Access MDB database you want to put on-line, and let's assume for simplicity that it's a typical contacts list containing name, email address and phone number. The first thing most tutorials or manuals will tell you is that you'll need to get your ISP to set up a system DSN (Data Source Name: see Control Panel/OBDC) on their server to point to the database. This is nonsense as you can get to your database without any need for a system DSN - you can do it all from your ASP source code.

The first step is to establish a connection with your database: we use something like this:

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "DRIVER=Microsoft Access Driver (*.mdb);" & _
"DBQ=" & server.mappath("\contacts\contacts.mdb")&";"

The trick that avoids having to use a system DSN is to know exactly where the database is living as a full physical disk path. If your site is being hosted by an ISP, you probably won't have a clue what this is. It could be anywhere between a C: drive and a Z: drive; you might be in the default directory \InetPub\wwwroot\your sitename\ or somewhere completely different. This is where that extremely useful server.mappath() function comes in, which takes a virtual path from your Web server and returns precisely the physical path information that you need here, and once you have that you can hit the database directly.

The next step is to grab contact data from the database. You've got a choice here, either to use the ADO calls built into IIS, or else to use SQL. ADO is probably easier but SQL is more flexible, and when things start to get complex it often gives more 'readable' code. We'd do something like this:

Set rst = Server.CreateObject ("ADODB.Recordset")
rst.Open "SELECT * from ContactsTable", DataConn

You've opened it, you've selected all of the data from it; now you have to display it, using something along these lines:

do while not rst.EOF %>

<B>Name:</B> <%= rst("FirstName") %>
 <%= rst("LastName") %><br>
<B>Phone:</B> <%= rst("Phone") %><br>
<B>Fax:</B> <%= rst("Fax") %><br>
<B>Email:</B> <%= rst("email") %><P>
<% rst.movenext

loop

It's just a matter of looping over all of the data in the recordset until you get to the end. Finally, tidy up by closing things down:

rst.Close DataConn.Close

That's it, your contacts database displayed in full, with all the processing done in less than a dozen lines of code. The next logical upgrade might be to modify this code so that it looks for people with a certain email address, or with a certain surname, which you can do by using query strings passed in as part of the URL. Add some code around the SQL select statement as follows:

if request("field") = "" then
rst.Open "SELECT * from ContactsTable", DataConn
else
rst.Open "SELECT * from ContactsTable " & _ "WHERE("&request("field")&"='" & _ request("value")&"')",DataConn
end if

See how we've slipped that extra WHERE clause in, which is only used if there is a value in the field query string. Now, all you have to do is add some information to the end of the URL, something like this:

http://tech/pcpro.asp?field=FirstName&value=Paul http://tech/pcpro.asp?field=LastName&value=Newton

Having shown you this WHERE technique, we now have some important advice. Don't do it like this! Can you spot the danger? Take another look at that alternative SQL statement with the WHERE clause: it picks up information directly from the field and value query strings and puts these straight into the SQL. You should never do this as some competitor or hacker could do no end of damage via a loophole like this: always pre-process the user input prior to letting it near your database. (The same warning applies to any parameters being passed to executables or to the command line.)