Before we start coding, let's take a look at the finished product. This is how the results will look to the client that requests the data from our server. Here is the form that the client will see after they submit the Publisher's ID (using the value "4") to the server:
Each of the titles from the Publisher with the PubID submitted will be displayed.
All right, now let's now move on to building the server code. We can cut and paste most of the code from the Wrox.asp previous example for the Results.asp form. In this example, we will grab the number that the user requested and retrieve the appropriate records. Recall in the request.htm page we just wrote, the user enters a Publisher's ID and submits this.
1. OK, open up your trusty Notepad.exe and create a new file called Results.asp and save it in the \Chapter13 directory. Add this code (you can adapt some of it from Wrox.asp):
<HTML>
<HEAD>
<TITLE>Database Programming with Visual Basic 6.0</TITLE>
</HEAD>
<CENTER>
<H1><FONT size=4>Requesting Publisher's Titles</H1></FONT>
<H2>Database Programming with Visual Basic 6.0</H2><BR>
<%
dim myConnection
dim rsTitleList
dim connectString
dim sqlString
dim requestPubID
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\begdb\biblio.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
Set rsTitleList = Server.CreateObject("ADODB.Recordset")
myConnection.Open connectString
requestPubID = Request.Form("PubID")
sqlString = "Select * From titles WHERE PubID = " & requestPubID
Set RSTitleList = myConnection.Execute(sqlString)
If (RSTitleList.BOF) AND (RSTitleList.EOF) then
Response.Write("Sorry, but Publisher Number " & requestPubID & " was not found.")
ELSE
%>
<TABLE align=center COLSPAN=8 CELLPADDING=5 BORDER=0 WIDTH=200>
<!-- BEGIN column header row -->
<TR>
<TD VALIGN=TOP BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Publisher ID
</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=2>
Title
</FONT>
</TD>
</TR>
<!-- Get Data -->
<% do while not RStitleList.EOF %>
<TR>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=RStitleList("PubID")%>
</font>
</TD>
<TD BGcolor ="f7efde" align=center>
<font style ="arial narrow" size=2>
<%=RSTitleList("Title") %>
</font>
</TD>
</TR>
<% RSTitleList.MoveNext%>
<%loop %><!-- Next Row -->
</TABLE>
</center>
</BODY>
<% End if %>
</HTML>
<HTML>
2. Be sure to save the file. Next, load up the request.asp form in your browser. Enter a publisher's id, such as 4, and this form will be called. It will retreive and display all of the titles for that publisher.
We are familiar with most of this code from the earlier Wrox.asp example. So let's just concentrate on what is different.
The main difference is that we are getting data from the requesting form. We use the Request.Form() to retrieve the value(s) from the form elements posted to the HTTP request. To use this, we use this format:
Request.Form(parameter)
Here the parameter is the name of the item on the form we want to retrieve. You can also get the count of the items in the collection. But in our example, we are just retrieving a value from the name of the field ("PubID") in the form's collection. Remember in the request.htm form we gave the text box used to get user input the name "PubID"? Well, that name is now accessible as a member of the requesting form's collection (these are built-in features of the ASP object model.) So we just ask for the value of the requesting form's text box named "PubID" and assign the result to our local variable requestPubID. This variable now holds the number of the publisher the user requested:
requestPubID = Request.Form("PubID")Many web programmers create hidden fields that hold information and pass this information back to the server. So if you ever need to get any information for the client, you can simply place the value in a text box and make that hidden. This way you can get much more information than the client sees. This keeps for a simple, clean requesting form.
In our example, we are taking the user's entry for the text field "PubID" on the request.htm form. We then assign it to a variable, requestPubID. Once we have the value, we simply embed it into a standard SQL statement. Once the string is assigned to the variable sqlString, we again use the Execute method of the ADO connection and assign the results of the recordset to RSTitleList.
sqlString = "Select * From titles WHERE PubID = " & requestPubID
Set RSTitleList = myConnection.Execute(sqlString)
The only eventuality we must cater for is that the user puts in a number that is not a valid Publisher ID. This is easy to check. If the recordset's .BOF and .EOF is true, we know that no records were returned. If that is the case, we simply inform the requester that the publisher number was not found. And as this is all done within VBScript, it is included within the % tags:
If (RSTitleList.BOF) AND (RSTitleList.EOF) then
Response.Write("Sorry, but Publisher Number " & requestPubID & " was not found.")
ELSE
%>
We covered displaying the output, if there is indeed output, in the earlier example. The only new element is the <% END IF %> line. This permits us to either display the message that there were no records, or display all of the records that were found:
</BODY>
<% END IF %>
</HTML>So now, if the user enters a bogus ID number, such as 4444, they will be shown the following message:
Depending on how your browser security is set up, you might see the following Security Alert:
This just tells you that you are sending information over the local Intranet - in our case where both the client and server reside on the same machine. That is because we are POSTING data - or sending it to the server. Usually you are just retrieving HTML pages, but not sending any user information.
So there you have it. We have just built an Internet client-server system! We allowed anyone anywhere to access your server, ask for records on a publisher, and then displayed the results using VBScript. I hope you realize just how powerful this principle is. In just a single chapter, we build an Internet client-server system permitting database access. Powerful applications beckon.