SQL string problem

Discussion in 'ASP General' started by Randy, Jan 14, 2008.

  1. Randy

    Randy Guest

    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.
     
    Randy, Jan 14, 2008
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Jan 14, 2008
    #2
    1. Advertising

  3. Randy

    Randy Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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
     
    Randy, Jan 14, 2008
    #3
  4. Randy

    Randy Guest

    "Randy" <*> wrote in message
    news:478b776b$0$11610$...
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> 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
     
    Randy, Jan 16, 2008
    #4
  5. 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.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&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    > 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.
     
    Bob Barrows [MVP], Jan 16, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ather Ali Shaikh

    Sql Connection String Problem

    Ather Ali Shaikh, Aug 11, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    779
  2. Dan
    Replies:
    3
    Views:
    4,621
  3. ecoolone
    Replies:
    0
    Views:
    788
    ecoolone
    Jan 3, 2008
  4. dave
    Replies:
    2
    Views:
    1,966
    daveh551
    Dec 4, 2008
  5. Replies:
    10
    Views:
    288
    Bob Barrows [MVP]
    Jan 6, 2005
Loading...

Share This Page