Introduction to Active Server Pages - Part II

Connecting to a Database

We will create an ADO (ActiveX Data Objects) connection to an Access database. The first step is to create a server object using the Server.CreateObject() method, e.g.

<% connCW = Server.CreateObject("ADODB.Connection"); %>

where connCW is just an arbitrary variable name. Then you establish a connection to the database using an ODBC (Open Database Connectivity) driver and the open method of the Server object. An OBDC driver is a program that translates messages from an application into the syntax expected by a specific database. We will use what is termed a DSN-less Connection String.

<% connCW.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ="c:\sample\bill.mdb"); %>

(e.g. see Database-Driven Web Sites by Morrison and Morrison, p. 275)

In the above statement there has to be a space between Driver and the left parenthesis.

DBQ requires the absolute, physical location of the database; it cannot be specified in relative terms which is of course the preferred way of doing references to files within one's website so as to make one's website portable (i.e. one doesn't have to change references when the site is moved to another server.) In addition to portability issues, specifying the physical location of the database would typically require contacting one's hosting provider since this information would typically not be disclosed by the hosting provider.

MapPath() to the Rescue

Fortunately, the MapPath() method of the Server object returns the absolute, physical location of the database but one can pass to the method the location of the database relative to the ASP document in which the MapPath() method will be used. Thus one can reference the database in relative terms, which provides portability, and one doesn't have to ask one's hosting provider for the physical location of the database. Thus the above code becomes:

<% connCW.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + Server.MapPath("bill.mdb")); %>

where the argument supplied to the MapPath method is the relative reference to the database.

Alternative Methods

There are two other approaches to connecting to a database which are discussed in texts, but which I don't use -- these are described next

The System DSN Alternative

Another approach to connecting to a database is with a System Data Source Name (DSN). In Windows XP, one creates a System DSN by choosing Start>Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and then selecting Add and then choosing the appropriate driver from the list and then clicking Finish and then entering a data source name and clicking the Select button and browsing to select the database, and then clicking OK twice. This writes to the registry which requires Administrative Privileges. Once the System DSN is established, the ASP code is as follows:

<% connCW.Open("accessDSN") %> where "accessDSN" is the DSN.

(see Teach Yourself E-Commerce Programming with ASP in 21 Days by Stephen Walter with Jonathan Levine, p. 93). However this method requires asking the server administrator to create a system DSN.

The File DSN Alternative

Another approach is to use a file DSN where the open command will specify the location of a file DSN (see "ACC97: Using a File DSN or DSN-Less Connection with ASP Files", Microsoft Knowledge Base Article 167294, last review January 19, 2007.) The open command then becomes:

<% connCW.Open("FILEDSN=c:\sample\bill.dsn") %>

where bill.dsn is a file. The file DSN is created using the same process as a System DSN except that one clicks on File DSN instead of System DSN in the Data Sources (ODBC) panel. The creation of the file DSN requires administative privileges; although the file could be created on the client computer and then transferred to the server. Since the reference to the database would be to the database on the client computer, I'm not sure how the file would reference the database on the server and thus create a connection.

SQL Commands

Once one connects to a database, one will often want to execute SQL (Structured Query Language) commands against the database. The syntax of the SQL used in these commands will vary slightly depending on the database being used. The syntax below is for Microsoft Access. A good way to generate SQL commands is to do the query in Access and then switch to SQL view in Access to see the SQL which you can then paste into your ASP code.

SELECT statement

The select statement selects certain fields and records from the database. The default query type in Access is a SELECT query. SQL statements are not case-sensitive although the convention is to put SQL keywords in all uppercase and everything else in lower case..

SELECT f1,f2,f3 FROM tablename

Here f1, f2, and f3 are fieldnames in the table tablename which is a table in the database one has connected to

SELECT * FROM tablename.

The * selects all fields in the table.

SELECT * FROM tablename WHERE f1="bill" ORDER BY f2

This selects all fields from all records in tablename where the value of field f1 is equal to "bill" and sorts the records by the field f2. If f1 is a numeric field rather than a text field, the quotes would be omitted.

In a relational database, one will often want to do select queries using data from multiple tables. If a fieldname is unique (only found in one table), one can simply refer to it by name as before; if it is not unique, then one must precede the field name by the name of the table followed by a period. The SELECT query will join the table based on the field(s) in common -- typically the primary key in one table and the corresponding foreign key in another table. One way to do this is as follows:

SELECT * FROM t1, t2 WHERE t1.fid = t2.fid

INSERT statement

INSERT INTO tablename VALUES (f1value, f2value, ...)

This inserts a new record into tablename and assigns the list of values to the columns in the order in which they appear in the table. Values inserted into text fields must be surrounded by single quotes whereas numbers will not be. The INSERT statement can also only insert values for selected fields as shown below:

INSERT INTO tablename (f2, f3, f4) VALUES(f2value, f3value, f4value)

In either case (specifying a value for all fields or just for some), one does not specify a value for a field which is defined in Access as autonum; Access will take care of creating the value for that field.

UPDATE statement

UPDATE tablename SET f1='f1val', f2=3 WHERE f3='bill' AND f4=10

This sets the field f1 (which we assume is defined as text in Access) to f1val and the numeric field f2 to 3 in all records where the value of f3 = 'bill' and the value of f4=10.

DELETE statement

DELETE FROM tablename WHERE f4=10

This deletes all records in the table where the value of field named f4 is 10. If there is no WHERE clause, all records in the table are deleted.

USING SQL Statements in ASP

The SELECT statement does not alter the database (the Access table) whereas the INSERT, UPDATE, and DELETE statements do. Thus for the latter, one simply executes the SQL statement against the database by using the Execute method of the Server object. However, since the purpose of the SELECT statement is to display the data, the results of applying the Execute method must be assigned to a variable so that these results can be displayed. For example, one would write

<% rsItem = connCW.Execute("SELECT * from tablename"); %>

where rsItem is an arbitrary variable name. One can then loop through the records in the resulting recordset. For example, in the code below, one first writes out the column headings using HTML and then each row in the recordset generates a row in the HTML table. One uses the EOF property to check to see whether you have moved beyond the last record, and the MoveNext() method to move to the next record in the recordset.

<% str = "SELECT * from games WHERE TicketsAvailable > 0";
rsItem = connCW.Execute(str); %>
<table>
<tr><td><b>Date of Game</b></td>
<td><b>Day of Game</b></td>
<td><b>Time of Game</b></td>
<td><b>Opponent</b></td>
<td><b>Price</b></td>
<td><b>Tickets Available</b></td>
</tr>
<%
while (!rsItem.EOF) {
if (rsItem("DateofGame")>=today-(24*60*60*1000)) {
%>
<tr valign="top"><td><% =rsItem("DateofGame") %></td>
<td><% =rsItem("DayofGame") %></td>
<td><% =rsItem("TimeofGame") %></td>
<td><% =rsItem("Opponent") %></td>
<td><% =rsItem("Price") %></td>
<td><% =rsItem("TicketsAvailable") %></td></tr>
<% } rsItem.MoveNext() }
connCW.Close() %>
</table>

In the above case there are two criteria - that there are tickets available and that the game date is not already past. I handle the first in the SQL statement and the second in the ASP if statement. It is probably more efficient to handle both in the SQL statement, but I had difficulty using dates in the SQL statement.

Using User Input in SQL Commands

In most cases, one will want to use user input in SQL commands and thus the SQL statement will not be as simple as "SELECT * from tablename". Thus the SQL statements will be dynamic, not static. An example of dynamic SQL in a SELECT statement would be if one wanted to select a given record based on user input. For example, in a "drilldown approach", several records will be displayed on one page and then when the user clicks on a particular record, one goes to another page devoted exclusively to that record. The links in the first page all go to the same page, but the links are written dynamically with different values for a querystring variable that corresponds to the primary key in the table and thus the receiving page will know which link was clicked. In an INSERT statement, one typically won't be inserting constants, but instead will be inserting input from users that has been entered into form fields. The same goes for an UPDATE statement and a DELETE statement.

Dynamic SQL typically involves string concatenation of string literals with user input. For example, to select a record corresponding to a username that the user entered in a textbox on the previous page, one might write

<% str = "SELECT custid FROM customer WHERE username = '" + Request.QueryString("username") + "'";;
connCW.Execute(str); %>

Assigning the SQL statement to a separate variable is often useful if the statement needs to be built up from several pieces of form input. One can then display the SQL statement using Response.Write(str) to determine the SQL statement is what one expects. When one is forming these dynamic SQL statements one must pay attention to whether the field in the database is text or numeric. If text, the value in the WHERE clause of a SELECT statement or the VALUES portion of an INSERT statement must be surrounded by single quotes; if the field is numeric, one does not use single quotes around the value.

Examples

SELECT statement and ASP code that populates drop-down, selection lists from an Access database - www.stanfordclubs.org/dc/remind.asp - page then submits to an ASP action page which adds a reminder to the database. The list of events in the Access database used to populate the drop-down selection lists, is displayed in www.stanfordclubs.org/dc/createevent.asp and updated through another action page.

Code Samples

SELECT statement and ASP loop code that generates HTML table displaying alumni club members who have renewed and the total number of renewed members - www.stanfordclubs.org/dc/listmembers.asp - Similar to before but this creates a new field that stores the total number of records in the recordset.

lastname = Request.QueryString("lastname") + "";
//convert to string, otherwise variable refers to QueryString object
str = "SELECT * from primarymember";
count = "SELECT Count(primarymember.sortablename) AS CountOfsortablename FROM primarymember";
rsItem = connCW.Execute(str);
rsItem1 = connCW.Execute(count);
%>

<h2>There are <% =rsItem1('CountOfsortablename') %> Current WDCSA Members</h2>
<table>
<tr><td><b>Last Name</b></td>
<td><b>Full Name</b></td>
<td><b>Membership Expires</b></td>
</tr>
<% while (!rsItem.EOF) { %>
<tr valign="top"><td><% = rsItem('lastname') %></td>
<td><% =rsItem("prefmailname") %></td>
<td><% =rsItem("validuntil") %></td></tr>
<% rsItem.MoveNext() }
connCW.Close() %>
</table>

An ASP action page (submitted to by a form page) that adds a reminder record to the database. Fixquote() is a function that checks whether the user entered a single quote in the text in the form page; this would cause the SQL insert statement to produce a syntax error.

<% eid= Request.QueryString("eid");
days = Request.QueryString("days");
email = Request.QueryString("email")+ ""; // convert to string, otherwise variable refers to Querystring object
enote = Request.QueryString("note")+"";
enote = fixquote(enote);
str = "SELECT * from event WHERE eid=" + eid; // to get only the right event

rsItem = connCW.Execute(str);
edate = Date.parse(rsItem("edate"));
edate = edate - days*1000*3600*24;
mdate = new Date();
mdate.setTime(edate);
strdate = (mdate.getMonth()+1) + "/" + mdate.getDate() + "/" + mdate.getFullYear();
strdate += " " + mdate.getHours() + ":" + mdate.getMinutes() + ":" + mdate.getSeconds();
// just using date directly in the SQL expression produces a syntax error

sep = "','";
str = "INSERT INTO email(eid,mdate,email,enote) VALUES (" + eid + ", '" + strdate + sep + email + sep + enote + "')";
connCW.Execute(str); %>

Revised: August 18, 2010. Comments to William Pegram, bill@billpegram.com