DataBase results Error continued

Art - Lost in databases · Sep 10, 2004

What I dont understand is that my query and output uses
the exact field names that exist in the SQL table.

So my query still looks like this and I dont see any
differences. I have never had this problem before with
other ASP pages using DRW, even using more complex queries
and filed names not the same

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a Frontpage
Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Company</title>
</head>

<body>

<form BOTID="0" METHOD="POST" ACTION="fax-test.asp">
<table BORDER="0">
<tr>
<td>Zip</td>
<td><input TYPE="TEXT" NAME="Zip" VALUE="<%=Request
("Zip")%>" size="20"></td>
</tr>
</table>
 
<input TYPE="Submit"><input TYPE="Reset"><!--webbot
bot="SaveAsASP" clientside suggestedext="asp" preview=" " -
->
 
</form>
<table width="100%" border="1">
<thead>
<tr>
<td>ID</td>
<td>Company</td>
<td>Location</td>
<td>Address</td>
<td>City</td>
<td>State</td>
<td>Zip</td>
<td>Telephone</td>
<td>Facsimile</td>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-
columntypes="3,200,200,200,200,200,200,200,200" s-
dataconnection="faxfinder" b-tableformat="TRUE" b-
menuformat="FALSE" s-menuchoice s-menuvalue b-
tableborder="TRUE" b-tableexpand="TRUE" b-
tableheader="TRUE" b-listlabels="TRUE" b-
listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-
recordsource="tblFaxList" s-
displaycolumns="ID,Company,Location,Address,City,State,Zip,
Telephone,Facsimile" s-criteria="[Zip] EQ {Zip} +" s-order
s-sql="SELECT * FROM tblFaxList WHERE (Zip = '::Zip::')"
b-procedure="FALSE" clientside suggestedext="asp" s-
defaultfields="Zip=00000" s-norecordsfound="No records
returned." i-maxrecords="0" i-groupsize="0" botid="0" u-
dblib="_fpclass/fpdblib.inc" u-
dbrgn1="_fpclass/fpdbrgn1.inc" u-
dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
preview="<tr><td colspan=64
bgcolor="#FFFF00" align="left"
width="100%">This is the start of a
Database Results
region.</td></tr>" startspan --

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color:
black;'>The Database Results component on this page is
unable to display database content. The page must have a
filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM tblFaxList WHERE (Zip = '::Zip::')"
fp_sDefault="Zip=00000"
fp_sNoRecords="<tr><td colspan=9 align=left
width=""100%"">No records returned.</td></tr>"
fp_sDataConn="faxfinder"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=9
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="ID" b-tableformat="TRUE" b-
hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat
preview="&lt;&lt;ID&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Company" b-tableformat="TRUE"
b-hashtml="FALSE" b-makelink="FALSE" clientside b-
MenuFormat preview="&lt;&lt;Company&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Location" b-tableformat="TRUE"
b-hashtml="FALSE" b-makelink="FALSE" clientside b-
MenuFormat preview="&lt;&lt;Location&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Address" b-tableformat="TRUE"
b-hashtml="FALSE" b-makelink="FALSE" clientside b-
MenuFormat preview="&lt;&lt;Address&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="City" b-tableformat="TRUE" b-
hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat
preview="&lt;&lt;City&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="State" b-tableformat="TRUE" b-
hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat
preview="&lt;&lt;State&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Zip" b-tableformat="TRUE" b-
hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat
preview="&lt;&lt;Zip&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Telephone" b-
tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE"
clientside b-MenuFormat preview="&lt;&lt;Telephone&gt;&gt;"

<td>
<!--webbot bot="DatabaseResultColumn" s-
columnnames="ID,Company,Location,Address,City,State,Zip,Tel
ephone,Facsimile" s-column="Facsimile" b-
tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE"
clientside b-MenuFormat preview="&lt;&lt;Facsimile&gt;&gt;"

</tr>

</tbody>
</table>

</body>

</html>

Subject: Database Results Error
From: "Nicholas" <[email protected]>
Sent: 9/9/2004 9:51:32 AM

What it boils down to is that FP uses only one SQL
statement to put results into the table created by DRW.
If you try to use different columns, it tries to write an
overcomplicated SQL statement that doesn't work.

The stuff that displays in the FP DRW and in the Normal
view are contained in the grayed out "DatabaseRegionStart"
area in the HTML view.

s-criteria="[Zip] EQ {Zip} +" s-order
s-sql="SELECT * FROM Results WHERE (Zip = '::Zip::')
ORDER BY Company"
b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="Zip="

The actual query that runs is in the ASP code below that.

fp_sQry="SELECT * FROM Results WHERE (Zip = '::ZIP::')
ORDER BY Company"
fp_sDefault="Zip="

Whatever is in s-sql and s-DefaultFields is copied into
corresponding ASP variables when you save the page. Try
setting it up exactly as above.

Or, try this:

s-criteria
s-order="[Zip] +"
b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields

fp_sQry="SELECT * FROM Results WHERE (Zip = '::ZIP::')
ORDER BY Company"
fp_sDefault=""

On one page, I ended up leaving all of the above blank and
writing a custom ASP script to build the SQL statement and
put it into the fp_sQry variable. The trick with that,
though, is that FP gives you an error message and inserts
fp_sQry="" when you save it. You have to open it in
Notepad and delete the fp_sQry="" before publishing.

Another approach is don't use the DRW at all. Use
ASP/VBscript to query and write the results.

..

Jim Buyens · Sep 10, 2004

I'm not entirely sure what's gone on with this so far; it
would've helped if you'd posted to the original thread.

However, the database field names are the only names the
database results wizard knows, and so those are the names
it uses for column headings.

You can overtype the headings after the DRW finishes, but
if you rerun the wizard, your hand-typed heading names
will be lost.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

I am trying to understand this code...	3	Jul 26, 2008
any ideas why I can update one table in my database but not the other?	2	Sep 22, 2006
Database Results Error	1	Sep 9, 2004
Please help, FP generated code produces a "Too few parameters. Expected1" error!	7	Jun 6, 2005
@ my wits end	1	Sep 12, 2006
Problem with form validation	1	Jul 19, 2004
Drop down box query no.2	1	Dec 11, 2004
FP Database help needed...	6	Oct 18, 2005

DataBase results Error continued

Art - Lost in databases

Jim Buyens

Ask a Question

Similar Threads