14.2.5- Using the User's Choices to Customize the Page

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 14. Advanced Data Handling Techniques.

Not tagged.

<< 14.2.4- Using SQL Commands to Insert and Delete Data

14.3.0- Manipulating Data in your non-database Data Store >>

Using the User's Choices to Customize the Page

You probably noticed that in some of the ADO examples you've seen over the last three chapters, the requests for data were hard-coded into the ASP code – and thus the user had no control over them. This isn't really a great technique for interactive web sites – we want to give the user a chance to decide for themselves what data they want!

One of the great things about the ASP/ADO combination is that we can use it to offer the user as little or as much control of the page's content as we like. In this example we'll write a page that displays data from the AllMovies table – but allows the user to decide exactly what data they want to see. When they've submitted their choices, we'll use them to build up a SQL SELECT statement, and thus create a table of their records.

The user interface in this example is blunt and direct. There's no subtlety about it – it just asks the user to select the name of a director (using a text box) and one or more field names (using a set of checkboxes). However, when you design your own pages, you don't have to be so rough-and-ready: you can design a more attractive, easy-to-use interface that still offers choices to the user. Underneath, you can submit those choices using much the same technique we see here.

Try It Out – A Customized Recordset

1. We're using the AllMovies table of the Movies database and the strConnect connection string again, so you'll need the DataStore.asp SSI file and either Movie2000.mdb or Movie2000.mdf as described in Chapter 12 .

2. We'll also be using the RecToTable.asp SSI file from Chapter 13 (recall that this file contains a procedure called RecToTable(), which accepts a single parameter (an ADO Recordset object) and uses it's contents to generate an HTML table of data). So make sure that RecToTable.asp is also present in your \inetpub\wwwroot\BegASPFiles folder.

3. In your editor, create a new file (which we'll call FindByDirector.asp) and add the following code:

Option Explicit

Dim strConnect

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>Find Director</TITLE>

</HEAD>

<BODY>

Dim strSQL ' SQL String

Dim objRS, objField ' ADO Recordset and Field objects

Dim intCount ' number of fields selected

Dim vbQuote ' quote character

vbQuote = Chr(34)

Response.Write "<H2>Find Movies by Director</H2>"

' Check whether the user has checked any boxes to select some fields.

' If so, we'll display a table of data

If Request.Form("Field").Count > 0 Then

' Find out which fields are to be selected

strSQL = ""

For intCount = 1 to Request.Form("Field").Count

strSQL = strSQL & Request.Form("Field")(intCount) & ", "

' Strip the trailing comma and space (added in the loop) from end of strSQL

strSQL = Left(strSQL, Len(strSQL) - 2)

' Add the SELECT command and the FROM criteria

strSQL = "SELECT " & strSQL & " FROM AllMovies"

' If user requested a particular director, add a WHERE clause

' (otherwise they'll get data for all directors)

If Request.Form("Director") <> "" Then

strSQL = strSQL & " WHERE Director LIKE '%" & Request.Form("Director") & "%'"

End If

' Create the recordset

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

objRS.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

' Write a table of the recordset

Response.Write RecToTable (objRS)

' Clean up

objRS.Close

Set objRS = Nothing

End If

Enter the Director to find:

Please select which fields you would like:<BR>

' Create a recordset on the AllMovies table

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

objRS.Open "AllMovies", strConnect, _

adOpenForwardOnly, adLockReadOnly, adCmdTable

' Create a checkbox in the form for each field in the recordset

For Each objField in objRS.Fields

Response.Write "<INPUT TYPE=CHECKBOX NAME=" & vbQuote & "Field" & vbQuote & _

" VALUE=" & vbQuote & objField.Name & vbQuote & ">" & _

objField.Name

objRS.Close ' clean up

Set objRS = Nothing

</FORM>

</BODY>

</HTML>

4. Save this file as FindByDirector.asp. Now call up this page in your browser. Here's what it should look like; as you can see it shows the <FORM> part of the page, with a text box (for a director's name) and a set of checkboxes (one for each field in the AllMovies table). I've added the name of a famous director and checked a few checkboxes ready to submit.

Chapter14_image014

5. Type the name of a film director, and select a few checkboxes, and then click the Find button. You should get something like this:

Chapter14_image015

This shows the table of data requested by the user; here, you can see that I selected the director Woody Allen and six of the available fields, and the table shows all that data. (Below it, we've displayed the <FORM> again so that the user can submit another query).

Pretty cool eh? And pretty simple too. Let's examine it a little more closely.

How It Works

This page is clearly in two parts. At the bottom of the page is an HTML form that uses some ASP to prompt the user to tell us what data he wants to see. Before that, at the top of the page, is a block of ASP that examines the most recently submitted request, and produces a table that displays the results of that request. The table at the top of the page is generated within a big If…Then…End If statement, which checks whether or not there is a request to deal with (if there's no request, then we don't bother generating a table). So the outline of what we are doing is this:

Check the Request.Form collection to see whether the user selected any checkboxes

If <there is at least one checkbox selected> Then

Check which checkboxes were selected, and build a SQL command string

If <a particular director was requested> Then

Append director details for SQL command string

End If

Use SQL command string to create a recordset

Use recordset to display the table

End If

Write empty textbox to browser

Use recordset to write checkboxes to browser

Allow user to Submit request

Let's look at how we capture the user's request first – that's the second half of the page. The HTML form will submit data to a refreshed version of FindByDirector.asp, passing it to the Request.Form collection:

...

</FORM>

Inside the form, the data collection is in two parts. First, we want the name of a director, so we create a text box for this purpose:

Enter the Director to find:

Second, we want to know which fields the user wants to know about. For this, we create a checkbox for each field in the AllMovies table. One way to create this list is to query the AllMovies table directly from the database, and iterate through the Fields collection of the resulting recordset, making one checkbox for each field:

Please select which fields you would like:<BR>

' Create a recordset on the AllMovies table

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

objRS.Open "AllMovies", strConnect, _

adOpenForwardOnly, adLockReadOnly, adCmdTable

' Create a checkbox in the form for each field in the recordset

For Each objField in objRS.Fields

Response.Write "<INPUT TYPE=CHECKBOX NAME=" & vbQuote & "Field" & vbQuote & _

" VALUE=" & vbQuote & objField.Name & vbQuote & ">" & _

objField.Name

objRS.Close ' clean up

Set objRS = Nothing

As you can see, that involves creating a recordset, populating it with data from the database, using its Fields collection, and then cleaning up the Recordset object when we've finished. This will give us a set of checkboxes like this:

<INPUT TYPE=CHECKBOX NAME="Field" VALUE="TitleID">TitleID

<INPUT TYPE=CHECKBOX NAME="Field" VALUE="Title">Title

We mustn't forget to add a SUBMIT button to the form (and also a RESET button, for good measure):

The user types text into the text box, checks a few checkboxes and then clicks the Find button. That causes the page to be reloaded, with the requested data passed to the Request.Form collection.

That deals with the second part of the page. Now to the first part – the request handling and table display. Whenever the page is loaded (or reloaded), the first thing we need to know is whether we have a request to deal with. To do that, we'll check to see whether there are any values in Request.Form("Field"). If so, then it means the user clicked at least one checkbox; if not, it means that no checkboxes were checked (or that the page is being requested for the first time):

If Request.Form("Field").Count > 0 Then

Assuming this condition returns True, then we have a request to deal with, and so we execute the contents of the If…Then…End If block. Before we build the recordset, we'll need to analyze the request to find out what the user asked for – we'll use this to build a SQL SELECT command, which we'll store in a string called strSQL. First, iterate through the contents of Request.Form("Field"), and add the substring "field_name," to the strSQL string for each field that was chosen:

' Find out which fields are to be selected

strSQL = ""

For intCount = 1 to Request.Form("Field").Count

strSQL = strSQL & Request.Form("Field")(intCount) & ", "

Next, remove the final comma and space at the end of the string we just generated:

strSQL = Left(strSQL, Len(strSQL) - 2)

Next, append SELECT at the beginning and FROM AllMovies at the end:

strSQL = "SELECT " & strSQL & " FROM AllMovies"

Now the string in strSQL is starting to look like a proper SELECT command! Next, we'll check whether the user asked for a particular director, and use that criterion as a WHERE clause:

If Request.Form("Director") <> "" Then

strSQL = strSQL & " WHERE Director LIKE '%" & Request.Form("Director") & "%'"

End If

Now use the completed SQL command to query the database, and produce a recordset:

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

objRS.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

Now we can display the contents of the recordset on screen. We'll use a shortcut here – by borrowing the RecToTable() routine that we wrote in Chapter 13 . It is conveniently contained in the RecToTable.asp SSI file, which we included at the top of the page using a #INCLUDE directive. All we need to do is call the RecToTable() function, passing the name of our recordset as parameter:

Response.Write RecToTable(objRS)

The RecToTable() routine returns a string containing all the HTML for the table, so we just Response.Write it to the browser. Then we just clean up the recordset:

objRS.Close

Set objRS = Nothing

End If

<< 14.2.4- Using SQL Commands to Insert and Delete Data

Chapter14

14.3.0- Manipulating Data in your non-database Data Store >>