SQL string problem

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.

Re: SQL string problem

Randy wrote:
> 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)


--
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"

Re: SQL string problem

"Bob Barrows [MVP]" wrote in message
news:uxrOfxkVIHA.4196@TK2MSFTNGP04.phx.gbl...
> Randy wrote:
>> 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 ...
>
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.
>>
>> 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)
>
>
> --
> 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

Re: SQL string problem

"Randy" <*randyfromsono@gmail.com> wrote in message
news:478b776b$0$11610$607ed4bc@cv.net...
>
> "Bob Barrows [MVP]" wrote in message
> news:uxrOfxkVIHA.4196@TK2MSFTNGP04.phx.gbl...
>> Randy wrote:
>>> 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 ...
>>
> 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.
>>>
>>> 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)
>>
>>
>> --
>> 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,
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

Re: SQL string problem

Randy wrote:

> 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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


> which would
> include outputing the items in the array,

? What array?

> db path etc.

Huh?

> 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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.