Setting Up an ODBC Data Source

In Windows XP, we need to open up the Windows Control Panel from the Windows Start menu. (See Figure 17-27.)

Figure 17-27

Depending on your PC settings you may see the classic console or the default console as shown Figure 17-27. We need to access the administrative tools option by clicking the Performance and Maintenance icon, which results in the screen shown in Figure 17-28.

Figure 17-28

Now click the Administrative Tools icon to see the screen shown in Figure 17-29.

Figure 17-29

Finally, double-click the Data Sources (ODBC) icon to show the data source panel shown in Figure 17-30.

Figure 17-30

While the Data Source panel is the same on older versions of Windows, the steps to access it are slightly different. First we need to open up the Control Panel on our Windows 98, 95, or NT computer by using the Start ® Settings menu. Next double-click the ODBC icon, which opens up the ODBC window. If you're using Windows 2000, you'll find ODBC under Administrative Tools in the Control Panel.

In the ODBC window, we select the System DSN tab. We want to add a data source for the machine, not a data source specific to a user. (See Figure 17-31.)

Figure 17-31

To add a new data source, click the Add button and the Create New Data Source dialog box will appear. (See Figure 17-32.) We want to add a data source that connects to an MS Access database, so make sure that Microsoft Access Driver (*.mdb) is selected, and then click the Finish button.

Figure 17-32

We're now given the opportunity to give our data source a name and select the .mdb file that contains our database in the ODBC Microsoft Access Setup dialog box, shown in Figure 17-33.

Figure 17-33

In the Data Source Name text box, enter CompBitsCoDB. Then click the Select button, browse to the CompBitsCo.mdb file, select it, and click OK. We can also enter a description of the data source in the Description box. This is optional, but may help us remember later what the data source is used for.

When finished, our data source box should look something like Figure 17-34. Click OK to finish the set up.

Figure 17-34

The new data source we've created will now be listed in the ODBC window. If we need to alter it, for example if the location of our database changes, just click the Configure button. If we switch to another database type, such as SQL Server or Oracle, we just delete the data source by clicking the Remove button, and then we set up a new one with the same name.

The Connection String

Our connection string will depend on the database parameters, such as the location of the database, any user login details (username and password), and whether we're using ODBC or OLE DB.

For example, to connect to the ODBC connection we just made for our CompBitsCo Access database, the connection string would just need to specify the data source name (DSN).

"DSN=CompBitsCoDB"

Connecting using OLE DB requires slightly more effort.

First we need to specify which OLE DB driver, known as a provider, will provide the connection. In our case it's an Access database, so the provider will be Microsoft.Jet.OLEDB.

We also need to specify the data source, which is where the database's .mdb file is located. Remember that in JavaScript the \ character is a special escape character. Since we need to use it to specify directory paths, we need to escape the escape character, by putting an extra \ in front of it, so that it is treated as an actual backslash.

I've put my CompBitsCo.mdb in the directory C:\temp, so my data source will be as follows:

"Data Source=C:\\ temp\\CompBitsCo.mdb"

Putting this information together, our OLE DB connection string will look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp\\CompBitsCo.mdb

Creating a Connection Object and Opening a Connection

A Connection object allows us to open up a reusable connection to a database that we can access again and again within the same page. To do this we first need to create a Connection object, and then use it to open the connection to the database.

We create a new Connection object using the Server object's CreateObject() method. The name of the library containing the ADO objects is ADODB. The parameter to pass to the CreateObject() method is this library name, ADODB, followed by a dot and then the name of the ADO object that we want to create, which in this case is the Connection object.

So, to create a Connection object we write the following:

var adoConnection = Server.CreateObject("ADODB.Connection");

Next we use the Connection object's Open() method to open a connection to a database. The Open() method takes a connection string as the first parameter. For the examples we use here, we'll use the ODBC data source we created earlier, and so the ODBC version of the connection string. Therefore, to open a connection to the CompBitsCoDB ODBC data source requires the following:

adoConnection.Open("DSN=CompBitsCoDB");

Now we have our connection to the database. We'll see shortly how to use it, but for now let's see how having made use of the connection, we then close it and release the object we created so as to free up memory.

adoConnection.Close();
adoConnection = null;

First, we close the connection using the Close() method of the Connection object. Then, by setting the variable containing the Connection object to null, we let it be known that we have finished with the variable and it can be removed from memory.

The Connection object is not the only way of connecting to a database. We'll see later how we can create a temporary connection with the Recordset object.

However, now that we have our connection, let's look at how we can use it.

Try It Out – Returning a List of Stock

Let's put what we have learned into action and create a web page that returns all the records in our CompBitsCo database's Stock table. We'll return the values in an HTML table for formatting reasons. For each item, we'll also add a hyperlink that links to a page that enables the purchase of that item, although this page won't be created until a later example.

<%@ language = JavaScript%>
<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<body>
<table border=1>
<Thead>
   <tr>
   <th>Product</th>
   <th>Quantity In Stock</th>
   <th>Cost per Item</th>
   <th></th>
   </tr>
</Thead>
<%
// Open connection to database, then populate a recordset with list of stock
   var adoConnection = Server.CreateObject("ADODB.Connection");
   var adoRecordSet;
   var mySQL;
   adoConnection.Open("DSN=CompBitsCoDB");
   var mySQL = "select StockId, ItemDescription, NumberInStock, ItemCost" +
      " from Stock";
   adoRecordSet = adoConnection.Execute(mySQL);
// Loop through recordset and write stock details out to page
   while ( adoRecordSet.Eof == false )
   {
%>
<tr>
   <td><%=adoRecordSet("ItemDescription").Value%></td>
   <td><%=adoRecordSet("NumberInStock").Value%></td>
   <td><%=adoRecordSet("ItemCost").Value%></td>
   <td><A href="buyitem.asp?StockId=<%=adoRecordSet("StockId").Value%>">
      Buy this</A></td>
</tr>
<%
   adoRecordSet.MoveNext();
   }
// Close Recordset and connections
// and release memory used by Recordset and Connection objects
   adoRecordSet.Close();
   adoRecordSet = null;
   adoConnection.Close();
   adoConnection = null;
%>
</table>
</body>
</html>

Save this page as DisplayStock.asp into the physical directory of the virtual directory AWalkOnTheServerSide that we created in the previous chapter.

Browse to the page in your browser by typing http://localhost/AWalkOnTheServerSide/DisplayStock.asp.

We should see a screen similar to that shown in Figure 17-36.

Figure 17-36

If we see a screen like that shown in Figure 17-37, we have the Access database still open and it's preventing the server from using the database. The easiest way around this is to simply close MS Access.

Figure 17-37

You'll notice that the BigBit price is shown as 10.989, whereas in the database it's shown as 10.99. The reason for this is that Access formats its display of data. In the case of currency, it displays only the first two digits after the decimal point and does any rounding necessary. However, the data itself is still 10.989. Remember that originally BigBit cost 9.99, and we then executed a query that added 10% on to this price, which made it 10.989. Access has the round function we can use to limit the number of decimal places. This function would be best used with the original SQL that updated the database and added 10% to stock prices.

We could also write a JavaScript function that fixes the number of decimal places to two, as we saw in Chapter 4. However, if this were a real online shopping application we'd actually not be charging the correct price. The correct price is 10.989 as far as the database is concerned, so the database needs to be changed. Because this is just an example, we'll leave it as it is.

How It Works

We start by making JavaScript the default server-side script language for the page.

<%@ language = JavaScript%>

Then in the body of the page we define our <table> and its <Thead>, containing four table headings. The first three of these are Product, Quantity In Stock, and Cost Per Item, but the last heading is empty. The rows of the table will contain the stock data and will be created dynamically later in the page.

<table border=1>
<Thead>
   <tr>
   <th>Product</th>
   <th>Quantity In Stock</th>
   <th>Cost per Item</th>
   <th></th>
   </tr>
</Thead>

Next we have the first server-side script block, the start of which is shown here:

<%
   var adoConnection = Server.CreateObject("ADODB.Connection");
   var adoRecordSet;
   var mySQL;
   adoConnection.Open("DSN=CompBitsCoDB");

In this script we first create a Connection object and declare two variables, mySQL, in which we will build our SQL query, and adoRecordset, which will hold the resulting recordset. We then open a connection to our CompBitsCo database.

In the following two lines of script, we first declare our SQL query, which will return information about the products in our Stock database table. We then run this query against the database using the Connection object's Execute() method. This returns a Recordset object, which we store in the variable adoRecordSet.

   var mySQL = "select StockId, ItemDescription, NumberInStock, ItemCost" +
      "from Stock";
   adoRecordSet = adoConnection.Execute(mySQL);

Our next code loops through the recordset stored in variable adoRecordset, starting with the first record and moving to the next, then the next, and so on until we are at the end of the recordset. We use each record to write HTML to the page displaying stock information.

The last column in the table is actually a link saying "Buy this." When clicked, the link will take us to the page buyitem.asp, which we will create in a later example. In the URL for the link, we also pass the StockId value for that record, which can be retrieved in the buyitem.asp page. Using this value, we will be able to determine what the customer wants to buy.

   while ( adoRecordSet.Eof == false )
   {
%>
<tr>
   <td><%=adoRecordSet("ItemDescription").Value%></td>
   <td><%=adoRecordSet("NumberInStock").Value%></td>
   <td><%=adoRecordSet("ItemCost").Value%></td>
   <td><A href="buyitem.asp?StockId=<%=adoRecordSet("StockId").Value%>">
      Buy this</A></td>
</tr>
<%
   adoRecordSet.MoveNext();
   }

Having created a row in the HTML table based on a row in the recordset, we then move to the next row in the recordset using the MoveNext() method. Note that it is very easy to forget to put this statement in and end up with an infinite loop that causes the web page to time out.

Finally, with all the data extracted, we close the Recordset and Connection objects and let the server know we've finished with the objects by setting the variables pointing to them to null. It's important that we do things in the right order: We must close the Recordset first, and then set the variable adoRecordset to null to allow the memory to be made available. Having closed the recordset, we can then close the connection and release that variable, again in that order.

   adoRecordSet.Close();
   adoRecordSet = null;
   adoConnection.Close();
   adoConnection = null;
%>

Opening Recordsets without a Connection Object

If we plan on opening a number of different recordsets within a page from a database by running SQL commands against it, it's worth using a Connection object as we did in the previous section. It means a connection to the database needs to be made only once in the page, and it speeds up the page's processing.

If, however, we want to retrieve only one recordset in a page, we can use the Recordset object without a Connection object. In fact, the Recordset object does create a Connection object for its own use, but it's destroyed immediately after it's used for retrieving the recordset and is not available for our own use.

The Recordset object has the Open() method, which opens not a new connection, but instead a new recordset. The minimum parameters we must pass to the Open() method are the SQL containing the select statement that will return the records, and a connection string like the connection sting we would pass to a Connection object's Open() method.

First we need to create a new Recordset object.

var adoRecordset = Server.CreateObject("ADODB.Recordset");

Then we open the Recordset, passing first the SQL, then the connection string.

var mySQL = "select StockId, ItemDescription, NumberInStock, ItemCost" +
             " from Stock";
adoRecordset.Open(mySQL, "DSN=CompBitsCoDB");

So far we have only seen recordsets that we can use the MoveNext() method to proceed through, records starting from the first record and moving toward the last. An additional benefit of opening recordsets in the way shown here is the ability to move forward, backward, and even jump to the beginning or end of the recordset. The downside of this type of flexibility is that greater resources are consumed and more load is placed on our web server.

To open a Recordset object that we can move forward and backward in, we need to pass an additional parameter to the Open() method, the CursorType parameter. When we open a Recordset with no CursorType parameter, we are opening a forward only cursor type recordset. As the name suggests, this allows only the MoveNext() movement and retrieves records as we move through the records. If we want to open what's called a static cursor type recordset, we need to pass the number 3 as the CursorType parameter. A static cursor type recordset contains all the records returned by a query and we can move forward and backward through it with the following commands:

• MoveFirst() moves to beginning of a recordset

• MoveLast() moves to end of a recordset

• MoveNext() moves to next record

• MovePrevious() moves to previous record

For example, to open a static cursor type recordset, we would use the following code:

var adoRecordset = Server.CreateObject("ADODB.Recordset");
var mySQL = "select StockId, ItemDescription, NumberInStock, ItemCost" +
   " from Stock";
adoRecordset.Open(mySQL, "DSN=CompBitsCoDB", 3);

This is identical to the previous example, except that we pass 3 as a third parameter for the Open() method of the Recordset object.

To move through the recordset, we would type commands from the following list:

adoRecordset.MoveLast();
adoRecordset.MoveFirst();
adoRecordset.MoveNext();
adoRecordset.MovePrevious();

Remember that static cursors take up more of our limited server resources, so don't use them unless you actually need the ability to move in any direction through a recordset.

Try It Out – Inserting Data

In a previous example, we displayed a list of stock, which allowed the customer to buy an item by clicking a link to the BuyItem.asp page. We'll now create this BuyItem.asp page, within which we create a form asking for the user's details. This form is then submitted to another ASP page where we update the CustomerOrder and OrderItem database tables.

<%@ language = JavaScript%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
<script>
function form1_onsubmit()
{
   var form = document.form1
   var controlCounter;
   var returnValue = true;
   var formControl;
// Check that all text boxes on form have not been left empty by user
// Cancel form post if form is not complete by returning false
   for (controlCounter = 0; controlCounter < form.length; controlCounter++)
   {
      formControl = form.elements[controlCounter];
      if (formControl.type == "text" && formControl.value == "")
      {
         alert("Please complete all of the form");
         formControl.focus();
         returnValue = false;
         break;
      }
   }
   return returnValue;
}
</script>
</head>
<body>
Please enter your customer details
<br>
<form action="AddOrderDetails.asp" method=POST name=form1 onsubmit="return
form1_onsubmit()">
<input type="hidden" name=txtStockId
   value="<%=Request.QueryString("StockId")%>">
Name
<br>
<input type="text" name=txtCustomerName maxlength=50>
<br>
Address
<br>
<input type="text" name=txtCustomerAddress maxlength=255>
<br>
Telephone Number
<br>
<input type="text" name=txtTelNumber maxlength=25>
<br>
<input type="submit" value="Send Order" id=submit1 name=submit1>
</form>
</body>
</html>

Save this page as BuyItem.asp in the same directory as DisplayStock.asp from the previous example. This page enables users to enter their names, addresses, and telephone numbers. When they have done this, they submit their details, which are posted to the next page, AddOrderDetails.asp, shown here:

<%@ language = JavaScript%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
</head>
<body>
<%
   // Array to hold names of months
   // used for creating date in format day month year
   var month = new Array("Jan","Feb","Mar","Apr","May","Jun","Jul",
      "Aug","Sep","Oct","Nov","Dec");
   var nowDate = new Date();
   var nowDate = nowDate.getDate() + " " + month[nowDate.getMonth()] + " " +
      nowDate.getFullYear();
   var orderNo;
   // Connect to database
   var adoConnection = Server.CreateObject("ADODB.Connection");
   adoConnection.Open("DSN=CompBitsCoDB");
   var adoRecordset;
   // Create SQL to insert new order into CustomerOrder table
   var mySQL = "insert into CustomerOrder " +
      "(CustomerName, CustomerAddress, TelNumber, OrderDate)";
   mySQL = mySQL + " values ('" + Request.Form("txtCustomerName") + "','";
   mySQL = mySQL + Request.Form("txtCustomerAddress") + "','";
   mySQL = mySQL + Request.Form("txtTelNumber") + "','";
   mySQL = mySQL + nowDate + "')";
   // Execute SQL to add new order
   adoConnection.Execute(mySQL);
   // Create SQL to get autonumber generated OrderNo from CustomerOrder table
   // for order just added
   mySQL = "select Max(OrderNo) AS MaxOrderNo from CustomerOrder where ";
   mySQL = mySQL + " OrderDate = #" + nowDate + "# AND ";
   mySQL = mySQL + " CustomerName = '" + Request.Form("txtCustomerName") + "'";
// Populate recordset with SQL to get OrderNo
   adoRecordset = adoConnection.Execute(mySQL);
   orderNo = adoRecordset("MaxOrderNo").Value;
   // Recordset not needed after this so close it and allow release of memory
   adoRecordset.Close();
   adoRecordset = null;
   // Create SQL to insert item ordered into OrderItem table
   var mySQL = "insert into OrderItem (OrderNo, StockId, QtyOrdered)"
   mySQL = mySQL + " values (" + orderNo + ","
   mySQL = mySQL + Request.Form("txtStockId") + ","
   mySQL = mySQL + "1)"
    
   // Execute SQL to insert details of order item purchased
   adoConnection.Execute(mySQL);
   // No more database access
   // so close connection and indicate memory no longer needed
   adoConnection.Close();
   adoConnection = null;
   Response.Write("<h2><center>Your order was completed successfully" +
      "</center></h2>");
%>
</body>
</html>

Save this page as AddOrderDetails.asp, again in the same directory as DisplayStock.asp.

Start by browsing to the DisplayStock.asp page on your web server, using the URL http://localhost/AWalkOnTheServerSide/DisplayStock.asp.

When we click any of the Buy this links to buy that item, we'll be taken to the customer form, shown in Figure 17-38, where we can enter our details. Notice that the StockId in the database for the item we clicked is added to the URL so that the page knows which item we clicked.

Figure 17-38

Then, when the Send Order button is clicked, the order is posted to another page, AddOrderDetails.asp, which enters the customer and order details into the relevant database tables, and displays a confirmation message. (See Figure 17-39.)

Figure 17-39

Check the database tables to see that our details have been added to the CustomerOrder and Order tables.

Note when using Windows XP, clicking Send Order results in the screen shown in Figure 17-40.

Figure 17-40

You may have a security access problem. Make sure that the file has all of its sharing properties enabled, especially "Allow network users to change my files." To do this, simply right-click the folder that the DB is located in and choose the Sharing tab. Typically, under Win2000, you can find it under a Security tab, as shown in Figure 17-41.

Figure 17-41

Although there are security implications, make sure all files are not shared giving access to anyone on the Internet. This is particularly important if we have a permanently on broadband connection such as cable or ADSL. Use of a software firewall is advisable. See also the following two links:

http://www.webwizguide.info/asp/faq/access_database_faq.asp

http://support.microsoft.com/default.aspx?scid=kb;en-us;q175168

How It Works

The way the code in the BuyItem.asp page works should be fairly familiar to us. The page starts with the familiar line, specifying that the script within the page is written in JavaScript.

<%@ language = JavaScript%>

Then within the head of the page, we define the form1_onsubmit() function. We will discuss this shortly.

Within the body of the page we have a form. The first control within this form is a hidden field. We saw how these worked in Chapter 6. They are similar to text boxes, except that the type attribute is set to hidden and therefore is not visible to the user on the browser. They allow us to store values in them, and these values are then submitted along with the rest of the form when the user clicks the submit button.

<input type="hidden" name=txtStockId
   value="<%=Request.QueryString("StockId")%>">

This hidden field is used to transfer the StockId of the item the customer is buying to the current page and also to send this value to the next page. We include the StockId in a hidden field on the form so that when the form is posted, the StockId is posted as well. The StockId itself was included in the URL by being placed at the end of the hyperlink as an item of data with name StockId. We can see this from the code snippet from DisplayStock.asp that created the URL.

<A HREF="buyitem.asp?StockId=<%=adoRecordSet("StockId").Value%>">

We use the Request object's QueryString property to extract the value from the URL.

The form also contains three text boxes, which the users are asked to fill with their names, addresses, and telephone numbers. The final control in the form is a submit button, which tries to submit the form when clicked.

However, within the definition of the <form> element, the onsubmit event handler is connected to the form1_onsubmit() function, which we will take a closer look at now.

The form1_onsubmit() function does a basic validation that checks that all the text fields in the form are filled in. We start by defining four variables.

function form1_onsubmit()
{
   var form = document.form1
   var controlCounter;
   var returnValue = true;
   var formControl;

Next, as we can see from the following code, we use the elements array property of the Form object and a for loop to go through each control in the form in turn. Within the for loop, we first check whether the control is a text control and whether its value is empty. If any text control is found to be empty, we alert the user, set the focus of the page to the empty control, and set the returnValue to false to prevent the form from being posted.

   for (controlCounter = 0; controlCounter < form.length; controlCounter++)
   {
      formControl = form.elements[controlCounter];
      if (formControl.type == "text" && formControl.value == "")
      {
         alert("Please complete all of the form");
         formControl.focus();
         returnValue = false;
         break;
      }
   }
   return returnValue;
}

Note that in addition to checking the validity in the browser, in real-world situations we should also do server-side validation using ASP code in the ASP page submitted to. Otherwise if someone doesn't have JavaScript enabled, no validation will take place.

We'll now turn to the AddOrderDetails.asp page, to which the form in BuyItem.asp is posted and where all the database action can be found. As usual, we start the page with the JavaScript language declaration.

In the AddOrderDetails.asp page we want to insert the customer and order details into the relevant tables in the database, based on the values posted from the form.

Within the body of the page, we start by getting today's date, which will be inserted into the OrderDate field of the CustomerOrder table.

   var month = new Array("Jan","Feb","Mar","Apr","May","Jun","Jul",
      "Aug","Sep","Oct","Nov","Dec");
   var nowDate = new Date();
   var nowDate = nowDate.getDate() + " " + month[nowDate.getMonth()] + " " +
      nowDate.getFullYear();

We want to insert a date in the OrderDate field in the form 1 Jan 2005, because this avoids any issues of date format. For example, in the United States 1/11/2000 is 11 Jan 2005, but a server in the UK would see this as 1 Nov 2005. Because the Date object's getMonth() method returns only numbers from 0 to 11 for the month, we need to convert this value to a three-letter month. By defining an array of month names in order, and then using the result of the getMonth() function as the array index, we get the name of the current month.

Next we declare another variable, and then create an ADO Connection object and make a connection, using ODBC, to our database. We also declare a variable to hold the adoRecordset object we'll be using shortly.

   var orderNo;
   var adoConnection = Server.CreateObject("ADODB.Connection");
   adoConnection.Open("DSN=CompBitsCoDB");
   var adoRecordset;

Our next block of code inserts the new customer order record into the CustomerOrder table.

   var mySQL = "insert into CustomerOrder " +
      "(CustomerName, CustomerAddress, TelNumber, OrderDate)";
   mySQL = mySQL + " values ('" + Request.Form("txtCustomerName") + "','";
   mySQL = mySQL + Request.Form("txtCustomerAddress") + "','";
   mySQL = mySQL + Request.Form("txtTelNumber") + "','";
   mySQL = mySQL + nowDate + "')";
   adoConnection.Execute(mySQL);

We use the variable mySQL to build our SQL string; specifically we use an insert statement with the inserted values being those passed by the form post. The posted values are retrieved using the Request.Form() collection property, which was introduced in Chapter 15. The completed SQL would look something like the following:

insert into CustomerOrder (CustomerName, CustomerAddress, TelNumber, OrderDate)
values ('Paul','Some Town','1234 56789','6 Jun 2005')

This SQL query is then executed against the database using the Connection object's Execute() method. You'll see that we don't insert any value for the OrderNo field, and the reason for this is it's an autonumber field. The autonumber field will generate its own unique number when a new record is inserted. In fact if we tried to set OrderNo to a specific value, Access would complain and throw an error.

Inserting details of the item ordered into the OrderItem table is a little more difficult. We first need to know the OrderNo for the record, which was generated automatically when we inserted the new record into the CustomerOrder table.

The autonumbering of the OrderNo is sequential, so by selecting the highest (Max) OrderNo number for the relevant customer name on today's date in the CustomerOrder table, we can be fairly sure to get the OrderNo just created for this customer. Therefore we build up the following SQL code:

   mySQL = "select Max(OrderNo) AS MaxOrderNo from CustomerOrder where ";
   mySQL = mySQL + " OrderDate = #" + nowDate + "# AND ";
   mySQL = mySQL + " CustomerName = '" + Request.Form("txtCustomerName") + "'";

However, while this is OK for our example, it would not be acceptable for a real online ordering system where there were thousands of concurrent users adding orders. The chance of someone with the same name making an order at the same time is slim, but possible.

The SQL created in mySQL will be something like the following:

select Max(OrderNo) AS MaxOrderNo from CustomerOrder where OrderDate = #6 Jun 2000#
AND CustomerName = 'Paul'

The SQL Max() function returns the highest number in a recordset. The AS MaxOrderNo part of the statement simply gives the value returned by Max(OrderNo) an alias, which allows us to access the value and store it in the variable orderNo. Note also that Access is unusual in that it wants dates delimited by the # character rather than quotes as in JavaScript.

We now run this SQL query using the Execute() method of the Connection object, storing the resulting Recordset object in the variable adoRecordset. In the following line we extract the value that we require, and store it in the variable orderNo. Finally, we close the Recordset object and set the variable adoRecordset to null, freeing up the memory space.

   adoRecordset = adoConnection.Execute(mySQL);
   orderNo = adoRecordset("MaxOrderNo").Value;
   adoRecordset.Close();
   adoRecordset = null;

Finally, we add the details of the item ordered to the OrderItem table. We first build our SQL statement.

   var mySQL = "insert into OrderItem (OrderNo, StockId, QtyOrdered)";
   mySQL = mySQL + " values (" + orderNo + ",";
   mySQL = mySQL + Request.Form("txtStockId") + ",";
   mySQL = mySQL + "1)";

The SQL that we build will resemble the following:

insert into OrderItem (OrderNo, StockId, QtyOrdered) values (20,5,1)

We then run the query against the database using the Execute() method. No Recordset object is returned because we are inserting data.

   adoConnection.Execute(mySQL);

Finally, we close the database connection and allow the Connection object to be released from memory.

   adoConnection.Close();
   adoConnection = null;
   Response.Write("<h2><center>Your order was completed successfully" +
      "</center></h2>");

We've also added a Response.Write() to send a message informing the user of the successful order.

Although our example has many limitations, it does show the basics of how online ordering might work. Clearly a lot more needs to be added to make it a robust and complete system. At the moment we allow only one instance of one item to be ordered. Also we take no payment details, so this would be the biggest loss making e-shop ever and that's saying something! Finally, we don't even deduct the item purchased from our stock.

It's time now to return one last time to the trivia quiz.