Venturing into Web development causes some questions to surface. One common question in development shops is, "We know how to implement a task in the client/server environment, but what is the best way to implement it on the Web?" If you can come to that point when beginning to use a new technology, the battle is almost over. Logic is logic is logic. My first programming language was FORTRAN. The class gave me fits. Since that point, learning a new language is easier because it has become a matter of learning new syntax. That's why the transition to developing on the Web has gone great.
The premise behind development is displaying data to the users, then giving them the ability to add, update, or delete the data. When displaying data to users, you must give them the option to navigate through the data. The invention of the Super Highway has caused developers to be more careful when planning how users will navigate through data on their Web sites. Implementing the wrong solution may bring a Web server to its knees, in turn causing people not to visit your site and in turn going out of business. Therefore, data navigation and sorting is mission critical. A client/server application with about 20 users is different than having thousands of users needing a connection to your database. A few issues that need to be addressed during the design phase of a project (not the development phase!) are disconnected record sets, COM objects, transaction and thread-control, and Active Server Page (ASP) objects.
Displaying Data to End Users
The concept of "drilling-down to data" is used in each application developed. Whether the development team uses that phrase or not, the concept is always addressed. For example, if you have a customer table with 25,000 records, how will you give your users the option to add, update, or delete customers? Are you going to have a record set created by SELECT * FROM TBL_CUSTOMER to populate your graphical user interface (GUI)? Hopefully that is not the case. First you want to show the user a subset of data from the respective table(s). For example, customer_id, customer_name, customer_address, and customer_phone. Second, the user will be given the option to select a customer record and proceed to another form or page where your SELECT statement will include a WHERE clause to populate a detail form or page.
Three Core Elements
There are three core elements in my (client/server or Web-based) applications:
A) Search Screen - This gives the user the option to search on selected criteria. End users will determine the fields they would like to be searchable.
B) Results Screen - It may or may not be on the same form or page where the search criterion is located. Once the users decide the criteria they want, they may click a button called <SEARCH>. The code will be an SQL statement with a WHERE clause that will narrow down the numbers of records retrieved in the record set. Once those records are retrieved, the data will be displayed to the user. In client/server applications, the results are typically shown on the same form. In Web applications, the results may be shown on the same page or you can redirect the user to another page. These records are displayed by populating a list box, grid, table, or other control. Giving the users the ability to sort the records by clicking on one of the column headers will gain you extra kudos!
In a Web application I just finished, I came across this issue. A record set was retrieved from an SQL Server 7.0 database and the data was displayed in a table format on an ASP. The users wanted the option to click on a column header and re-sort the table. Record-set paging was used to display the data. Record-set paging is a concept that many Web sites implement at the results screen level. My column headers were graphics, not buttons. The code below will show you how to use the REQUEST object to determine which image the user selected.
C) Detail Screen - This is the form or page the user will navigate to after they have selected a specific customer. Adding and updating customer information will be accomplished at this level (deleting may be done at the Results Screen level).
There are good examples of record-set paging to use on your Web site. ActiveX Data Objects (ADO) give you the flexibility to customize how data is shown to the users. I have found that once data is displayed to users, giving them the option to sort the record set by a certain column makes them as happy as "clams at high-tide." Let's plunge into some code.
' *******Begin execution of the ASP ***********
Sub Main ()
Call OpenClientConnection
Call OpenClientRecordSet
Call CloseClientDatabase
End Sub
*****************************************
'**************** Establish the Connection to Our Database **********************
Sub OpenClientConnection
'Instantiate (create) a connection object
set cn = Server.CreateObject("ADODB.Connection")
'Create the connection string to the db
cn.ConnectionString = "driver={SQL Server};server=ServerName;uid=clientuser;pwd=kcchiefs;database=client_database"
'Open connection
cn.Open
End Sub
************************************************************************
' *******************Open Record Set *************************************
Sub OpenFacilityRecordSet
Dim strSQL
'Instantiate a record set object
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient
rs.CursorType = 3 'adOpenStatic
rs.ActiveConnection = cn
strSQL = "SELECT CustomerID, CustomerName, CustomerAuditDate, " &
"CustomerConstructionDate, CustomerSiteDate " &
"FROM tbl_Customer "
The above SQL statement will return a record set containing the following fields:
* Customer ID
* Customer Name
* Customer Audit Date
* Customer Construction Date
* Customer Site Date
An Active Server Page was created to display this information to the user via an HTML table. The HTML that creates the table is shown below. The column headers could be buttons or HREFs, but we decided to have our graphic artist on staff create pretty images. Since the users wanted the option to re-sort the table by clicking on a column header, our ORDER BY clause will be offered to the user.
Using Images vs. Buttons as Column Headers
If we used buttons as column headers, we could use the following code:
IF Request ("CustomerID") Then
intSort = "CustomerID"
ElseIF Request ("CustomerName") Then
intSort = "CustomerName"
.
.
.
End If
Determining Which Image The End-User Selected
But when using images, that cannot be done. So here is the code to determine which image the user selected.
If Request.Form("CustomerID.X") Then
'did the user want to sort by customer id?
intSort = "CustomerID"
ElseIf Request.Form("CustomerName.X") Then
'did the user want to sort by customer name?
intSort = "CustomerName"
ElseIf Request.Form("AuditFinish.X") Then
'did the user want to sort by audit date?
intSort = "AuditFinish"
ElseIf Request.Form("ConstructFinish.X") Then
'did the user want to sort by construction date?
intSort = "ConstructFinish"
ElseIf Request.Form("SiteFinish.X") Then
'did the user want to sort by site date?
intSort = "SiteFinish"
Else
'the default order-by statement will be customer id
intSort = "CustomerID"
End If
'ORDER BY clause needs to be determined
Select Case intSort
Case "CustomerID"
strOrderBy = " CustomerID ASC"
Case "CustomerName"
strOrderBy = " CustomerName ASC"
Case "ConstructFinish"
strOrderBy = " CustomerConstructionDate ASC"
Case "SiteFinish"
strOrderBy = " CustomerSiteDate ASC"
Case Else
strOrderBy = " CustomerID ASC"
End Select
'add the order-by clause onto the SQL statement
strSQL = strSQL & " ORDER BY " & strOrderBy
'Open the recordset
rs.Open strSQL
'if no records were found, display a message to the user and do cleanup
If rs.EOF and rs.BOF Then
Response.Write "Sorry, no records were found matching that criteria."
Call CloseClientDatabase
Response.End
End If
'Set the page-size and cache-size properties of the record set. Also, set the
'intPageCount and intRecordCount variables
rs.PageSize = 15
rs.CacheSize = rs.PageSize
intPageCount = rs.PageCount
intRecordCount = rs.RecordCount
This is another part of code where I needed to determine which image the user chose. You will see these images on quite a number of Web sites. For example << < 1 of 15 > >>. Using buttons makes this job easy, but if you want graphics, some adjustments need to be made.
, , ,
'did the user click the first button
If Request.Form("first_button.X") Then
strButtonClicked = "FirstButton"
End If
'did the user click the previous button
If Request.Form("previous_button.X") Then
strButtonClicked = "PreviousButton"
End If
'did the user click the next button
If Request.Form("next_button.X") Then
strButtonClicked = "NextButton"
End If
'did the user click the last button
If Request.Form("last_button.X") Then
strButtonClicked = "LastButton"
End If
'depending on the image the user clicked, that will determine where to set our paging variables.
Select Case strButtonClicked
Case "FirstButton"
intPage = 1
intSort = Request.Form("intsort")
Case "PreviousButton"
intPage = Request("intpage") - 1
If intPage < 1 Then intPage = 1
intSort = Request.Form("intsort")
Case "NextButton"
intPage = Request("intpage") + 1
If intPage > intPageCount Then intPage = intPageCount
intSort = Request.Form("intsort")
Case "LastButton"
intPage = intPageCount
Case Else
If Request.Form("intpage") = "" Then
intPage = 1
Else
intPage = Request.Form("intpage")
End If
End Select
'If the recordset is not empty, set the AbsolutePage property and then
'populate the intStart and intFinish variables.
If intRecordCount > 0 Then
rs.AbsolutePage = intPage
intStart = rs.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (rs.PageSize - 1)
End If
End If
End Sub
Now let's look at the HTML:
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Customer Information</title>
</head>
<body Link=Darkslategray VLINK=DarkGoldenRod>
<map NAME="facilityhead_map">
<area SHAPE="RECT" COORDS="351,36,405,62" HREF="Default.asp" ALT="Home">
<area SHAPE="RECT" COORDS="424,37,484,62" HREF="FacilitySearch.asp" ALT="Search">
<area SHAPE="RECT" COORDS="501,36,564,61" HREF="SelectReport.htm" ALT="Reports">
<area SHAPE="RECT" HREF="Default.asp" coords="0,0,0,0">
</map>
<p align="center">
<img border="0" height="79" src="images/facilitiesheading.gif" useMap="#facilityhead_map" width="590">
</p>
<form name="movepage" action="default.asp" method="post">
<table CELLSPACING="0" CELLPADDING="0" BORDER="0" WIDTH="100%" id="TABLE1">
<tr BGCOLOR="white" VALIGN="Left">
<td width="25%" align="left"><b>Number of Clients Found: </b></td>
<td width="30%" align="left"><b><%=intRecordCount%> </b></td>
<td width="8%" align="right">
'This is where the navigation images are created. 'Their input type is not "SUBMIT" so we cannot grab the value 'property from the Request Object.
<input TYPE="image" name="first_button" src="images/FirstPage.gif" WIDTH="41" HEIGHT="24" border=0>
<input TYPE="image" name="previous_button" src="images/PrevPage.gif" WIDTH="26" HEIGHT="24" border=0>
</td>
<td width="12%" align="center">
<%=intStart & " through " & intFinish%>
</td>
<td width="8%" align="right">
<input TYPE="image" name="next_button" src="images/NextPage.gif" WIDTH="26" HEIGHT="24" border=0>
<input TYPE="image" name="last_button" src="images/LastPage.gif" WIDTH="41" HEIGHT="24" border=0>
</td>
</tr>
</table>
'Hidden fields are used for the request object.
<input TYPE="HIDDEN" Name="intpage" VALUE="<%=intpage%>">
<input TYPE="HIDDEN" Name="txtCustomerID" VALUE="<%=Request("txtSiteID")%>">
<input TYPE="HIDDEN" Name="txtAuditFinishDate" VALUE="<%=Request("txtAuditFinishDate")%>">
<input TYPE="HIDDEN" Name="txtConstFinishDate" VALUE="<%=Request("txtConstFinishDate")%>">
<input TYPE="HIDDEN" Name="txtSiteCompleteDate" VALUE="<%=Request("txtSiteCompleteDate")%>">
<input TYPE="HIDDEN" Name="cboCustomer" VALUE="<%=Request("cboCustomer")%>">
<input TYPE="HIDDEN" Name="intsort" VALUE="<%=Request("sort")%>">
<table CELLSPACING="0" CELLPADDING="0" BORDER="0" WIDTH="100%" id="TABLE1">
<tr BGCOLOR="white" VALIGN="MIDDLE">
The column headings for the table were graphics too.
<td width="9%" ALIGN="left">
<input TYPE="image" name="CustomerID" src="images/SortSiteID.gif" border=0 WIDTH="96%" HEIGHT="27"></td>
<td width="13%" ALIGN="left">
<input TYPE="image" name="CustomerName" src="images/SortContractor.gif" border=0 WIDTH="98%" HEIGHT="26"></td>
<td width="11%" ALIGN="left">
<input TYPE="image" name="AuditFinish" src="images/SortAuditFinDate.gif" border=0 WIDTH="98%" HEIGHT="26"></td>
<td width="14%" ALIGN="left">
<input TYPE="image" name="ConstructFinish" src="images/SortConstFinDate.gif" border=0 WIDTH="98%" HEIGHT="26"></td>
<td width="10%" ALIGN="left">
<input TYPE="image" name="SiteFinish" src="images/SortSiteFinDate.gif" border=0 WIDTH="98%" HEIGHT="26"></td>
</tr>
</table>
</form>
'Now that the column headings are created, populate the table by looping through the record set according to the record set 'paging variables.
As with any development language, there are many ways to accomplish the same task. When doing Web development, this is pretty noticeable. Record-set paging to display data to users can be done using hidden fields, query strings, HREFs, etc. How the users want to navigate through the record set will determine the approach taken. They may or may not want to use record-set paging. If they do, they will need to inform the development team about their preferences. Here are some examples:
* << < Page 1 of 15 > >>
* << Page 1 of 15 >>
* Previous Record 5 of 16,128 Next
* Previous 10 Next 10
* etc....
If you want to use graphics on your Web sites instead of buttons for navigation and/or column headings, you may want to use the Request.Form("ImageName.X") property to determine which image the user selected. If they did select the respective image, the Request.Form("ImageName.X") property will be returned as TRUE. Keep the keyboards clicking and don't forget to document!
About the Author
Sean C. Barker is a Microsoft Certified Professional who is a member of the TechSolutions consulting group within Burns & McDonnell Engineering/Architectural Company in Kansas City, Missouri. His areas of expertise are Visual Basic, Visual InterDev, SQL Server, and MS Access. Please visit the TechSolutions Web site at techsolutions.burnsmcd.com or send Sean an e-mail at sbarker@burnsmcd.com.
Where the collection of specific feedback is necessary, the mailto attribute just won't cut it. With ASP and CDONTS, Web site owners can obtain specialized information from everyone -- even those without e-mail clients. [Read This Article][Top]
In this article Christophe Berg show you how to build our own iSql with ASP and ADO 2.0. Using ASP you can build a database administration page that will allow you to modify your database from your browser. It’s both easy to implement and very useful, and it’s a good way to see how to work on a database with ASP. [Read This Article][Top]
In this issue 15 Seconds implements a catalog site that is build with Active Server pages and SQL Server. Along with the implementation there is source code and a discussion of the advantages and disadvantages of creating a catalog site that gets its content from a database. Included are pages for displaying products, creating a menu page, category page, and running a search across a database. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
, 
, 
, 
