By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,321 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,321 IT Pros & Developers. It's quick & easy.

variable in SQL statement

P: n/a
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.

Jul 18 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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"
Jul 18 '06 #2

P: n/a
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"
Jul 18 '06 #3

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i3g2000cwc.googlegroups.co m...
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"

Jul 19 '06 #4

P: n/a
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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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"
Jul 19 '06 #5

P: n/a
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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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"
Jul 19 '06 #6

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
>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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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"

Jul 19 '06 #7

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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"
Jul 19 '06 #8

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
>>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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #9

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #10

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
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/micro...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/adva..._injection.pdf
>http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #11

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
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/micro...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/adva..._injection.pdf
http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #12

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
>>>11*********************@i42g2000cwa.googlegroups.c om...
>>>>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/micro...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/adva..._injection.pdf
>>>>>http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #13

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
>11*********************@i42g2000cwa.googlegroups.c om...
>>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/micro...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/adva..._injection.pdf
>>>http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #14

P: n/a
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" <mw******@caldrywall.coma écrit dans le message de news:
11*********************@i42g2000cwa.googlegroups.c om...
>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/micro...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/adva..._injection.pdf
>>http://www.nextgenss.com/papers/more..._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.
Jul 19 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.