SQL string problem

[Randy]

For the life of me, I can't figure out how to correctly punctuate the end
of this statement!
..ASP classic. Access 2000 database (I'm stuck with this for the time being)

When I in an actual value (2052 in this case) as follows:

Recset.Open "SELECT URL.TITLE, URL.ID, URL.URL from URL, BOATTOURL WHERE
URL.ID = 2052", CONN, 2, 3

It works fine and finds all appropriate records.

I just can't figure out how to put the request.querystring in there as
below. I've put in every comination of single, double, triple quotes I can
think of and just won't work.

Recset.Open "SELECT URL.TITLE, URL.ID, URL.URL from URL, BOATTOURL WHERE
URL.ID = URL_ID AND CLASS_ID = Clng(Request.QueryString("class_ID"))'"',
CONN, 2, 3

(this would normally be on one continuous line).

It's a numeric value being passed from a previous page.

As you can tell, I'm not too experienced with .asp OR SQL.

Hope this is enough info and thanks to anyone who would care to take the
time to look at it.

 

[Bob Barrows [MVP]]

For the life of me, I can't figure out how to correctly punctuate
the end of this statement!
.ASP classic. Access 2000 database (I'm stuck with this for the time
being)
When I in an actual value (2052 in this case) as follows:

Recset.Open "SELECT URL.TITLE, URL.ID, URL.URL from URL, BOATTOURL
WHERE URL.ID = 2052", CONN, 2, 3

??? Is there no link between URL and BOATTOURL? You're getting a cartesian
join here. Is that really what is intended? Actually, why does BOATTOURL
even appear in this sql statement? You're not retrieving any information
from it ...

It works fine and finds all appropriate records.

I just can't figure out how to put the request.querystring in there as
below. I've put in every comination of single, double, triple quotes
I can think of and just won't work.

Recset.Open "SELECT URL.TITLE, URL.ID, URL.URL from URL, BOATTOURL
WHERE URL.ID = URL_ID AND CLASS_ID =

Is URL_ID in BOATTOURL? if so, the correct syntax to use is JOIN, or more
explicitly, INNER JOIN. See below.

So the idea is to only retrieve information from URL if there is related
data in BOATTOURL, correct?

Clng(Request.QueryString("class_ID"))'"', CONN, 2, 3

(this would normally be on one continuous line).

It's a numeric value being passed from a previous page.

As you can tell, I'm not too experienced with .asp OR SQL.

Hope this is enough info and thanks to anyone who would care to take
the time to look at it.

Here is the most secure way to do this, as well as the easiest.

dim sql, arParms, cmd
sql = "SELECT URL.TITLE, URL.ID, URL.URL from URL " & _
" INNER JOIN BOATTOURL ON URL.ID = URL_ID " & _
"WHERE CLASS_ID =?"

arParms = Array(Clng(Request.QueryString("class_ID")))
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1 'adcmdtext
set cmd.activeconnection = conn
set Recset = cmd.execute(,arParms)

 

[Randy]

??? Is there no link between URL and BOATTOURL? You're getting a cartesian
join here. Is that really what is intended? Actually, why does BOATTOURL
even appear in this sql statement? You're not retrieving any information
from it ...

BOATTOURL contains the link between the form and BOAT. But many thanks for
your help. Let me change things around and try to do it the way you have
suggested, which is undoubtedly better. I'll let you know if I can get it
to work.

Is URL_ID in BOATTOURL? if so, the correct syntax to use is JOIN, or more
explicitly, INNER JOIN. See below.

So the idea is to only retrieve information from URL if there is related
data in BOATTOURL, correct?


Here is the most secure way to do this, as well as the easiest.

dim sql, arParms, cmd
sql = "SELECT URL.TITLE, URL.ID, URL.URL from URL " & _
" INNER JOIN BOATTOURL ON URL.ID = URL_ID " & _
"WHERE CLASS_ID =?"

arParms = Array(Clng(Request.QueryString("class_ID")))
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1 'adcmdtext
set cmd.activeconnection = conn
set Recset = cmd.execute(,arParms)


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Randy

 

[Randy]

BOATTOURL contains the link between the form and BOAT. But many thanks
for your help. Let me change things around and try to do it the way you
have suggested, which is undoubtedly better. I'll let you know if I can
get it to work.

I solved the problem. (Thanks again.) I did have difficulty with the method
you suggested with the parameters, placeholder '?' etc.
Is there a complete sample somewhere that I can download, which would
include outputing the items in the array, db path etc. I like to able to
figure out these things on my own as much as possible, and only use a forum
such as this to ask specific questions should I have any.

Cheers
Randy Browning

 

[Bob Barrows [MVP]]

I solved the problem. (Thanks again.) I did have difficulty with the
method you suggested with the parameters, placeholder '?' etc.

What problem? Error message?

Is there a complete sample somewhere that I can download,

The sample included in the post is as complete as any I've got. This is
the message I usually refer to:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

I use stored procedures for the most part.
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

which would
include outputing the items in the array,

? What array?