building SQL query strings


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: building SQL query strings

  1. #1
    Russ Wickstrom Guest

    building SQL query strings

    I am trying to put a where clause into a SQL statement in a VBscript ASP.

    The problem I have is trying to embed a string literal into that where
    clause, because the SQL statement as a whole is in a string variable, to
    simplify the execute call. What I have is:


    vSQL = "SELECT * FROM emaillist order by dept, email"
    Set Rs = Conn.Execute(vSQL)



    Without the single quote (demote that the remainder of line is a COMMENT in
    VBscript), I am at a loss to know HOW to have it return any record in which
    the field name contains a string entered on the calling form called
    "search". I would like to something like:

    ....where name = "*formcontent*" order by...

    but anytime I try to embed quotes into the string the page errors out!

    Would someone, please, enlighten me?

    Thank You,

    Russ Wickstrom, MCP
    Intranet Webmaster
    Billy Graham Evangelistic Assn
    Minneapolis, MN

    ph: (612) 335-1300 x2291
    fx: (612) 359-7081

    rwickstrom@bgea.org







  2. #2
    Alex Guest

    Re: building SQL query strings


    "Russ Wickstrom" <rwickstrom@bgea.org> wrote:
    >I am trying to put a where clause into a SQL statement in a VBscript ASP.
    >
    >The problem I have is trying to embed a string literal into that where
    >clause, because the SQL statement as a whole is in a string variable, to
    >simplify the execute call. What I have is:
    >
    >
    >vSQL = "SELECT * FROM emaillist order by dept, email"
    >Set Rs = Conn.Execute(vSQL)
    >
    >
    >
    >Without the single quote (demote that the remainder of line is a COMMENT

    in
    >VBscript), I am at a loss to know HOW to have it return any record in which
    >the field name contains a string entered on the calling form called
    >"search". I would like to something like:
    >
    >....where name = "*formcontent*" order by...
    >
    >but anytime I try to embed quotes into the string the page errors out!
    >
    >Would someone, please, enlighten me?
    >
    >Thank You,
    >
    >Russ Wickstrom, MCP
    >Intranet Webmaster
    >Billy Graham Evangelistic Assn
    >Minneapolis, MN
    >
    >ph: (612) 335-1300 x2291
    >fx: (612) 359-7081
    >
    >rwickstrom@bgea.org
    >
    >
    >
    >
    >
    >



  3. #3
    Calin Guest

    Re: building SQL query strings

    try this:

    vSQL = "SELECT * FROM emaillist " & _
    "WHERE name='" & formcontent & "' " & _
    "ORDER BY dept, email "

    Note that you use single quotes, but they're INSIDE the constant string, and
    surround the value being passed to the DB server.

    --
    Calin (aka Toby)
    http://www.calin.com/
    calin at calin dot com

    "Russ Wickstrom" <rwickstrom@bgea.org> wrote in message
    news:396f0031@news.devx.com...
    > I am trying to put a where clause into a SQL statement in a VBscript ASP.
    >
    > The problem I have is trying to embed a string literal into that where
    > clause, because the SQL statement as a whole is in a string variable, to
    > simplify the execute call. What I have is:
    >
    >
    > vSQL = "SELECT * FROM emaillist order by dept, email"
    > Set Rs = Conn.Execute(vSQL)
    >
    > Without the single quote (demote that the remainder of line is a COMMENT

    in
    > VBscript), I am at a loss to know HOW to have it return any record in

    which
    > the field name contains a string entered on the calling form called
    > "search". I would like to something like:
    >
    > ...where name = "*formcontent*" order by...
    >
    > but anytime I try to embed quotes into the string the page errors out!
    >





  4. #4
    Russ Wickstrom Guest

    Re: building SQL query strings

    Callin:

    Thanks, I'm no longer getting the error message! I am not getting any DATA
    either...

    (ARGH!)

    ---Code fragment follows---

    vFragment = "*" & trim(Request.form("search")) & "*"

    vSQL = "SELECT * FROM emaillist " & _
    "WHERE name='" & vFragment & "' " & _
    "ORDER BY dept, email "

    ---End---

    What I am using is an access database, located on my IIS4 server which I am
    addressing via a DSN.

    There are over 500 records in the table. I take the Department name,
    Person's name, and Email address and construct a table with a mailto: anchor
    tag behind the person's name. What I want to do is JUST display those that
    contain the incoming string within the person's name. It appears that the
    Where clause for a DSN cannot handle wildcards in this way.

    Is this a correct assessment?
    Can anyone suggest another way to limit the results that are returned?

    Thanks allot!

    -Russ
    Calin <calin*@*calin.com> wrote in message news:396f1372$1@news.devx.com...
    > try this:
    >
    > vSQL = "SELECT * FROM emaillist " & _
    > "WHERE name='" & formcontent & "' " & _
    > "ORDER BY dept, email "
    >
    > Note that you use single quotes, but they're INSIDE the constant string,

    and
    > surround the value being passed to the DB server.
    >
    > --
    > Calin (aka Toby)
    > http://www.calin.com/
    > calin at calin dot com
    >

    <snipped>



  5. #5
    Ajit Pal Guest

    Re: building SQL query strings


    hey Russ , you are doing fine with the sql code , just that instead of using
    the relational operator '=' , try using 'Like' while comparing !!
    see if it works..!!
    vFragment = "*" & trim(Request.form("search")) & "*"

    vSQL = "SELECT * FROM emaillist " & _
    "WHERE name Like '" & vFragment & "' " & _
    "ORDER BY dept, email "


    "Russ Wickstrom" <rwickstrom@bgea.org> wrote:
    >Callin:
    >
    >Thanks, I'm no longer getting the error message! I am not getting any DATA
    >either...
    >
    >(ARGH!)
    >
    >---Code fragment follows---
    >
    >vFragment = "*" & trim(Request.form("search")) & "*"
    >
    >vSQL = "SELECT * FROM emaillist " & _
    > "WHERE name='" & vFragment & "' " & _
    > "ORDER BY dept, email "
    >
    >---End---
    >
    >What I am using is an access database, located on my IIS4 server which I

    am
    >addressing via a DSN.
    >
    >There are over 500 records in the table. I take the Department name,
    >Person's name, and Email address and construct a table with a mailto: anchor
    >tag behind the person's name. What I want to do is JUST display those that
    >contain the incoming string within the person's name. It appears that the
    >Where clause for a DSN cannot handle wildcards in this way.
    >
    >Is this a correct assessment?
    >Can anyone suggest another way to limit the results that are returned?
    >
    >Thanks allot!
    >
    >-Russ
    >Calin <calin*@*calin.com> wrote in message news:396f1372$1@news.devx.com...
    >> try this:
    >>
    >> vSQL = "SELECT * FROM emaillist " & _
    >> "WHERE name='" & formcontent & "' " & _
    >> "ORDER BY dept, email "
    >>
    >> Note that you use single quotes, but they're INSIDE the constant string,

    >and
    >> surround the value being passed to the DB server.
    >>
    >> --
    >> Calin (aka Toby)
    >> http://www.calin.com/
    >> calin at calin dot com
    >>

    ><snipped>
    >
    >



  6. #6
    Shawn K. Hall Guest

    Re: building SQL query strings

    Russ,

    In addition to using Like, you might be better off using %
    instead of an * as the string as well.

    > vFragment = "%" & trim(Request.form("search")) & "%"


    Regards,
    --
    Shawn K. Hall
    http://i.am/shawnkhall

    Please post/respond *only* in the newsgroups

    "Ajit Pal " <ajitpal@dcemail.com> wrote in message
    news:396f3f23$1@news.devx.com...
    >
    > hey Russ , you are doing fine with the sql code , just that

    instead of using
    > the relational operator '=' , try using 'Like' while comparing

    !!
    > see if it works..!!
    > vFragment = "*" & trim(Request.form("search")) & "*"
    >
    > vSQL = "SELECT * FROM emaillist " & _
    > "WHERE name Like '" & vFragment & "' " & _
    > "ORDER BY dept, email "





  7. #7
    Mark Harr Guest

    Re: building SQL query strings


    Sorry Shawn, but "*" is correct for an Access database. "%" is for a SQL
    Server.

    Mark

    "Shawn K. Hall" <shawnkhall@iname.com> wrote:
    >Russ,
    >
    >In addition to using Like, you might be better off using %
    >instead of an * as the string as well.
    >
    >> vFragment = "%" & trim(Request.form("search")) & "%"

    >
    >Regards,
    >--
    >Shawn K. Hall
    >http://i.am/shawnkhall
    >
    >Please post/respond *only* in the newsgroups
    >
    >"Ajit Pal " <ajitpal@dcemail.com> wrote in message
    >news:396f3f23$1@news.devx.com...
    >>
    >> hey Russ , you are doing fine with the sql code , just that

    >instead of using
    >> the relational operator '=' , try using 'Like' while comparing

    >!!
    >> see if it works..!!
    >> vFragment = "*" & trim(Request.form("search")) & "*"
    >>
    >> vSQL = "SELECT * FROM emaillist " & _
    >> "WHERE name Like '" & vFragment & "' " & _
    >> "ORDER BY dept, email "

    >
    >
    >



  8. #8
    Russ Wickstrom Guest

    Re: building SQL query strings

    Ajit:

    Thank for the suggestion, but the "like" operator seems to have had no
    effect... I get my canned headers and no data...

    I've not done much in SQL for about 8 years (used to work with INGRES) so it
    is refreshing to know that I'm on the right track, but I do not understand
    what I'm missing and my VBscript reference material isn't ASP specific, so
    it doesn't show the syntax for ODBC connections like this...

    Can anyone recommend a web resource or a book that might be able to help?

    --Russ

    ---

    Ajit Pal <ajitpal@dcemail.com> wrote in message
    news:396f3f23$1@news.devx.com...
    >
    > hey Russ , you are doing fine with the sql code , just that instead of

    using
    > the relational operator '=' , try using 'Like' while comparing !!
    > see if it works..!!
    > vFragment = "*" & trim(Request.form("search")) & "*"
    >
    > vSQL = "SELECT * FROM emaillist " & _
    > "WHERE name Like '" & vFragment & "' " & _
    > "ORDER BY dept, email "
    >
    >
    > "Russ Wickstrom" <rwickstrom@bgea.org> wrote:
    > >Callin:
    > >
    > >Thanks, I'm no longer getting the error message! I am not getting any

    DATA
    > >either...
    > >
    > >(ARGH!)
    > >
    > >---Code fragment follows---
    > >
    > >vFragment = "*" & trim(Request.form("search")) & "*"
    > >
    > >vSQL = "SELECT * FROM emaillist " & _
    > > "WHERE name='" & vFragment & "' " & _
    > > "ORDER BY dept, email "
    > >
    > >---End---
    > >
    > >What I am using is an access database, located on my IIS4 server which I

    > am
    > >addressing via a DSN.
    > >
    > >There are over 500 records in the table. I take the Department name,
    > >Person's name, and Email address and construct a table with a mailto:

    anchor
    > >tag behind the person's name. What I want to do is JUST display those

    that
    > >contain the incoming string within the person's name. It appears that

    the
    > >Where clause for a DSN cannot handle wildcards in this way.
    > >
    > >Is this a correct assessment?
    > >Can anyone suggest another way to limit the results that are returned?
    > >
    > >Thanks allot!
    > >
    > >-Russ
    > ><snipped>
    > >
    > >

    >




  9. #9
    Ajit Pal Guest

    Re: building SQL query strings


    Hi Russ:

    now even i am confused as to why you r getting an empty recordset if you
    have followed the correct syntax in VBscript to connect to access thru ODBC
    !!
    i tried a similar query u posted, on my pc running pws, on asp using vbscript
    connecting a db i have in access, thru ODBC, which has a table for customers
    (their name,add,etc...)

    the code that i wrote worked perfectly fine. please check the connectivity
    syntax or the wildcard u r using.

    try one more thing. Use the wildcard '%' instead of '*'.
    This is just a small segment of the code i am sending ..

    <%
    dim cn,rs,strSQL,cnt,rcount,strTable
    'create a FILEDSN linking it to your access db
    ' setup up the connection and create the recordset to be used on this page

    set cn = server.CreateObject("ADODB.Connection")
    set rs = server.CreateObject("ADODB.Recordset")
    cn.Open ("FILEDSN=pub.dsn")

    set rs = cn.Execute("select * from customers where pname like '%" & trim(request("txtcust"))
    & "%' order by pname ")

    if (rs.EOF and rs.BOF) then
    Response.Write "No Records Found !! "
    Response.End
    end if
    end if


    Response.Write "Displaying Records Found . . " & "<BR>"
    Response.Write cnt & " Record/s found. . . " & "<BR>"

    ' start Display from here....
    while rs.EOF <> true
    rcount=rcount+1
    strTable=""
    Response.Write rs.Fields(i).Name & " : "
    Response.Write rs.Fields(i)
    next
    Response.Write strTable
    rs.MoveNext
    wend




    "Russ Wickstrom" <rwickstrom@bgea.org> wrote:
    >Ajit:
    >
    >Thank for the suggestion, but the "like" operator seems to have had no
    >effect... I get my canned headers and no data...
    >
    >I've not done much in SQL for about 8 years (used to work with INGRES) so

    it
    >is refreshing to know that I'm on the right track, but I do not understand
    >what I'm missing and my VBscript reference material isn't ASP specific,

    so
    >it doesn't show the syntax for ODBC connections like this...
    >
    >Can anyone recommend a web resource or a book that might be able to help?
    >
    >--Russ
    >
    >---
    >
    >Ajit Pal <ajitpal@dcemail.com> wrote in message
    >news:396f3f23$1@news.devx.com...
    >>
    >> hey Russ , you are doing fine with the sql code , just that instead of

    >using
    >> the relational operator '=' , try using 'Like' while comparing !!
    >> see if it works..!!
    >> vFragment = "*" & trim(Request.form("search")) & "*"
    >>
    >> vSQL = "SELECT * FROM emaillist " & _
    >> "WHERE name Like '" & vFragment & "' " & _
    >> "ORDER BY dept, email "
    >>
    >>
    >> "Russ Wickstrom" <rwickstrom@bgea.org> wrote:
    >> >Callin:
    >> >
    >> >Thanks, I'm no longer getting the error message! I am not getting any

    >DATA
    >> >either...
    >> >
    >> >(ARGH!)
    >> >
    >> >---Code fragment follows---
    >> >
    >> >vFragment = "*" & trim(Request.form("search")) & "*"
    >> >
    >> >vSQL = "SELECT * FROM emaillist " & _
    >> > "WHERE name='" & vFragment & "' " & _
    >> > "ORDER BY dept, email "
    >> >
    >> >---End---
    >> >
    >> >What I am using is an access database, located on my IIS4 server which

    I
    >> am
    >> >addressing via a DSN.
    >> >
    >> >There are over 500 records in the table. I take the Department name,
    >> >Person's name, and Email address and construct a table with a mailto:

    >anchor
    >> >tag behind the person's name. What I want to do is JUST display those

    >that
    >> >contain the incoming string within the person's name. It appears that

    >the
    >> >Where clause for a DSN cannot handle wildcards in this way.
    >> >
    >> >Is this a correct assessment?
    >> >Can anyone suggest another way to limit the results that are returned?
    >> >
    >> >Thanks allot!
    >> >
    >> >-Russ
    >> ><snipped>
    >> >
    >> >

    >>

    >
    >



  10. #10
    Russ Wickstrom Guest

    Re: building SQL query strings

    Ajit:

    '%' WAS the answer.

    Thanks to one and all for your assistance.

    --Russ

    ---
    Ajit Pal <ajitpal@dcmail.com> wrote in message
    news:39702650$1@news.devx.com...
    >
    > Hi Russ:
    >
    > now even i am confused as to why you r getting an empty recordset if you
    > have followed the correct syntax in VBscript to connect to access thru

    ODBC
    > !!
    > i tried a similar query u posted, on my pc running pws, on asp using

    vbscript
    > connecting a db i have in access, thru ODBC, which has a table for

    customers
    > (their name,add,etc...)
    >
    > the code that i wrote worked perfectly fine. please check the connectivity
    > syntax or the wildcard u r using.
    >
    > try one more thing. Use the wildcard '%' instead of '*'.
    > This is just a small segment of the code i am sending ..
    >
    > <%
    > dim cn,rs,strSQL,cnt,rcount,strTable
    > 'create a FILEDSN linking it to your access db
    > ' setup up the connection and create the recordset to be used on this page
    >
    > set cn = server.CreateObject("ADODB.Connection")
    > set rs = server.CreateObject("ADODB.Recordset")
    > cn.Open ("FILEDSN=pub.dsn")
    >
    > set rs = cn.Execute("select * from customers where pname like '%" &

    trim(request("txtcust"))
    > & "%' order by pname ")
    >
    > if (rs.EOF and rs.BOF) then
    > Response.Write "No Records Found !! "
    > Response.End
    > end if
    > end if
    >
    >
    > Response.Write "Displaying Records Found . . " & "<BR>"
    > Response.Write cnt & " Record/s found. . . " & "<BR>"
    >
    > ' start Display from here....
    > while rs.EOF <> true
    > rcount=rcount+1
    > strTable=""
    > Response.Write rs.Fields(i).Name & " : "
    > Response.Write rs.Fields(i)
    > next
    > Response.Write strTable
    > rs.MoveNext
    > wend
    >
    >
    >
    >
    ><snipped>





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center