variable in SQL statement

Discussion in 'ASP General' started by Matt, Jul 18, 2006.

  1. Matt

    Matt Guest

    I need to add the following variable into an SQL statement and not sure
    how to do it.

    strGCID needs to be inserted into the following statement:

    SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"

    i am just not sure of the proper syntax.
     
    Matt, Jul 18, 2006
    #1
    1. Advertising

  2. Matt wrote:
    > I need to add the following variable into an SQL statement and not
    > sure how to do it.
    >
    > strGCID needs to be inserted into the following statement:
    >
    > SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON


    http://www.aspfaq.com/show.asp?id=2096

    > tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    >
    > i am just not sure of the proper syntax.


    Here is the secure way:


    SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    c.GCID = g.gcID WHERE c.GCID=?"

    dim arParms
    arParms=array(strGCID)
    dim cmd,rs
    set cmd=createobject("adodb.command")
    cmd.commandtext=sql
    cmd.commandtype=1 'adCmdText
    set cmd.activeconnection=objConn
    set rs = 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], Jul 18, 2006
    #2
    1. Advertising

  3. Matt

    Matt Guest

    Is there an easier way just to put the variable in the SQL Select
    statement?
    Thanks


    Bob Barrows [MVP] wrote:
    > Matt wrote:
    > > I need to add the following variable into an SQL statement and not
    > > sure how to do it.
    > >
    > > strGCID needs to be inserted into the following statement:
    > >
    > > SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON

    >
    > http://www.aspfaq.com/show.asp?id=2096
    >
    > > tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    > >
    > > i am just not sure of the proper syntax.

    >
    > Here is the secure way:
    >
    >
    > SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > c.GCID = g.gcID WHERE c.GCID=?"
    >
    > dim arParms
    > arParms=array(strGCID)
    > dim cmd,rs
    > set cmd=createobject("adodb.command")
    > cmd.commandtext=sql
    > cmd.commandtype=1 'adCmdText
    > set cmd.activeconnection=objConn
    > set rs = 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"
     
    Matt, Jul 19, 2006
    #3
  4. Matt

    Patrice Guest

    You can easily wrap this into your own reusable function if you want to
    write a single line.

    The other well known option you'll see is to construct a string that
    includes the data. Additionaly to security issues it has its own problems
    (as data are written inside the SQL statement, it's easy to use a country or
    server dependant format for those data that could bite you at some point).

    --
    Patrice

    "Matt" <> a écrit dans le message de news:
    ...
    > Is there an easier way just to put the variable in the SQL Select
    > statement?
    > Thanks
    >
    >
    > Bob Barrows [MVP] wrote:
    >> Matt wrote:
    >> > I need to add the following variable into an SQL statement and not
    >> > sure how to do it.
    >> >
    >> > strGCID needs to be inserted into the following statement:
    >> >
    >> > SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON

    >>
    >> http://www.aspfaq.com/show.asp?id=2096
    >>
    >> > tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    >> >
    >> > i am just not sure of the proper syntax.

    >>
    >> Here is the secure way:
    >>
    >>
    >> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >> c.GCID = g.gcID WHERE c.GCID=?"
    >>
    >> dim arParms
    >> arParms=array(strGCID)
    >> dim cmd,rs
    >> set cmd=createobject("adodb.command")
    >> cmd.commandtext=sql
    >> cmd.commandtype=1 'adCmdText
    >> set cmd.activeconnection=objConn
    >> set rs = 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"

    >
     
    Patrice, Jul 19, 2006
    #4
  5. This is the second easiest*, and most secure, way I know.

    More difficult (IMO) is to use dynamic sql: i.e., concatenate the value of
    the variable into the string. It would work like this, given that GCID has a
    numeric datatype:

    sql = " ... WHERE c.GCID = " & strGCID
    'for debugging when things go wrong:
    Response.Write sql

    To me, thistechnique is more difficult because you have to deal with
    delimiters, both when forming the sql statement, and also when the data
    contains characters that are considered to be delimiters by the database
    engine. A huge percentage of the questions we answer on these groups are a
    result of the incorrect handling of delimiter characters. Here is one of my
    older posts where I talk about how to handle delimiters in dynamic sql:
    http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

    The other MAJOR problem with dynamic sql is SQL Injection, which is
    discussed in these articles:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf


    *The post I cited above shows what I consider to be the easiest way to pass
    values into sql statements.

    Matt wrote:
    > Is there an easier way just to put the variable in the SQL Select
    > statement?
    > Thanks
    >
    >
    > Bob Barrows [MVP] wrote:
    >> Matt wrote:
    >>> I need to add the following variable into an SQL statement and not
    >>> sure how to do it.
    >>>
    >>> strGCID needs to be inserted into the following statement:
    >>>
    >>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON

    >>
    >> http://www.aspfaq.com/show.asp?id=2096
    >>
    >>> tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    >>>
    >>> i am just not sure of the proper syntax.

    >>
    >> Here is the secure way:
    >>
    >>
    >> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >> c.GCID = g.gcID WHERE c.GCID=?"
    >>
    >> dim arParms
    >> arParms=array(strGCID)
    >> dim cmd,rs
    >> set cmd=createobject("adodb.command")
    >> cmd.commandtext=sql
    >> cmd.commandtype=1 'adCmdText
    >> set cmd.activeconnection=objConn
    >> set rs = 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"


    --
    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], Jul 19, 2006
    #5
  6. Matt

    Matt Guest

    I am still having problems with this working. Any help is greatly
    appreciated. Here is the entire piece that I am trying to get working:

    Dim objConn
    Dim strConnect, sql, rs
    Dim strGCId

    strGCId = Request.QueryString("gcID")
    strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"

    Set objConn = Server.CreateObject ("ADODB.Connection")

    Set rs = Server.CreateObject ("ADODB.Recordset")

    SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    g.gcID WHERE c.GCID=" & strGCId

    'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN tblGC
    ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" & strGCId

    RS.Open sql, strConnect, adOpenStatic



    response.write "<table>"

    response.write "<tr>"
    response.write "<td><br><h2>" & RS("Company") & "</td>"
    response.write "</tr>"
    response.write "<tr>"
    response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    response.write "</tr>"
    response.write "<tr>"
    response.write "<td><h4>" & RS("City") & "," & RS("State") & "&nbsp;"
    & RS("ZipCode") & "</b></td>"
    response.write "</tr>"
    response.write "<tr>"
    response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    RS("WebsiteURL") & "</a><br><br></td>"
    response.write "</tr>"
    response.write "</table>"

    response.write "<table border=0>"
    response.write "<tr>"
    response.write "<td width=200><b>Name</b></td><td
    width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    response.write "</tr>"

    Do While Not RS.EOF

    response.write "<tr>"
    response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    response.write "<td valign=top>" & RS("WorkPhone") & "<br>Ext&nbsp;" &
    RS("contactPhoneExt") & "</td>"
    response.write "<td valign=top>" & RS("contactMobile") & "</td>"

    response.write "</tr>"
    response.write "<tr>"
    response.write "<td colspan=3 valign=top><a href=mailto:" &
    RS("contactEmail") & ">" & RS("contactEmail") & "</a><br><br></td>"
    response.write "</tr>"
    response.write "<tr>"
    response.write "<td colspan=3><hr></td>"
    response.write "</tr>"

    RS.MoveNext
    Loop

    response.write "</table>"

    rs.close



    %>
    Bob Barrows [MVP] wrote:
    > This is the second easiest*, and most secure, way I know.
    >
    > More difficult (IMO) is to use dynamic sql: i.e., concatenate the value of
    > the variable into the string. It would work like this, given that GCID has a
    > numeric datatype:
    >
    > sql = " ... WHERE c.GCID = " & strGCID
    > 'for debugging when things go wrong:
    > Response.Write sql
    >
    > To me, thistechnique is more difficult because you have to deal with
    > delimiters, both when forming the sql statement, and also when the data
    > contains characters that are considered to be delimiters by the database
    > engine. A huge percentage of the questions we answer on these groups are a
    > result of the incorrect handling of delimiter characters. Here is one of my
    > older posts where I talk about how to handle delimiters in dynamic sql:
    > http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >
    > The other MAJOR problem with dynamic sql is SQL Injection, which is
    > discussed in these articles:
    > http://mvp.unixwiz.net/techtips/sql-injection.html
    > http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    >
    >
    > *The post I cited above shows what I consider to be the easiest way to pass
    > values into sql statements.
    >
    > Matt wrote:
    > > Is there an easier way just to put the variable in the SQL Select
    > > statement?
    > > Thanks
    > >
    > >
    > > Bob Barrows [MVP] wrote:
    > >> Matt wrote:
    > >>> I need to add the following variable into an SQL statement and not
    > >>> sure how to do it.
    > >>>
    > >>> strGCID needs to be inserted into the following statement:
    > >>>
    > >>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    > >>
    > >> http://www.aspfaq.com/show.asp?id=2096
    > >>
    > >>> tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    > >>>
    > >>> i am just not sure of the proper syntax.
    > >>
    > >> Here is the secure way:
    > >>
    > >>
    > >> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >> c.GCID = g.gcID WHERE c.GCID=?"
    > >>
    > >> dim arParms
    > >> arParms=array(strGCID)
    > >> dim cmd,rs
    > >> set cmd=createobject("adodb.command")
    > >> cmd.commandtext=sql
    > >> cmd.commandtype=1 'adCmdText
    > >> set cmd.activeconnection=objConn
    > >> set rs = 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"

    >
    > --
    > 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"
     
    Matt, Jul 19, 2006
    #6
  7. Matt

    Patrice Guest

    You forgot to mention the problem in your post...

    A quick read raises :

    I see \\ in the path of the DB. Also depending on which Jet engine you are
    using the JOIN notation could perhaps be different or if strGCId is a string
    you'll have to enclose this value within quotes (response.write your SQL
    statement as suggested by Bob).

    The error message would be really helpfull.

    --
    Patrice

    "Matt" <> a écrit dans le message de news:
    ...
    >I am still having problems with this working. Any help is greatly
    > appreciated. Here is the entire piece that I am trying to get working:
    >
    > Dim objConn
    > Dim strConnect, sql, rs
    > Dim strGCId
    >
    > strGCId = Request.QueryString("gcID")
    > strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    >
    > Set objConn = Server.CreateObject ("ADODB.Connection")
    >
    > Set rs = Server.CreateObject ("ADODB.Recordset")
    >
    > SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    > g.gcID WHERE c.GCID=" & strGCId
    >
    > 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN tblGC
    > ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" & strGCId
    >
    > RS.Open sql, strConnect, adOpenStatic
    >
    >
    >
    > response.write "<table>"
    >
    > response.write "<tr>"
    > response.write "<td><br><h2>" & RS("Company") & "</td>"
    > response.write "</tr>"
    > response.write "<tr>"
    > response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > response.write "</tr>"
    > response.write "<tr>"
    > response.write "<td><h4>" & RS("City") & "," & RS("State") & "&nbsp;"
    > & RS("ZipCode") & "</b></td>"
    > response.write "</tr>"
    > response.write "<tr>"
    > response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > RS("WebsiteURL") & "</a><br><br></td>"
    > response.write "</tr>"
    > response.write "</table>"
    >
    > response.write "<table border=0>"
    > response.write "<tr>"
    > response.write "<td width=200><b>Name</b></td><td
    > width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > response.write "</tr>"
    >
    > Do While Not RS.EOF
    >
    > response.write "<tr>"
    > response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    > RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    > response.write "<td valign=top>" & RS("WorkPhone") & "<br>Ext&nbsp;" &
    > RS("contactPhoneExt") & "</td>"
    > response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    >
    > response.write "</tr>"
    > response.write "<tr>"
    > response.write "<td colspan=3 valign=top><a href=mailto:" &
    > RS("contactEmail") & ">" & RS("contactEmail") & "</a><br><br></td>"
    > response.write "</tr>"
    > response.write "<tr>"
    > response.write "<td colspan=3><hr></td>"
    > response.write "</tr>"
    >
    > RS.MoveNext
    > Loop
    >
    > response.write "</table>"
    >
    > rs.close
    >
    >
    >
    > %>
    > Bob Barrows [MVP] wrote:
    >> This is the second easiest*, and most secure, way I know.
    >>
    >> More difficult (IMO) is to use dynamic sql: i.e., concatenate the value
    >> of
    >> the variable into the string. It would work like this, given that GCID
    >> has a
    >> numeric datatype:
    >>
    >> sql = " ... WHERE c.GCID = " & strGCID
    >> 'for debugging when things go wrong:
    >> Response.Write sql
    >>
    >> To me, thistechnique is more difficult because you have to deal with
    >> delimiters, both when forming the sql statement, and also when the data
    >> contains characters that are considered to be delimiters by the database
    >> engine. A huge percentage of the questions we answer on these groups are
    >> a
    >> result of the incorrect handling of delimiter characters. Here is one of
    >> my
    >> older posts where I talk about how to handle delimiters in dynamic sql:
    >> http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >>
    >> The other MAJOR problem with dynamic sql is SQL Injection, which is
    >> discussed in these articles:
    >> http://mvp.unixwiz.net/techtips/sql-injection.html
    >> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    >> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    >>
    >>
    >> *The post I cited above shows what I consider to be the easiest way to
    >> pass
    >> values into sql statements.
    >>
    >> Matt wrote:
    >> > Is there an easier way just to put the variable in the SQL Select
    >> > statement?
    >> > Thanks
    >> >
    >> >
    >> > Bob Barrows [MVP] wrote:
    >> >> Matt wrote:
    >> >>> I need to add the following variable into an SQL statement and not
    >> >>> sure how to do it.
    >> >>>
    >> >>> strGCID needs to be inserted into the following statement:
    >> >>>
    >> >>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    >> >>
    >> >> http://www.aspfaq.com/show.asp?id=2096
    >> >>
    >> >>> tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    >> >>>
    >> >>> i am just not sure of the proper syntax.
    >> >>
    >> >> Here is the secure way:
    >> >>
    >> >>
    >> >> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >> >> c.GCID = g.gcID WHERE c.GCID=?"
    >> >>
    >> >> dim arParms
    >> >> arParms=array(strGCID)
    >> >> dim cmd,rs
    >> >> set cmd=createobject("adodb.command")
    >> >> cmd.commandtext=sql
    >> >> cmd.commandtype=1 'adCmdText
    >> >> set cmd.activeconnection=objConn
    >> >> set rs = 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"

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

    >
     
    Patrice, Jul 19, 2006
    #7
  8. Matt

    Matt Guest

    Response.write SQL produces:

    SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    WHERE c.GCID=2582

    which is the corried GCId that I am looking for... but no data is
    returned... no error messages, just no data

    Patrice wrote:
    > You forgot to mention the problem in your post...
    >
    > A quick read raises :
    >
    > I see \\ in the path of the DB. Also depending on which Jet engine you are
    > using the JOIN notation could perhaps be different or if strGCId is a string
    > you'll have to enclose this value within quotes (response.write your SQL
    > statement as suggested by Bob).
    >
    > The error message would be really helpfull.
    >
    > --
    > Patrice
    >
    > "Matt" <> a écrit dans le message de news:
    > ...
    > >I am still having problems with this working. Any help is greatly
    > > appreciated. Here is the entire piece that I am trying to get working:
    > >
    > > Dim objConn
    > > Dim strConnect, sql, rs
    > > Dim strGCId
    > >
    > > strGCId = Request.QueryString("gcID")
    > > strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > > DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    > >
    > > Set objConn = Server.CreateObject ("ADODB.Connection")
    > >
    > > Set rs = Server.CreateObject ("ADODB.Recordset")
    > >
    > > SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    > > g.gcID WHERE c.GCID=" & strGCId
    > >
    > > 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN tblGC
    > > ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" & strGCId
    > >
    > > RS.Open sql, strConnect, adOpenStatic
    > >
    > >
    > >
    > > response.write "<table>"
    > >
    > > response.write "<tr>"
    > > response.write "<td><br><h2>" & RS("Company") & "</td>"
    > > response.write "</tr>"
    > > response.write "<tr>"
    > > response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > > response.write "</tr>"
    > > response.write "<tr>"
    > > response.write "<td><h4>" & RS("City") & "," & RS("State") & "&nbsp;"
    > > & RS("ZipCode") & "</b></td>"
    > > response.write "</tr>"
    > > response.write "<tr>"
    > > response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > > RS("WebsiteURL") & "</a><br><br></td>"
    > > response.write "</tr>"
    > > response.write "</table>"
    > >
    > > response.write "<table border=0>"
    > > response.write "<tr>"
    > > response.write "<td width=200><b>Name</b></td><td
    > > width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > > response.write "</tr>"
    > >
    > > Do While Not RS.EOF
    > >
    > > response.write "<tr>"
    > > response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    > > RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    > > response.write "<td valign=top>" & RS("WorkPhone") & "<br>Ext&nbsp;" &
    > > RS("contactPhoneExt") & "</td>"
    > > response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    > >
    > > response.write "</tr>"
    > > response.write "<tr>"
    > > response.write "<td colspan=3 valign=top><a href=mailto:" &
    > > RS("contactEmail") & ">" & RS("contactEmail") & "</a><br><br></td>"
    > > response.write "</tr>"
    > > response.write "<tr>"
    > > response.write "<td colspan=3><hr></td>"
    > > response.write "</tr>"
    > >
    > > RS.MoveNext
    > > Loop
    > >
    > > response.write "</table>"
    > >
    > > rs.close
    > >
    > >
    > >
    > > %>
    > > Bob Barrows [MVP] wrote:
    > >> This is the second easiest*, and most secure, way I know.
    > >>
    > >> More difficult (IMO) is to use dynamic sql: i.e., concatenate the value
    > >> of
    > >> the variable into the string. It would work like this, given that GCID
    > >> has a
    > >> numeric datatype:
    > >>
    > >> sql = " ... WHERE c.GCID = " & strGCID
    > >> 'for debugging when things go wrong:
    > >> Response.Write sql
    > >>
    > >> To me, thistechnique is more difficult because you have to deal with
    > >> delimiters, both when forming the sql statement, and also when the data
    > >> contains characters that are considered to be delimiters by the database
    > >> engine. A huge percentage of the questions we answer on these groups are
    > >> a
    > >> result of the incorrect handling of delimiter characters. Here is oneof
    > >> my
    > >> older posts where I talk about how to handle delimiters in dynamic sql:
    > >> http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    > >>
    > >> The other MAJOR problem with dynamic sql is SQL Injection, which is
    > >> discussed in these articles:
    > >> http://mvp.unixwiz.net/techtips/sql-injection.html
    > >> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > >> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > >> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > >>
    > >>
    > >> *The post I cited above shows what I consider to be the easiest way to
    > >> pass
    > >> values into sql statements.
    > >>
    > >> Matt wrote:
    > >> > Is there an easier way just to put the variable in the SQL Select
    > >> > statement?
    > >> > Thanks
    > >> >
    > >> >
    > >> > Bob Barrows [MVP] wrote:
    > >> >> Matt wrote:
    > >> >>> I need to add the following variable into an SQL statement and not
    > >> >>> sure how to do it.
    > >> >>>
    > >> >>> strGCID needs to be inserted into the following statement:
    > >> >>>
    > >> >>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    > >> >>
    > >> >> http://www.aspfaq.com/show.asp?id=2096
    > >> >>
    > >> >>> tblContacts.GCID = tblGC.gcID WHERE (((tblContacts.GCID)=strGCID))"
    > >> >>>
    > >> >>> i am just not sure of the proper syntax.
    > >> >>
    > >> >> Here is the secure way:
    > >> >>
    > >> >>
    > >> >> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >> >> c.GCID = g.gcID WHERE c.GCID=?"
    > >> >>
    > >> >> dim arParms
    > >> >> arParms=array(strGCID)
    > >> >> dim cmd,rs
    > >> >> set cmd=createobject("adodb.command")
    > >> >> cmd.commandtext=sql
    > >> >> cmd.commandtype=1 'adCmdText
    > >> >> set cmd.activeconnection=objConn
    > >> >> set rs = 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"
    > >>
    > >> --
    > >> 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"

    > >
     
    Matt, Jul 19, 2006
    #8
  9. I just notice. Why is tblGC involved in this query at all? It seems to
    me that
    SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    would retrieve the same results ...

    Open your database in Access, create a new query in Design View, switch
    to SQL View, paste in the result of the respone.write and try it. Does
    it return records? If so, try my version above and see if it returns a
    different set of records.


    Matt wrote:
    > Response.write SQL produces:
    >
    > SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    > WHERE c.GCID=2582
    >
    > which is the corried GCId that I am looking for... but no data is
    > returned... no error messages, just no data
    >
    > Patrice wrote:
    >> You forgot to mention the problem in your post...
    >>
    >> A quick read raises :
    >>
    >> I see \\ in the path of the DB. Also depending on which Jet engine
    >> you are using the JOIN notation could perhaps be different or if
    >> strGCId is a string you'll have to enclose this value within quotes
    >> (response.write your SQL statement as suggested by Bob).
    >>
    >> The error message would be really helpfull.
    >>
    >> --
    >> Patrice
    >>
    >> "Matt" <> a écrit dans le message de news:
    >> ...
    >>> I am still having problems with this working. Any help is greatly
    >>> appreciated. Here is the entire piece that I am trying to get
    >>> working:
    >>>
    >>> Dim objConn
    >>> Dim strConnect, sql, rs
    >>> Dim strGCId
    >>>
    >>> strGCId = Request.QueryString("gcID")
    >>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    >>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    >>>
    >>> Set objConn = Server.CreateObject ("ADODB.Connection")
    >>>
    >>> Set rs = Server.CreateObject ("ADODB.Recordset")
    >>>
    >>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    >>> g.gcID WHERE c.GCID=" & strGCId
    >>>
    >>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    >>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" &
    >>> strGCId
    >>>
    >>> RS.Open sql, strConnect, adOpenStatic
    >>>
    >>>
    >>>
    >>> response.write "<table>"
    >>>
    >>> response.write "<tr>"
    >>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    >>> response.write "</tr>"
    >>> response.write "<tr>"
    >>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    >>> response.write "</tr>"
    >>> response.write "<tr>"
    >>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    >>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    >>> response.write "</tr>"
    >>> response.write "<tr>"
    >>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    >>> RS("WebsiteURL") & "</a><br><br></td>"
    >>> response.write "</tr>"
    >>> response.write "</table>"
    >>>
    >>> response.write "<table border=0>"
    >>> response.write "<tr>"
    >>> response.write "<td width=200><b>Name</b></td><td
    >>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    >>> response.write "</tr>"
    >>>
    >>> Do While Not RS.EOF
    >>>
    >>> response.write "<tr>"
    >>> response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    >>> RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    >>> response.write "<td valign=top>" & RS("WorkPhone") &
    >>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    >>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    >>>
    >>> response.write "</tr>"
    >>> response.write "<tr>"
    >>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    >>> RS("contactEmail") & ">" & RS("contactEmail") & "</a><br><br></td>"
    >>> response.write "</tr>"
    >>> response.write "<tr>"
    >>> response.write "<td colspan=3><hr></td>"
    >>> response.write "</tr>"
    >>>
    >>> RS.MoveNext
    >>> Loop
    >>>
    >>> response.write "</table>"
    >>>
    >>> rs.close
    >>>
    >>>
    >>>
    >>> %>
    >>> Bob Barrows [MVP] wrote:
    >>>> This is the second easiest*, and most secure, way I know.
    >>>>
    >>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate the
    >>>> value of
    >>>> the variable into the string. It would work like this, given that
    >>>> GCID has a
    >>>> numeric datatype:
    >>>>
    >>>> sql = " ... WHERE c.GCID = " & strGCID
    >>>> 'for debugging when things go wrong:
    >>>> Response.Write sql
    >>>>
    >>>> To me, thistechnique is more difficult because you have to deal
    >>>> with delimiters, both when forming the sql statement, and also
    >>>> when the data contains characters that are considered to be
    >>>> delimiters by the database engine. A huge percentage of the
    >>>> questions we answer on these groups are a
    >>>> result of the incorrect handling of delimiter characters. Here is
    >>>> one of my
    >>>> older posts where I talk about how to handle delimiters in dynamic
    >>>> sql:
    >>>>

    http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >>>>
    >>>> The other MAJOR problem with dynamic sql is SQL Injection, which is
    >>>> discussed in these articles:
    >>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    >>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    >>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    >>>>
    >>>>
    >>>> *The post I cited above shows what I consider to be the easiest
    >>>> way to pass
    >>>> values into sql statements.
    >>>>
    >>>> Matt wrote:
    >>>>> Is there an easier way just to put the variable in the SQL Select
    >>>>> statement?
    >>>>> Thanks
    >>>>>
    >>>>>
    >>>>> Bob Barrows [MVP] wrote:
    >>>>>> Matt wrote:
    >>>>>>> I need to add the following variable into an SQL statement and
    >>>>>>> not sure how to do it.
    >>>>>>>
    >>>>>>> strGCID needs to be inserted into the following statement:
    >>>>>>>
    >>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    >>>>>>
    >>>>>> http://www.aspfaq.com/show.asp?id=2096
    >>>>>>
    >>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    >>>>>>> (((tblContacts.GCID)=strGCID))"
    >>>>>>>
    >>>>>>> i am just not sure of the proper syntax.
    >>>>>>
    >>>>>> Here is the secure way:
    >>>>>>
    >>>>>>
    >>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    >>>>>>
    >>>>>> dim arParms
    >>>>>> arParms=array(strGCID)
    >>>>>> dim cmd,rs
    >>>>>> set cmd=createobject("adodb.command")
    >>>>>> cmd.commandtext=sql
    >>>>>> cmd.commandtype=1 'adCmdText
    >>>>>> set cmd.activeconnection=objConn
    >>>>>> set rs = 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"
    >>>>
    >>>> --
    >>>> 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"


    --
    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], Jul 19, 2006
    #9
  10. Matt

    Matt Guest

    tblGC contains all of the company information and tblContacts contains
    detailed employee information




    Bob Barrows [MVP] wrote:
    > I just notice. Why is tblGC involved in this query at all? It seems to
    > me that
    > SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    > would retrieve the same results ...
    >
    > Open your database in Access, create a new query in Design View, switch
    > to SQL View, paste in the result of the respone.write and try it. Does
    > it return records? If so, try my version above and see if it returns a
    > different set of records.
    >
    >
    > Matt wrote:
    > > Response.write SQL produces:
    > >
    > > SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    > > WHERE c.GCID=2582
    > >
    > > which is the corried GCId that I am looking for... but no data is
    > > returned... no error messages, just no data
    > >
    > > Patrice wrote:
    > >> You forgot to mention the problem in your post...
    > >>
    > >> A quick read raises :
    > >>
    > >> I see \\ in the path of the DB. Also depending on which Jet engine
    > >> you are using the JOIN notation could perhaps be different or if
    > >> strGCId is a string you'll have to enclose this value within quotes
    > >> (response.write your SQL statement as suggested by Bob).
    > >>
    > >> The error message would be really helpfull.
    > >>
    > >> --
    > >> Patrice
    > >>
    > >> "Matt" <> a écrit dans le message de news:
    > >> ...
    > >>> I am still having problems with this working. Any help is greatly
    > >>> appreciated. Here is the entire piece that I am trying to get
    > >>> working:
    > >>>
    > >>> Dim objConn
    > >>> Dim strConnect, sql, rs
    > >>> Dim strGCId
    > >>>
    > >>> strGCId = Request.QueryString("gcID")
    > >>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > >>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    > >>>
    > >>> Set objConn = Server.CreateObject ("ADODB.Connection")
    > >>>
    > >>> Set rs = Server.CreateObject ("ADODB.Recordset")
    > >>>
    > >>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    > >>> g.gcID WHERE c.GCID=" & strGCId
    > >>>
    > >>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    > >>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" &
    > >>> strGCId
    > >>>
    > >>> RS.Open sql, strConnect, adOpenStatic
    > >>>
    > >>>
    > >>>
    > >>> response.write "<table>"
    > >>>
    > >>> response.write "<tr>"
    > >>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    > >>> response.write "</tr>"
    > >>> response.write "<tr>"
    > >>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > >>> response.write "</tr>"
    > >>> response.write "<tr>"
    > >>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    > >>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    > >>> response.write "</tr>"
    > >>> response.write "<tr>"
    > >>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > >>> RS("WebsiteURL") & "</a><br><br></td>"
    > >>> response.write "</tr>"
    > >>> response.write "</table>"
    > >>>
    > >>> response.write "<table border=0>"
    > >>> response.write "<tr>"
    > >>> response.write "<td width=200><b>Name</b></td><td
    > >>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > >>> response.write "</tr>"
    > >>>
    > >>> Do While Not RS.EOF
    > >>>
    > >>> response.write "<tr>"
    > >>> response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    > >>> RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    > >>> response.write "<td valign=top>" & RS("WorkPhone") &
    > >>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    > >>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    > >>>
    > >>> response.write "</tr>"
    > >>> response.write "<tr>"
    > >>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    > >>> RS("contactEmail") & ">" & RS("contactEmail") & "</a><br><br></td>"
    > >>> response.write "</tr>"
    > >>> response.write "<tr>"
    > >>> response.write "<td colspan=3><hr></td>"
    > >>> response.write "</tr>"
    > >>>
    > >>> RS.MoveNext
    > >>> Loop
    > >>>
    > >>> response.write "</table>"
    > >>>
    > >>> rs.close
    > >>>
    > >>>
    > >>>
    > >>> %>
    > >>> Bob Barrows [MVP] wrote:
    > >>>> This is the second easiest*, and most secure, way I know.
    > >>>>
    > >>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate the
    > >>>> value of
    > >>>> the variable into the string. It would work like this, given that
    > >>>> GCID has a
    > >>>> numeric datatype:
    > >>>>
    > >>>> sql = " ... WHERE c.GCID = " & strGCID
    > >>>> 'for debugging when things go wrong:
    > >>>> Response.Write sql
    > >>>>
    > >>>> To me, thistechnique is more difficult because you have to deal
    > >>>> with delimiters, both when forming the sql statement, and also
    > >>>> when the data contains characters that are considered to be
    > >>>> delimiters by the database engine. A huge percentage of the
    > >>>> questions we answer on these groups are a
    > >>>> result of the incorrect handling of delimiter characters. Here is
    > >>>> one of my
    > >>>> older posts where I talk about how to handle delimiters in dynamic
    > >>>> sql:
    > >>>>

    > http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    > >>>>
    > >>>> The other MAJOR problem with dynamic sql is SQL Injection, which is
    > >>>> discussed in these articles:
    > >>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    > >>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > >>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > >>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > >>>>
    > >>>>
    > >>>> *The post I cited above shows what I consider to be the easiest
    > >>>> way to pass
    > >>>> values into sql statements.
    > >>>>
    > >>>> Matt wrote:
    > >>>>> Is there an easier way just to put the variable in the SQL Select
    > >>>>> statement?
    > >>>>> Thanks
    > >>>>>
    > >>>>>
    > >>>>> Bob Barrows [MVP] wrote:
    > >>>>>> Matt wrote:
    > >>>>>>> I need to add the following variable into an SQL statement and
    > >>>>>>> not sure how to do it.
    > >>>>>>>
    > >>>>>>> strGCID needs to be inserted into the following statement:
    > >>>>>>>
    > >>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC ON
    > >>>>>>
    > >>>>>> http://www.aspfaq.com/show.asp?id=2096
    > >>>>>>
    > >>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    > >>>>>>> (((tblContacts.GCID)=strGCID))"
    > >>>>>>>
    > >>>>>>> i am just not sure of the proper syntax.
    > >>>>>>
    > >>>>>> Here is the secure way:
    > >>>>>>
    > >>>>>>
    > >>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    > >>>>>>
    > >>>>>> dim arParms
    > >>>>>> arParms=array(strGCID)
    > >>>>>> dim cmd,rs
    > >>>>>> set cmd=createobject("adodb.command")
    > >>>>>> cmd.commandtext=sql
    > >>>>>> cmd.commandtype=1 'adCmdText
    > >>>>>> set cmd.activeconnection=objConn
    > >>>>>> set rs = 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"
    > >>>>
    > >>>> --
    > >>>> 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"

    >
    > --
    > 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.
     
    Matt, Jul 19, 2006
    #10
  11. Right ... but you're not returning any of the data from tblGC, so why
    mention it in the query?

    Matt wrote:
    > tblGC contains all of the company information and tblContacts contains
    > detailed employee information
    >
    >
    >
    >
    > Bob Barrows [MVP] wrote:
    >> I just notice. Why is tblGC involved in this query at all? It seems
    >> to me that
    >> SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    >> would retrieve the same results ...
    >>
    >> Open your database in Access, create a new query in Design View,
    >> switch to SQL View, paste in the result of the respone.write and try
    >> it. Does it return records? If so, try my version above and see if
    >> it returns a different set of records.
    >>
    >>
    >> Matt wrote:
    >>> Response.write SQL produces:
    >>>
    >>> SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    >>> WHERE c.GCID=2582
    >>>
    >>> which is the corried GCId that I am looking for... but no data is
    >>> returned... no error messages, just no data
    >>>
    >>> Patrice wrote:
    >>>> You forgot to mention the problem in your post...
    >>>>
    >>>> A quick read raises :
    >>>>
    >>>> I see \\ in the path of the DB. Also depending on which Jet engine
    >>>> you are using the JOIN notation could perhaps be different or if
    >>>> strGCId is a string you'll have to enclose this value within quotes
    >>>> (response.write your SQL statement as suggested by Bob).
    >>>>
    >>>> The error message would be really helpfull.
    >>>>
    >>>> --
    >>>> Patrice
    >>>>
    >>>> "Matt" <> a écrit dans le message de news:
    >>>> ...
    >>>>> I am still having problems with this working. Any help is greatly
    >>>>> appreciated. Here is the entire piece that I am trying to get
    >>>>> working:
    >>>>>
    >>>>> Dim objConn
    >>>>> Dim strConnect, sql, rs
    >>>>> Dim strGCId
    >>>>>
    >>>>> strGCId = Request.QueryString("gcID")
    >>>>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    >>>>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    >>>>>
    >>>>> Set objConn = Server.CreateObject ("ADODB.Connection")
    >>>>>
    >>>>> Set rs = Server.CreateObject ("ADODB.Recordset")
    >>>>>
    >>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID
    >>>>> = g.gcID WHERE c.GCID=" & strGCId
    >>>>>
    >>>>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    >>>>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" &
    >>>>> strGCId
    >>>>>
    >>>>> RS.Open sql, strConnect, adOpenStatic
    >>>>>
    >>>>>
    >>>>>
    >>>>> response.write "<table>"
    >>>>>
    >>>>> response.write "<tr>"
    >>>>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    >>>>> response.write "</tr>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    >>>>> response.write "</tr>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    >>>>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    >>>>> response.write "</tr>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    >>>>> RS("WebsiteURL") & "</a><br><br></td>"
    >>>>> response.write "</tr>"
    >>>>> response.write "</table>"
    >>>>>
    >>>>> response.write "<table border=0>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td width=200><b>Name</b></td><td
    >>>>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    >>>>> response.write "</tr>"
    >>>>>
    >>>>> Do While Not RS.EOF
    >>>>>
    >>>>> response.write "<tr>"
    >>>>> response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    >>>>> RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    >>>>> response.write "<td valign=top>" & RS("WorkPhone") &
    >>>>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    >>>>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    >>>>>
    >>>>> response.write "</tr>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    >>>>> RS("contactEmail") & ">" & RS("contactEmail") &
    >>>>> "</a><br><br></td>" response.write "</tr>"
    >>>>> response.write "<tr>"
    >>>>> response.write "<td colspan=3><hr></td>"
    >>>>> response.write "</tr>"
    >>>>>
    >>>>> RS.MoveNext
    >>>>> Loop
    >>>>>
    >>>>> response.write "</table>"
    >>>>>
    >>>>> rs.close
    >>>>>
    >>>>>
    >>>>>
    >>>>> %>
    >>>>> Bob Barrows [MVP] wrote:
    >>>>>> This is the second easiest*, and most secure, way I know.
    >>>>>>
    >>>>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate the
    >>>>>> value of
    >>>>>> the variable into the string. It would work like this, given that
    >>>>>> GCID has a
    >>>>>> numeric datatype:
    >>>>>>
    >>>>>> sql = " ... WHERE c.GCID = " & strGCID
    >>>>>> 'for debugging when things go wrong:
    >>>>>> Response.Write sql
    >>>>>>
    >>>>>> To me, thistechnique is more difficult because you have to deal
    >>>>>> with delimiters, both when forming the sql statement, and also
    >>>>>> when the data contains characters that are considered to be
    >>>>>> delimiters by the database engine. A huge percentage of the
    >>>>>> questions we answer on these groups are a
    >>>>>> result of the incorrect handling of delimiter characters. Here
    >>>>>> is one of my
    >>>>>> older posts where I talk about how to handle delimiters in
    >>>>>> dynamic sql:
    >>>>>>

    >>

    http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >>>>>>
    >>>>>> The other MAJOR problem with dynamic sql is SQL Injection, which
    >>>>>> is discussed in these articles:
    >>>>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    >>>>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >>>>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    >>>>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    >>>>>>
    >>>>>>
    >>>>>> *The post I cited above shows what I consider to be the easiest
    >>>>>> way to pass
    >>>>>> values into sql statements.
    >>>>>>
    >>>>>> Matt wrote:
    >>>>>>> Is there an easier way just to put the variable in the SQL
    >>>>>>> Select statement?
    >>>>>>> Thanks
    >>>>>>>
    >>>>>>>
    >>>>>>> Bob Barrows [MVP] wrote:
    >>>>>>>> Matt wrote:
    >>>>>>>>> I need to add the following variable into an SQL statement and
    >>>>>>>>> not sure how to do it.
    >>>>>>>>>
    >>>>>>>>> strGCID needs to be inserted into the following statement:
    >>>>>>>>>
    >>>>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC
    >>>>>>>>> ON
    >>>>>>>>
    >>>>>>>> http://www.aspfaq.com/show.asp?id=2096
    >>>>>>>>
    >>>>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    >>>>>>>>> (((tblContacts.GCID)=strGCID))"
    >>>>>>>>>
    >>>>>>>>> i am just not sure of the proper syntax.
    >>>>>>>>
    >>>>>>>> Here is the secure way:
    >>>>>>>>
    >>>>>>>>
    >>>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >>>>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    >>>>>>>>
    >>>>>>>> dim arParms
    >>>>>>>> arParms=array(strGCID)
    >>>>>>>> dim cmd,rs
    >>>>>>>> set cmd=createobject("adodb.command")
    >>>>>>>> cmd.commandtext=sql
    >>>>>>>> cmd.commandtype=1 'adCmdText
    >>>>>>>> set cmd.activeconnection=objConn
    >>>>>>>> set rs = 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"
    >>>>>>
    >>>>>> --
    >>>>>> 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"

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


    --
    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], Jul 19, 2006
    #11
  12. Matt

    Matt Guest

    I am returning data from the tblGC, RS("Company") RS("Address1") etc...
    all the rs's in the first table come from the tblGC.



    Bob Barrows [MVP] wrote:
    > Right ... but you're not returning any of the data from tblGC, so why
    > mention it in the query?
    >
    > Matt wrote:
    > > tblGC contains all of the company information and tblContacts contains
    > > detailed employee information
    > >
    > >
    > >
    > >
    > > Bob Barrows [MVP] wrote:
    > >> I just notice. Why is tblGC involved in this query at all? It seems
    > >> to me that
    > >> SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    > >> would retrieve the same results ...
    > >>
    > >> Open your database in Access, create a new query in Design View,
    > >> switch to SQL View, paste in the result of the respone.write and try
    > >> it. Does it return records? If so, try my version above and see if
    > >> it returns a different set of records.
    > >>
    > >>
    > >> Matt wrote:
    > >>> Response.write SQL produces:
    > >>>
    > >>> SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    > >>> WHERE c.GCID=2582
    > >>>
    > >>> which is the corried GCId that I am looking for... but no data is
    > >>> returned... no error messages, just no data
    > >>>
    > >>> Patrice wrote:
    > >>>> You forgot to mention the problem in your post...
    > >>>>
    > >>>> A quick read raises :
    > >>>>
    > >>>> I see \\ in the path of the DB. Also depending on which Jet engine
    > >>>> you are using the JOIN notation could perhaps be different or if
    > >>>> strGCId is a string you'll have to enclose this value within quotes
    > >>>> (response.write your SQL statement as suggested by Bob).
    > >>>>
    > >>>> The error message would be really helpfull.
    > >>>>
    > >>>> --
    > >>>> Patrice
    > >>>>
    > >>>> "Matt" <> a écrit dans le message de news:
    > >>>> ...
    > >>>>> I am still having problems with this working. Any help is greatly
    > >>>>> appreciated. Here is the entire piece that I am trying to get
    > >>>>> working:
    > >>>>>
    > >>>>> Dim objConn
    > >>>>> Dim strConnect, sql, rs
    > >>>>> Dim strGCId
    > >>>>>
    > >>>>> strGCId = Request.QueryString("gcID")
    > >>>>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > >>>>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    > >>>>>
    > >>>>> Set objConn = Server.CreateObject ("ADODB.Connection")
    > >>>>>
    > >>>>> Set rs = Server.CreateObject ("ADODB.Recordset")
    > >>>>>
    > >>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID
    > >>>>> = g.gcID WHERE c.GCID=" & strGCId
    > >>>>>
    > >>>>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    > >>>>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID="&
    > >>>>> strGCId
    > >>>>>
    > >>>>> RS.Open sql, strConnect, adOpenStatic
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>> response.write "<table>"
    > >>>>>
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    > >>>>> response.write "</tr>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > >>>>> response.write "</tr>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    > >>>>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    > >>>>> response.write "</tr>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > >>>>> RS("WebsiteURL") & "</a><br><br></td>"
    > >>>>> response.write "</tr>"
    > >>>>> response.write "</table>"
    > >>>>>
    > >>>>> response.write "<table border=0>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td width=200><b>Name</b></td><td
    > >>>>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > >>>>> response.write "</tr>"
    > >>>>>
    > >>>>> Do While Not RS.EOF
    > >>>>>
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    > >>>>> RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    > >>>>> response.write "<td valign=top>" & RS("WorkPhone") &
    > >>>>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    > >>>>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    > >>>>>
    > >>>>> response.write "</tr>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    > >>>>> RS("contactEmail") & ">" & RS("contactEmail") &
    > >>>>> "</a><br><br></td>" response.write "</tr>"
    > >>>>> response.write "<tr>"
    > >>>>> response.write "<td colspan=3><hr></td>"
    > >>>>> response.write "</tr>"
    > >>>>>
    > >>>>> RS.MoveNext
    > >>>>> Loop
    > >>>>>
    > >>>>> response.write "</table>"
    > >>>>>
    > >>>>> rs.close
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>> %>
    > >>>>> Bob Barrows [MVP] wrote:
    > >>>>>> This is the second easiest*, and most secure, way I know.
    > >>>>>>
    > >>>>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate the
    > >>>>>> value of
    > >>>>>> the variable into the string. It would work like this, given that
    > >>>>>> GCID has a
    > >>>>>> numeric datatype:
    > >>>>>>
    > >>>>>> sql = " ... WHERE c.GCID = " & strGCID
    > >>>>>> 'for debugging when things go wrong:
    > >>>>>> Response.Write sql
    > >>>>>>
    > >>>>>> To me, thistechnique is more difficult because you have to deal
    > >>>>>> with delimiters, both when forming the sql statement, and also
    > >>>>>> when the data contains characters that are considered to be
    > >>>>>> delimiters by the database engine. A huge percentage of the
    > >>>>>> questions we answer on these groups are a
    > >>>>>> result of the incorrect handling of delimiter characters. Here
    > >>>>>> is one of my
    > >>>>>> older posts where I talk about how to handle delimiters in
    > >>>>>> dynamic sql:
    > >>>>>>
    > >>

    > http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    > >>>>>>
    > >>>>>> The other MAJOR problem with dynamic sql is SQL Injection, which
    > >>>>>> is discussed in these articles:
    > >>>>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    > >>>>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > >>>>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > >>>>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > >>>>>>
    > >>>>>>
    > >>>>>> *The post I cited above shows what I consider to be the easiest
    > >>>>>> way to pass
    > >>>>>> values into sql statements.
    > >>>>>>
    > >>>>>> Matt wrote:
    > >>>>>>> Is there an easier way just to put the variable in the SQL
    > >>>>>>> Select statement?
    > >>>>>>> Thanks
    > >>>>>>>
    > >>>>>>>
    > >>>>>>> Bob Barrows [MVP] wrote:
    > >>>>>>>> Matt wrote:
    > >>>>>>>>> I need to add the following variable into an SQL statement and
    > >>>>>>>>> not sure how to do it.
    > >>>>>>>>>
    > >>>>>>>>> strGCID needs to be inserted into the following statement:
    > >>>>>>>>>
    > >>>>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC
    > >>>>>>>>> ON
    > >>>>>>>>
    > >>>>>>>> http://www.aspfaq.com/show.asp?id=2096
    > >>>>>>>>
    > >>>>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    > >>>>>>>>> (((tblContacts.GCID)=strGCID))"
    > >>>>>>>>>
    > >>>>>>>>> i am just not sure of the proper syntax.
    > >>>>>>>>
    > >>>>>>>> Here is the secure way:
    > >>>>>>>>
    > >>>>>>>>
    > >>>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >>>>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    > >>>>>>>>
    > >>>>>>>> dim arParms
    > >>>>>>>> arParms=array(strGCID)
    > >>>>>>>> dim cmd,rs
    > >>>>>>>> set cmd=createobject("adodb.command")
    > >>>>>>>> cmd.commandtext=sql
    > >>>>>>>> cmd.commandtype=1 'adCmdText
    > >>>>>>>> set cmd.activeconnection=objConn
    > >>>>>>>> set rs = 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"
    > >>>>>>
    > >>>>>> --
    > >>>>>> 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"
    > >>
    > >> --
    > >> 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.

    >
    > --
    > 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.
     
    Matt, Jul 19, 2006
    #12
  13. Maybe you think you are, but your select statement says differently*.

    Select c.* FROM tblContacts c

    will only return fields from tblContacts. Try it in Access and you will
    see.
    BTW, what happened when you tried what I advised two posts ago?



    * Yet another reason to explicitly list all the fields you want to
    return. Stop using selstar.


    Matt wrote:
    > I am returning data from the tblGC, RS("Company") RS("Address1")
    > etc...
    > all the rs's in the first table come from the tblGC.
    >
    >
    >
    > Bob Barrows [MVP] wrote:
    >> Right ... but you're not returning any of the data from tblGC, so why
    >> mention it in the query?
    >>
    >> Matt wrote:
    >>> tblGC contains all of the company information and tblContacts
    >>> contains detailed employee information
    >>>
    >>>
    >>>
    >>>
    >>> Bob Barrows [MVP] wrote:
    >>>> I just notice. Why is tblGC involved in this query at all? It seems
    >>>> to me that
    >>>> SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    >>>> would retrieve the same results ...
    >>>>
    >>>> Open your database in Access, create a new query in Design View,
    >>>> switch to SQL View, paste in the result of the respone.write and
    >>>> try it. Does it return records? If so, try my version above and
    >>>> see if it returns a different set of records.
    >>>>
    >>>>
    >>>> Matt wrote:
    >>>>> Response.write SQL produces:
    >>>>>
    >>>>> SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    >>>>> g.gcID WHERE c.GCID=2582
    >>>>>
    >>>>> which is the corried GCId that I am looking for... but no data is
    >>>>> returned... no error messages, just no data
    >>>>>
    >>>>> Patrice wrote:
    >>>>>> You forgot to mention the problem in your post...
    >>>>>>
    >>>>>> A quick read raises :
    >>>>>>
    >>>>>> I see \\ in the path of the DB. Also depending on which Jet
    >>>>>> engine you are using the JOIN notation could perhaps be
    >>>>>> different or if strGCId is a string you'll have to enclose this
    >>>>>> value within quotes (response.write your SQL statement as
    >>>>>> suggested by Bob).
    >>>>>>
    >>>>>> The error message would be really helpfull.
    >>>>>>
    >>>>>> --
    >>>>>> Patrice
    >>>>>>
    >>>>>> "Matt" <> a écrit dans le message de news:
    >>>>>> ...
    >>>>>>> I am still having problems with this working. Any help is
    >>>>>>> greatly appreciated. Here is the entire piece that I am trying
    >>>>>>> to get working:
    >>>>>>>
    >>>>>>> Dim objConn
    >>>>>>> Dim strConnect, sql, rs
    >>>>>>> Dim strGCId
    >>>>>>>
    >>>>>>> strGCId = Request.QueryString("gcID")
    >>>>>>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    >>>>>>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    >>>>>>>
    >>>>>>> Set objConn = Server.CreateObject ("ADODB.Connection")
    >>>>>>>
    >>>>>>> Set rs = Server.CreateObject ("ADODB.Recordset")
    >>>>>>>
    >>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >>>>>>> c.GCID = g.gcID WHERE c.GCID=" & strGCId
    >>>>>>>
    >>>>>>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    >>>>>>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID="
    >>>>>>> & strGCId
    >>>>>>>
    >>>>>>> RS.Open sql, strConnect, adOpenStatic
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>> response.write "<table>"
    >>>>>>>
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    >>>>>>> response.write "</tr>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    >>>>>>> response.write "</tr>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    >>>>>>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    >>>>>>> response.write "</tr>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    >>>>>>> RS("WebsiteURL") & "</a><br><br></td>"
    >>>>>>> response.write "</tr>"
    >>>>>>> response.write "</table>"
    >>>>>>>
    >>>>>>> response.write "<table border=0>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td width=200><b>Name</b></td><td
    >>>>>>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    >>>>>>> response.write "</tr>"
    >>>>>>>
    >>>>>>> Do While Not RS.EOF
    >>>>>>>
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td valign=top>" & RS("contactFirst") &
    >>>>>>> "&nbsp;" & RS("contactLast") & "<br>" & RS("contactTitle") &
    >>>>>>> "</td>" response.write "<td valign=top>" & RS("WorkPhone") &
    >>>>>>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    >>>>>>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    >>>>>>>
    >>>>>>> response.write "</tr>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    >>>>>>> RS("contactEmail") & ">" & RS("contactEmail") &
    >>>>>>> "</a><br><br></td>" response.write "</tr>"
    >>>>>>> response.write "<tr>"
    >>>>>>> response.write "<td colspan=3><hr></td>"
    >>>>>>> response.write "</tr>"
    >>>>>>>
    >>>>>>> RS.MoveNext
    >>>>>>> Loop
    >>>>>>>
    >>>>>>> response.write "</table>"
    >>>>>>>
    >>>>>>> rs.close
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>> %>
    >>>>>>> Bob Barrows [MVP] wrote:
    >>>>>>>> This is the second easiest*, and most secure, way I know.
    >>>>>>>>
    >>>>>>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate
    >>>>>>>> the value of
    >>>>>>>> the variable into the string. It would work like this, given
    >>>>>>>> that GCID has a
    >>>>>>>> numeric datatype:
    >>>>>>>>
    >>>>>>>> sql = " ... WHERE c.GCID = " & strGCID
    >>>>>>>> 'for debugging when things go wrong:
    >>>>>>>> Response.Write sql
    >>>>>>>>
    >>>>>>>> To me, thistechnique is more difficult because you have to deal
    >>>>>>>> with delimiters, both when forming the sql statement, and also
    >>>>>>>> when the data contains characters that are considered to be
    >>>>>>>> delimiters by the database engine. A huge percentage of the
    >>>>>>>> questions we answer on these groups are a
    >>>>>>>> result of the incorrect handling of delimiter characters. Here
    >>>>>>>> is one of my
    >>>>>>>> older posts where I talk about how to handle delimiters in
    >>>>>>>> dynamic sql:
    >>>>>>>>
    >>>>

    >>

    http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    >>>>>>>>
    >>>>>>>> The other MAJOR problem with dynamic sql is SQL Injection,
    >>>>>>>> which is discussed in these articles:
    >>>>>>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    >>>>>>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >>>>>>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    >>>>>>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    >>>>>>>>
    >>>>>>>>
    >>>>>>>> *The post I cited above shows what I consider to be the easiest
    >>>>>>>> way to pass
    >>>>>>>> values into sql statements.
    >>>>>>>>
    >>>>>>>> Matt wrote:
    >>>>>>>>> Is there an easier way just to put the variable in the SQL
    >>>>>>>>> Select statement?
    >>>>>>>>> Thanks
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>> Bob Barrows [MVP] wrote:
    >>>>>>>>>> Matt wrote:
    >>>>>>>>>>> I need to add the following variable into an SQL statement
    >>>>>>>>>>> and not sure how to do it.
    >>>>>>>>>>>
    >>>>>>>>>>> strGCID needs to be inserted into the following statement:
    >>>>>>>>>>>
    >>>>>>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN
    >>>>>>>>>>> tblGC ON
    >>>>>>>>>>
    >>>>>>>>>> http://www.aspfaq.com/show.asp?id=2096
    >>>>>>>>>>
    >>>>>>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    >>>>>>>>>>> (((tblContacts.GCID)=strGCID))"
    >>>>>>>>>>>
    >>>>>>>>>>> i am just not sure of the proper syntax.
    >>>>>>>>>>
    >>>>>>>>>> Here is the secure way:
    >>>>>>>>>>
    >>>>>>>>>>
    >>>>>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    >>>>>>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    >>>>>>>>>>
    >>>>>>>>>> dim arParms
    >>>>>>>>>> arParms=array(strGCID)
    >>>>>>>>>> dim cmd,rs
    >>>>>>>>>> set cmd=createobject("adodb.command")
    >>>>>>>>>> cmd.commandtext=sql
    >>>>>>>>>> cmd.commandtype=1 'adCmdText
    >>>>>>>>>> set cmd.activeconnection=objConn
    >>>>>>>>>> set rs = 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"
    >>>>>>>>
    >>>>>>>> --
    >>>>>>>> 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"
    >>>>
    >>>> --
    >>>> 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.

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


    --
    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], Jul 19, 2006
    #13
  14. Matt

    Mike Brind Guest

    But your SQL statement only selects every field from tblContacts. You
    haven't selected any fields from any other table at all. Therefore,
    you are not returning data from tblGC. You do have a SQL statement
    that selects from tblGC, but it's commented out...

    --
    Mike Brind

    Matt wrote:
    > I am returning data from the tblGC, RS("Company") RS("Address1") etc...
    > all the rs's in the first table come from the tblGC.
    >
    >
    >
    > Bob Barrows [MVP] wrote:
    > > Right ... but you're not returning any of the data from tblGC, so why
    > > mention it in the query?
    > >
    > > Matt wrote:
    > > > tblGC contains all of the company information and tblContacts contains
    > > > detailed employee information
    > > >
    > > >
    > > >
    > > >
    > > > Bob Barrows [MVP] wrote:
    > > >> I just notice. Why is tblGC involved in this query at all? It seems
    > > >> to me that
    > > >> SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    > > >> would retrieve the same results ...
    > > >>
    > > >> Open your database in Access, create a new query in Design View,
    > > >> switch to SQL View, paste in the result of the respone.write and try
    > > >> it. Does it return records? If so, try my version above and see if
    > > >> it returns a different set of records.
    > > >>
    > > >>
    > > >> Matt wrote:
    > > >>> Response.write SQL produces:
    > > >>>
    > > >>> SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID = g.gcID
    > > >>> WHERE c.GCID=2582
    > > >>>
    > > >>> which is the corried GCId that I am looking for... but no data is
    > > >>> returned... no error messages, just no data
    > > >>>
    > > >>> Patrice wrote:
    > > >>>> You forgot to mention the problem in your post...
    > > >>>>
    > > >>>> A quick read raises :
    > > >>>>
    > > >>>> I see \\ in the path of the DB. Also depending on which Jet engine
    > > >>>> you are using the JOIN notation could perhaps be different or if
    > > >>>> strGCId is a string you'll have to enclose this value within quotes
    > > >>>> (response.write your SQL statement as suggested by Bob).
    > > >>>>
    > > >>>> The error message would be really helpfull.
    > > >>>>
    > > >>>> --
    > > >>>> Patrice
    > > >>>>
    > > >>>> "Matt" <> a écrit dans le message de news:
    > > >>>> ...
    > > >>>>> I am still having problems with this working. Any help is greatly
    > > >>>>> appreciated. Here is the entire piece that I am trying to get
    > > >>>>> working:
    > > >>>>>
    > > >>>>> Dim objConn
    > > >>>>> Dim strConnect, sql, rs
    > > >>>>> Dim strGCId
    > > >>>>>
    > > >>>>> strGCId = Request.QueryString("gcID")
    > > >>>>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > > >>>>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    > > >>>>>
    > > >>>>> Set objConn = Server.CreateObject ("ADODB.Connection")
    > > >>>>>
    > > >>>>> Set rs = Server.CreateObject ("ADODB.Recordset")
    > > >>>>>
    > > >>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID
    > > >>>>> = g.gcID WHERE c.GCID=" & strGCId
    > > >>>>>
    > > >>>>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    > > >>>>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID=" &
    > > >>>>> strGCId
    > > >>>>>
    > > >>>>> RS.Open sql, strConnect, adOpenStatic
    > > >>>>>
    > > >>>>>
    > > >>>>>
    > > >>>>> response.write "<table>"
    > > >>>>>
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    > > >>>>> response.write "</tr>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > > >>>>> response.write "</tr>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    > > >>>>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    > > >>>>> response.write "</tr>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > > >>>>> RS("WebsiteURL") & "</a><br><br></td>"
    > > >>>>> response.write "</tr>"
    > > >>>>> response.write "</table>"
    > > >>>>>
    > > >>>>> response.write "<table border=0>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td width=200><b>Name</b></td><td
    > > >>>>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > > >>>>> response.write "</tr>"
    > > >>>>>
    > > >>>>> Do While Not RS.EOF
    > > >>>>>
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td valign=top>" & RS("contactFirst") & "&nbsp;" &
    > > >>>>> RS("contactLast") & "<br>" & RS("contactTitle") & "</td>"
    > > >>>>> response.write "<td valign=top>" & RS("WorkPhone") &
    > > >>>>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    > > >>>>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    > > >>>>>
    > > >>>>> response.write "</tr>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    > > >>>>> RS("contactEmail") & ">" & RS("contactEmail") &
    > > >>>>> "</a><br><br></td>" response.write "</tr>"
    > > >>>>> response.write "<tr>"
    > > >>>>> response.write "<td colspan=3><hr></td>"
    > > >>>>> response.write "</tr>"
    > > >>>>>
    > > >>>>> RS.MoveNext
    > > >>>>> Loop
    > > >>>>>
    > > >>>>> response.write "</table>"
    > > >>>>>
    > > >>>>> rs.close
    > > >>>>>
    > > >>>>>
    > > >>>>>
    > > >>>>> %>
    > > >>>>> Bob Barrows [MVP] wrote:
    > > >>>>>> This is the second easiest*, and most secure, way I know.
    > > >>>>>>
    > > >>>>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate the
    > > >>>>>> value of
    > > >>>>>> the variable into the string. It would work like this, given that
    > > >>>>>> GCID has a
    > > >>>>>> numeric datatype:
    > > >>>>>>
    > > >>>>>> sql = " ... WHERE c.GCID = " & strGCID
    > > >>>>>> 'for debugging when things go wrong:
    > > >>>>>> Response.Write sql
    > > >>>>>>
    > > >>>>>> To me, thistechnique is more difficult because you have to deal
    > > >>>>>> with delimiters, both when forming the sql statement, and also
    > > >>>>>> when the data contains characters that are considered to be
    > > >>>>>> delimiters by the database engine. A huge percentage of the
    > > >>>>>> questions we answer on these groups are a
    > > >>>>>> result of the incorrect handling of delimiter characters. Here
    > > >>>>>> is one of my
    > > >>>>>> older posts where I talk about how to handle delimiters in
    > > >>>>>> dynamic sql:
    > > >>>>>>
    > > >>

    > > http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    > > >>>>>>
    > > >>>>>> The other MAJOR problem with dynamic sql is SQL Injection, which
    > > >>>>>> is discussed in these articles:
    > > >>>>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    > > >>>>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > > >>>>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > > >>>>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > > >>>>>>
    > > >>>>>>
    > > >>>>>> *The post I cited above shows what I consider to be the easiest
    > > >>>>>> way to pass
    > > >>>>>> values into sql statements.
    > > >>>>>>
    > > >>>>>> Matt wrote:
    > > >>>>>>> Is there an easier way just to put the variable in the SQL
    > > >>>>>>> Select statement?
    > > >>>>>>> Thanks
    > > >>>>>>>
    > > >>>>>>>
    > > >>>>>>> Bob Barrows [MVP] wrote:
    > > >>>>>>>> Matt wrote:
    > > >>>>>>>>> I need to add the following variable into an SQL statement and
    > > >>>>>>>>> not sure how to do it.
    > > >>>>>>>>>
    > > >>>>>>>>> strGCID needs to be inserted into the following statement:
    > > >>>>>>>>>
    > > >>>>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN tblGC
    > > >>>>>>>>> ON
    > > >>>>>>>>
    > > >>>>>>>> http://www.aspfaq.com/show.asp?id=2096
    > > >>>>>>>>
    > > >>>>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    > > >>>>>>>>> (((tblContacts.GCID)=strGCID))"
    > > >>>>>>>>>
    > > >>>>>>>>> i am just not sure of the proper syntax.
    > > >>>>>>>>
    > > >>>>>>>> Here is the secure way:
    > > >>>>>>>>
    > > >>>>>>>>
    > > >>>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > > >>>>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    > > >>>>>>>>
    > > >>>>>>>> dim arParms
    > > >>>>>>>> arParms=array(strGCID)
    > > >>>>>>>> dim cmd,rs
    > > >>>>>>>> set cmd=createobject("adodb.command")
    > > >>>>>>>> cmd.commandtext=sql
    > > >>>>>>>> cmd.commandtype=1 'adCmdText
    > > >>>>>>>> set cmd.activeconnection=objConn
    > > >>>>>>>> set rs = 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"
    > > >>>>>>
    > > >>>>>> --
    > > >>>>>> 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"
    > > >>
    > > >> --
    > > >> 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.

    > >
    > > --
    > > 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.
     
    Mike Brind, Jul 19, 2006
    #14
  15. Matt

    Matt Guest

    I switched over the commented SQL statement and it works now but it
    didn't work before. It was returning and exception error... hmmm. All
    appears to be okay... thanks!

    Bob Barrows [MVP] wrote:
    > Maybe you think you are, but your select statement says differently*.
    >
    > Select c.* FROM tblContacts c
    >
    > will only return fields from tblContacts. Try it in Access and you will
    > see.
    > BTW, what happened when you tried what I advised two posts ago?
    >
    >
    >
    > * Yet another reason to explicitly list all the fields you want to
    > return. Stop using selstar.
    >
    >
    > Matt wrote:
    > > I am returning data from the tblGC, RS("Company") RS("Address1")
    > > etc...
    > > all the rs's in the first table come from the tblGC.
    > >
    > >
    > >
    > > Bob Barrows [MVP] wrote:
    > >> Right ... but you're not returning any of the data from tblGC, so why
    > >> mention it in the query?
    > >>
    > >> Matt wrote:
    > >>> tblGC contains all of the company information and tblContacts
    > >>> contains detailed employee information
    > >>>
    > >>>
    > >>>
    > >>>
    > >>> Bob Barrows [MVP] wrote:
    > >>>> I just notice. Why is tblGC involved in this query at all? It seems
    > >>>> to me that
    > >>>> SELECT c.* FROM tblContacts c WHERE c.GCID=2582
    > >>>> would retrieve the same results ...
    > >>>>
    > >>>> Open your database in Access, create a new query in Design View,
    > >>>> switch to SQL View, paste in the result of the respone.write and
    > >>>> try it. Does it return records? If so, try my version above and
    > >>>> see if it returns a different set of records.
    > >>>>
    > >>>>
    > >>>> Matt wrote:
    > >>>>> Response.write SQL produces:
    > >>>>>
    > >>>>> SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON c.GCID =
    > >>>>> g.gcID WHERE c.GCID=2582
    > >>>>>
    > >>>>> which is the corried GCId that I am looking for... but no data is
    > >>>>> returned... no error messages, just no data
    > >>>>>
    > >>>>> Patrice wrote:
    > >>>>>> You forgot to mention the problem in your post...
    > >>>>>>
    > >>>>>> A quick read raises :
    > >>>>>>
    > >>>>>> I see \\ in the path of the DB. Also depending on which Jet
    > >>>>>> engine you are using the JOIN notation could perhaps be
    > >>>>>> different or if strGCId is a string you'll have to enclose this
    > >>>>>> value within quotes (response.write your SQL statement as
    > >>>>>> suggested by Bob).
    > >>>>>>
    > >>>>>> The error message would be really helpfull.
    > >>>>>>
    > >>>>>> --
    > >>>>>> Patrice
    > >>>>>>
    > >>>>>> "Matt" <> a écrit dans le message de news:
    > >>>>>> ...
    > >>>>>>> I am still having problems with this working. Any help is
    > >>>>>>> greatly appreciated. Here is the entire piece that I am trying
    > >>>>>>> to get working:
    > >>>>>>>
    > >>>>>>> Dim objConn
    > >>>>>>> Dim strConnect, sql, rs
    > >>>>>>> Dim strGCId
    > >>>>>>>
    > >>>>>>> strGCId = Request.QueryString("gcID")
    > >>>>>>> strConnect = "Driver={Microsoft Access Driver (*.mdb)};
    > >>>>>>> DBQ=\\CALSJ1\PMAPPS\contactsData.mdb"
    > >>>>>>>
    > >>>>>>> Set objConn = Server.CreateObject ("ADODB.Connection")
    > >>>>>>>
    > >>>>>>> Set rs = Server.CreateObject ("ADODB.Recordset")
    > >>>>>>>
    > >>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >>>>>>> c.GCID = g.gcID WHERE c.GCID=" & strGCId
    > >>>>>>>
    > >>>>>>> 'SQL="SELECT tblContacts.*, tblGC.* FROM tblContacts INNER JOIN
    > >>>>>>> tblGC ON tblContacts.GCID = tblGC.gcID WHERE tblContacts.GCID="
    > >>>>>>> & strGCId
    > >>>>>>>
    > >>>>>>> RS.Open sql, strConnect, adOpenStatic
    > >>>>>>>
    > >>>>>>>
    > >>>>>>>
    > >>>>>>> response.write "<table>"
    > >>>>>>>
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td><br><h2>" & RS("Company") & "</td>"
    > >>>>>>> response.write "</tr>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td><h4>" & RS("Address1") & "</b></td>" '
    > >>>>>>> response.write "</tr>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td><h4>" & RS("City") & "," & RS("State") &
    > >>>>>>> "&nbsp;" & RS("ZipCode") & "</b></td>"
    > >>>>>>> response.write "</tr>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td><a href=" & RS("WebsiteURL") & ">" &
    > >>>>>>> RS("WebsiteURL") & "</a><br><br></td>"
    > >>>>>>> response.write "</tr>"
    > >>>>>>> response.write "</table>"
    > >>>>>>>
    > >>>>>>> response.write "<table border=0>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td width=200><b>Name</b></td><td
    > >>>>>>> width=150><b>Phone</b></td><td><b>Mobile</b></td>"
    > >>>>>>> response.write "</tr>"
    > >>>>>>>
    > >>>>>>> Do While Not RS.EOF
    > >>>>>>>
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td valign=top>" & RS("contactFirst") &
    > >>>>>>> "&nbsp;" & RS("contactLast") & "<br>" & RS("contactTitle") &
    > >>>>>>> "</td>" response.write "<td valign=top>" & RS("WorkPhone") &
    > >>>>>>> "<br>Ext&nbsp;" & RS("contactPhoneExt") & "</td>"
    > >>>>>>> response.write "<td valign=top>" & RS("contactMobile") & "</td>"
    > >>>>>>>
    > >>>>>>> response.write "</tr>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td colspan=3 valign=top><a href=mailto:" &
    > >>>>>>> RS("contactEmail") & ">" & RS("contactEmail") &
    > >>>>>>> "</a><br><br></td>" response.write "</tr>"
    > >>>>>>> response.write "<tr>"
    > >>>>>>> response.write "<td colspan=3><hr></td>"
    > >>>>>>> response.write "</tr>"
    > >>>>>>>
    > >>>>>>> RS.MoveNext
    > >>>>>>> Loop
    > >>>>>>>
    > >>>>>>> response.write "</table>"
    > >>>>>>>
    > >>>>>>> rs.close
    > >>>>>>>
    > >>>>>>>
    > >>>>>>>
    > >>>>>>> %>
    > >>>>>>> Bob Barrows [MVP] wrote:
    > >>>>>>>> This is the second easiest*, and most secure, way I know.
    > >>>>>>>>
    > >>>>>>>> More difficult (IMO) is to use dynamic sql: i.e., concatenate
    > >>>>>>>> the value of
    > >>>>>>>> the variable into the string. It would work like this, given
    > >>>>>>>> that GCID has a
    > >>>>>>>> numeric datatype:
    > >>>>>>>>
    > >>>>>>>> sql = " ... WHERE c.GCID = " & strGCID
    > >>>>>>>> 'for debugging when things go wrong:
    > >>>>>>>> Response.Write sql
    > >>>>>>>>
    > >>>>>>>> To me, thistechnique is more difficult because you have to deal
    > >>>>>>>> with delimiters, both when forming the sql statement, and also
    > >>>>>>>> when the data contains characters that are considered to be
    > >>>>>>>> delimiters by the database engine. A huge percentage of the
    > >>>>>>>> questions we answer on these groups are a
    > >>>>>>>> result of the incorrect handling of delimiter characters. Here
    > >>>>>>>> is one of my
    > >>>>>>>> older posts where I talk about how to handle delimiters in
    > >>>>>>>> dynamic sql:
    > >>>>>>>>
    > >>>>
    > >>

    > http://groups.google.com/group/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
    > >>>>>>>>
    > >>>>>>>> The other MAJOR problem with dynamic sql is SQL Injection,
    > >>>>>>>> which is discussed in these articles:
    > >>>>>>>> http://mvp.unixwiz.net/techtips/sql-injection.html
    > >>>>>>>> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
    > >>>>>>>> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
    > >>>>>>>> http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
    > >>>>>>>>
    > >>>>>>>>
    > >>>>>>>> *The post I cited above shows what I consider to be the easiest
    > >>>>>>>> way to pass
    > >>>>>>>> values into sql statements.
    > >>>>>>>>
    > >>>>>>>> Matt wrote:
    > >>>>>>>>> Is there an easier way just to put the variable in the SQL
    > >>>>>>>>> Select statement?
    > >>>>>>>>> Thanks
    > >>>>>>>>>
    > >>>>>>>>>
    > >>>>>>>>> Bob Barrows [MVP] wrote:
    > >>>>>>>>>> Matt wrote:
    > >>>>>>>>>>> I need to add the following variable into an SQL statement
    > >>>>>>>>>>> and not sure how to do it.
    > >>>>>>>>>>>
    > >>>>>>>>>>> strGCID needs to be inserted into the following statement:
    > >>>>>>>>>>>
    > >>>>>>>>>>> SQL = "SELECT tblContacts.* FROM tblContacts INNER JOIN
    > >>>>>>>>>>> tblGC ON
    > >>>>>>>>>>
    > >>>>>>>>>> http://www.aspfaq.com/show.asp?id=2096
    > >>>>>>>>>>
    > >>>>>>>>>>> tblContacts.GCID = tblGC.gcID WHERE
    > >>>>>>>>>>> (((tblContacts.GCID)=strGCID))"
    > >>>>>>>>>>>
    > >>>>>>>>>>> i am just not sure of the proper syntax.
    > >>>>>>>>>>
    > >>>>>>>>>> Here is the secure way:
    > >>>>>>>>>>
    > >>>>>>>>>>
    > >>>>>>>>>> SQL = "SELECT c.* FROM tblContacts c INNER JOIN tblGC g ON
    > >>>>>>>>>> c.GCID = g.gcID WHERE c.GCID=?"
    > >>>>>>>>>>
    > >>>>>>>>>> dim arParms
    > >>>>>>>>>> arParms=array(strGCID)
    > >>>>>>>>>> dim cmd,rs
    > >>>>>>>>>> set cmd=createobject("adodb.command")
    > >>>>>>>>>> cmd.commandtext=sql
    > >>>>>>>>>> cmd.commandtype=1 'adCmdText
    > >>>>>>>>>> set cmd.activeconnection=objConn
    > >>>>>>>>>> set rs = 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"
    > >>>>>>>>
    > >>>>>>>> --
    > >>>>>>>> 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"
    > >>>>
    > >>>> --
    > >>>> 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.
    > >>
    > >> --
    > >> 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.

    >
    > --
    > 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.
     
    Matt, Jul 19, 2006
    #15
    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. dna
    Replies:
    1
    Views:
    1,309
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    474
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    479
    David Browne
    Aug 21, 2003
  4. Froefel
    Replies:
    1
    Views:
    801
    Mark Rae [MVP]
    Jul 4, 2007
  5. weiwei

    asp and sql statement in sql server db

    weiwei, Sep 22, 2004, in forum: ASP General
    Replies:
    3
    Views:
    219
    Jeff Cochran
    Sep 22, 2004
Loading...

Share This Page