---

Spooky's Databases > Advanced Tips

The FP 2000 DRW is now more customizable than ever. 
It is no longer reliant on web bots and can be easily changed to suit your needs.

Some advanced features can be achieved by using the available tools within FP2000.
Features like:

• displaying images
• displaying URL's
• displaying Email addresses.

Click here to learn more

The rest of this page is a collection of hints and tips that I come across in my never ending quest to make the DRW more useful (other than forcing all of you to learn asp and do it properly ;)

• Alternating line colors for results
• Modifying the 'Next' and 'Previous' form buttons
• File types
• Customizing ad Boolean fields
• Using the FP database connection
• Counting records
• Displaying rows of information
• Sending Email confirmations
• Modify the DRW code The Spooky Diet!
• Getting the ID of a newly saved record
• DRW input Syntax
• Making a list box show "selected" items NEW!
• Other advanced links

Alternating line colors for results
By changing the display code, you are able to better highlight a new line of records by adding color to the table row.

• Locate the first database row <td> tag in HTML view:

<!--webbot bot="DatabaseRegionStart" i-CheckSum="44985" endspan -->
<tr>
<td><!--webbot bot="DatabaseResultColumn" startspan

• Change the <td> tag to read :

<td <%
Dim x, bgcolor
 if x = 1 then
     bgcolor="gray"
     response.write "bgcolor='gray'"
     x=2
Else
    bgcolor="White"
    Response.write "bgcolor='white'"
    x=1
End if %>>

• Locate each new row <td> tag

bot="DatabaseResultColumn" i-CheckSum="30557" endspan -->
</td>
<td><!--webbot bot="DatabaseResultColumn" startspan

• Change this tag to read:

<td bgcolor='<%=bgcolor%>'>

Back to top

Modifying the 'Next' and 'Previous' form buttons
The file we need to modify is fpdbrgn1.inc. The following labels can be renamed so that the form buttons used for navigation show different information

fp_sFirstLabel = " |< "
fp_sPrevLabel = " < "
fp_sNextLabel = " > "
fp_sLastLabel = " >| "
fp_sDashLabel = " -- "

e.g.

fp_sFirstLabel = " Start "
fp_sPrevLabel = " Prev "
fp_sNextLabel = " Next "
fp_sLastLabel = " End "
fp_sDashLabel = " -- "

Back to top

File types
Some of the ASP that does all the work for the DRW, is located in a hidden directory called '_fpclass'

It contains the files of functions:

• fpdblib.inc contains the library functions used by the database features.
• fpdbrgn1.inc contains the ASP code that establishes the database connection, and constructs and executes the query, starting a loop through all database records. Page form buttons reside here.
• fpdbrgn2.inc contains the ASP code that finishes the query loop started by fpdbrgn1.inc and closes the database connection.
• fpdbform.inc contains the ASP code that implements the Save to Database form handler.

Back to top

Customizing ad Boolean fields
Occasionally you want to display custom results from a database field that has yes/no true/false checkboxes in it.
This is an ad Boolean field.
FP only supports True/false by default. Editing the fpdblib.inc file can change all that.

• Find the lines :

Select Case rs(fldname).Type
Case 128, 204, 205

• Between them, add this :
  (add text if you require a different value than check boxes )

Select Case rs(fldname).Type
    Case 11
If lCase(rs(fldname)) = "true" then
FP_Field = "<input type='checkbox' CHECKED>"
Else
FP_Field = "<input type='checkbox'>"
End if
    Case 128, 204, 205

• Now, in FP explorer, right click on the column value in the DRW and choose 'Database column values'. 
• Tick 'Contains HTML'

Remember that any changes are global, and modifications will be overwritten by FrontPage IF you modify a DRW or publish to a server (other than opening live)

Back to top

Using the FP database connection
One day you will get sick or reading all my hints and tips and you will 
<insert subtle hint> LEARN ASP </insert>
A good way to get started is to get some examples and practice.
The first problem most people come across is 
"How do I connect to my database?" 

Never fear, there is an easy way.
With existing DB's in your /fpdb/ directory, open the global.asa file from the root (right click - open with notepad)
Each database in your /fpdb/ directory will have its own connection.
Look for this string in the global.asa file 
(where "Sample" is the name of my existing database connection and fpnwind.mdb the name of my database)

'--Project Data Connection
Application("Sample_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/fpnwind.mdb"

Now, when you want to use this connection, you only have to add  Application("Sample_ConnectionString")  to your script.

eg: MyDSN = Application("Sample_ConnectionString")

Counting Records
You can use the DRW to return a record count as well.
For example, using the northwinds database, at step 2 select custom query.
Enter this query :
SELECT Count(Products.ProductID) AS ProductID FROM Products;
In your edit list at step 3, remove all fields except ProductID.
In step 5, use list / paragraph formatting.
The result should be the number of records in the database.

Formatting output in rows
Here's a way of placing records into rows across the screen rather than just listing them 1 by one down the screen. 

For example, when displaying individual photos like this :

Heres the code!

<!--webbot bot="DatabaseRegionStart" i-CheckSum="53750" endspan -->
<%If x = 0 then %>
<tr>
<%end if%>
<%x=x+1%>
<TD> Normal DRW Code
<% If x < 4 then %>
</td>
<%Else
x = 0%>
</td></tr>
<%end if%>
<!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"

DRW Input syntax
Varying formats are used to query or update information in a database.
It all depends what field type exists in the database as to what format you need to use.

Other Advanced links

• Sort results by column names
• DRW - Update / New / Delete
• www.learnasp.com - Can I tempt you? ;c)
• www.4guysfromrolla.com