HELP - Database Wizard not generating correct SQL queries

[John]

I am using Access 2003, FrontPage 2003 and IIS V5.0 to publish my Web Site
on a local computer.

I created an Access Data Base and used the Database Results Wizard to
display data.

If I use the database as the data source in the Wizard, it runs fine. If I
use a Query instead, I get the following message

After I publish the site, I keep getting the following message:

Database Results Wizard Error
Description: Syntax error in query. Incomplete query clause.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

It looks like the Database Results Wizard is generating incorrect SQL. If I
write my own SQL, it runs fine.

Any suggestions?

John

 

[Paul]

maybe missing some quotes somewhere? can you view the SQL query to determine
that it's valid/not using reserved words

regards
paul

 

[John]

Since the Database Wizard is generating the SQL, I am not sure how I could
view it.

John

 

[Kathleen Anderson [MVP - FrontPage]]

Switch to "Code View" and look for a statement that begins with:

s-sql="SELECT


--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[John]

Kathleen,

Here is the SQL from the Access Query:

SELECT POST.[Last Name], POST.[First Name], POST.[Room Number],
POST.Address, POST.City, POST.State, POST.Zip, POST.[e-Mail],
POST.Telephone, POST.Cel, POST.Fax, POST.HRID, POST.[Supervisor HRID],
POST.[Org Code], POST.Level
FROM POST
WHERE (((POST.[Last Name]) Like "adams"));



Here is the Database Wizard Generated Statements:

<!--webbot bot="DatabaseRegionStart" s-columnnames="Last Name,First
Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level"
s-columntypes="202,202,202,202,202,202,202,202,202,202,202,3,3,202,202"
s-dataconnection="post" 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="FALSE" s-recordsource="POST Query"
s-displaycolumns="Last Name,First Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level" s-criteria s-order s-sql="SELECT * FROM &quot;POST
Query&quot;" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256"
i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc"
u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc"
tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the
start of a Database Results region. The page must be fetched from a web
server with a web browser to display correctly; the current web is stored on
your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;"
startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>

 

[Kathleen Anderson [MVP - FP]]

John:
Try single quotes around the string:

WHERE (((POST.[Last Name]) Like 'adams'));

See: http://support.microsoft.com/default.aspx?scid=kb;[LN];306430

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

Kathleen,

Here is the SQL from the Access Query:

SELECT POST.[Last Name], POST.[First Name], POST.[Room Number],
POST.Address, POST.City, POST.State, POST.Zip, POST.[e-Mail],
POST.Telephone, POST.Cel, POST.Fax, POST.HRID, POST.[Supervisor HRID],
POST.[Org Code], POST.Level
FROM POST
WHERE (((POST.[Last Name]) Like "adams"));



Here is the Database Wizard Generated Statements:

<!--webbot bot="DatabaseRegionStart" s-columnnames="Last Name,First
Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level"
s-columntypes="202,202,202,202,202,202,202,202,202,202,202,3,3,202,202"
s-dataconnection="post" 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="FALSE" s-recordsource="POST Query"
s-displaycolumns="Last Name,First Name,Room
Number,Address,City,State,Zip,e-Mail,Telephone,Cel,Fax,HRID,Supervisor
HRID,Org Code,Level" s-criteria s-order s-sql="SELECT * FROM
&quot;POST Query&quot;" b-procedure="FALSE" clientside
suggestedext="asp" s-defaultfields s-norecordsfound="No records
returned." i-maxrecords="256" i-groupsize="0" botid="0"
u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This
is the start of a Database Results region. The page must be fetched
from a web server with a web browser to display correctly; the
current web is stored on your local disk or
network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;"
startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>

Switch to "Code View" and look for a statement that begins with:

s-sql="SELECT


--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/