Correct syntax for SELECT with asp field?

[Jackie]

I'm stuck on the correct syntax for an access database select statement, if
what I'm trying to do is even possible within a DRW! Can anyone advise,
please?

I'm writing the contents of a form to a database. On the Confirmation page I
need to use the value of one of the fields (numeric) to look up a record on
another table within the same database, so that full details can be
displayed.

I know that I can't actually use the confirmation field itself, but was
hoping to be able to use <% =FP_SavedFields("bookerid") %> in the SQL
statement. I've written it to the confirmation page and know it contains the
value I need.

My HTML code says: fp_sQry="SELECT * FROM bookers where id = <%
=FP_SavedFields(""bookerid"") %>" but I'm getting 'Unterminated string
constant' error with this.

I've tried all sorts of combinations over the last few hours but I'm not
having any luck and would appreciate some help )

Many thanks,
Jackie

(XP Pro and FP2002 with server extensions)

 

[Thomas A. Rowe]

Try

Bookerid = FP_SavedFields("bookerid")

fp_sQry="SELECT * FROM bookers where id = " & Bookerid

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Jackie]

Thomas -

thanks for the reply. I've had a few attempts at this, but with no success
so far.

Nearly everything gets thrown out as invalid syntax when I try to do it
within the FrontPage database results custom query, except for
fp_sQry="SELECT * FROM bookers WHERE bookerid = "" &Bookerid""" which
results in error : Too few parameters. Expected 2. (I didn't type all those
quotes in, most were generated).

I've gone into HTML view, but I can't change the fp_sQry ..... line as
FrontPage changes it back when I try to save.

I've tried to make the changes to the s-sql= ..... line in the webbot
section above, but whatever I type in there comes out differently in the
fp_sQry statement when it's saved.

For example :-

s-sql="SELECT * FROM bookers WHERE bookerid = "&Bookerid
results in
fp_sQry="SELECT * FROM bookers WHERE bookerid = "
which chops off the end of the statement.


s-sql="'SELECT * FROM bookers WHERE bookerid =' &Bookerid"
results in
fp_sQry="'SELECT * FROM bookers WHERE bookerid =' &Bookerid"
which gives Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

I've tried various combinations of single and double quotes but all result
in 'invalid SQL statement' or 'too few parameter' errors.

Have you any idea how I can format the s-sql statement so that it comes out
correctly when it's saved to the fp_sQry line?

Thanks,
Jackie

 

[Thomas A. Rowe]

Personally, I avoid using the FP database component and hand code my ASP,
anyway...

Is the bookerid a number or text field in the database?

If it is a text field, try:

fp_sQry="SELECT * FROM bookers WHERE bookerid = '" & Bookerid & "' "

Otherwise, unless someone else can help, it is time to learn to hand code
which will give you a lot more flexibility in the long run, plus you will
have learn ASP.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Jackie]

Thomas -

Bookerid is a numeric field, it's the primary key, an auto-generated number.

I'll give it a few more tries, then if no-one else can help I guess I'll
have to bite the bullet and hit the books!

Thanks for your help,
Jackie

 

[Jackie]

Thomas -

I gave up with the various combinations in the end and decided to have a go
at writing my own ASP code for the database retrieval. It's only taken me 10
minutes from knowing nothing to getting it working after all day faffing
about with the DRW yesterday - I didn't realise it was so easy!

All the best,
Jackie

 

[Thomas A. Rowe]

Great! This is why I suggest that folks learn to hand code their ASP.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================