Stephen Travis' FrontPage 2000 'Tweaks'

     Database Results Wizard
          Pagination
          Line Breaks
          Passing Query Values
               Hidden Form Fields
               Link
               Page Redirection
               DRW Script Modifications
     Save to database
          Autonumber
          Custom Error Message

The database results wizard can paginate the query results over several pages. However, when you enter a new query after paging through the current result set, the new query results are displayed starting on the page that you left off with in your previous query. The following changes to the script eliminates this problem;

Tools... Web Settings... Advanced... Show documents in hidden directories.
Open _fpclass/fpdbrgn1.inc
Find: fp_sFormKey = fp_sFormName & "_PagingMove"
Add the following after the above line;

' Clear the session variable if this is a new query.
If Request.Form(fp_sFormKey) = "" Then Session(fp_sEnvKey) = ""

Line Breaks

When database results fields containing carriage returns are displayed on a page, there are no line breaks in the HTML, resulting in one continuous line of text. The following script change will add line breaks to the HTML of every field on every page in your web;

Tools... Web Settings... Advanced... Show documents in hidden directories.
Open _fpclass/fpdblib.inc
Find: Function FP_FieldVal(rs, fldname)
Find: FP_FieldVal = Server.HTMLEncode(FP_Field(rs, fldname))
Change the above line to;

FP_FieldVal = Replace(Server.HTMLEncode(FP_Field(rs, fldname)), VbCrLf, "<br/>")

The other option is to change the result field on the DRW page itself;

Delete the database results field.
Insert... Advanced... HTML... and add the following (replacing databasefieldname with your database field name);

<%=Replace(fp_rs("databasefieldname"), VbCrLf, "<br/")%>
or
<%=Replace(FP_FieldVal(fp_rs,"databasefieldname"), VbCrLf, "<br/")%>

Passing Query Values

The FrontPage Database Results Wizard (DRW) only processes form fields passed in the search form (Request.Form) or fields passed in the querystring (Request.QueryString). It does so by substituting fields in the SQL statement. For example, SELECT * FROM TableName WHERE ID = ::id::, substitutes the form field id value in the SQL statement. There are a number of methods that can be used to pass other types of query values to the DRW.

Hidden Form Fields

If the user is using a form to do the query, the variable can be passed in a hidden form field on an ASP form page. For example;;

<input type="hidden" name="uid" value="<%=session("uid")%>">

Link

If the user is linking to the query from another page, the variable can be passed in a link. For example;

<a href="edit.asp?uid=<%=session("uid")%>">Edit My Information</a>

Page Redirection

If the user is going to navigate directly to the page, the variable needs to be added to the querystring by redirecting back to the same page. For example;

%
If Request("uid") = "" Then Response.Redirect Request.ServerVariables("SCRIPT_NAME") & "?uid=" & Session("uid")
%>
<html>

DRW Script Modifications

You can also modify the script that the the DRW uses to create the query to process values other than Request.Form or Request.QuerySting. You can also modify the same script to use a Session object, Application Object or Request Objects (Form, QueryString, ServerVariables, Cookies, Certificates), as follows;

Tools... Web Settings... Advanced...Show documents in hidden directories.
Open _fpclass/fpdbrgn1.inc
Find the line of code in the file about 1/3rd the way down, in the 'replace any input parameters in query string' section;

if len(fp_sValue) = 0 then fp_sValue = Request.QueryString(fp_sField)
Add one (or all) of these lines immediately below;

if len(fp_sValue) = 0 then fp_sValue = Session(fp_sField)
if len(fp_sValue) = 0 then fp_sValue = Application(fp_sField)
if len(fp_sValue) = 0 then fp_sValue = Request(fp_sField)
Use a custom DRW to query on the object by it's name. For example, use a Session Login ID (e.g. customernumber) or the Windows Logon Username (e.g. LOGON_USER) in a query;

SELECT * FROM TableName WHERE CustomerNumber = '::customernumber::'
SELECT * FROM TableName WHERE Username = '::LOGON_USER::'

Save to database

Insert... Form...

Autonumber

You can use the FrontPage 2000 'Save to database' wizard to add records to an Access database. However, the autonumber value of the new record is not available as a confirmation field. The following script change will make the autonumber available as a confirmation field called 'ID'.

Tools... Web Settings... Advanced...Show documents in hidden directories.
Open _fpclass/fpdbform.inc
Find the FP_DumpError Function at the end of the file.
Immediately before the End Function statement, add (copy and paste) the following ;

If strMsg = "Cannot update the database" Then
arFormFields0(UBound(arFormFields0)) = "ID"
arFormValues0(UBound(arFormValues0)) = fp_rs(0)
End If

NOTE: The fp_rs(0) above assumes that your autonumber field is the first field (0) in the table.

The autonumber field is then available on your confirmation page either as a confirmation field or as one of FrontPage's saved dictionary items.

Insert... Component... Confirmation Field... ID

<p>Your New Customer ID is <%=FP_SavedFields.Item("ID")%></p>

Custom Error Message

When you use database constraints (such as duplicate values), the FrontPage 'Save to database' component will generate a default error message when the constraint is violated. You can add your own error message by modifying the script that FrontPage uses to add records to the database. The following script change will capture specific errors and generate a custom error message while preserving the default error message for other errors;

Tools... Web Settings... Advanced...Show documents in hidden directories.
Open _fpclass/fpdbform.inc
Find the FP_DumpError Function at the end of the file.
Immediately after the Else statement, add (copy and paste) the following;

Select Case Err.Number
Case -2147217873
Response.Write "Sorry, this item already exists in the database. Hit the <Back> button of your browser and try again."
Case Else
Locate the next End If statement and add the following statement immediately before it;

End Select

Back to Stephen Travis' Thingumajig