variable in SQL statement

[Matt]

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.

 

[Bob Barrows [MVP]]

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)

 

[Matt]

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

 

[Patrice]

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

 

[Bob Barrows [MVP]]

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]

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



%>

 

[Patrice]

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.

 

[Matt]

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

 

[Bob Barrows [MVP]]

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]

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

 

[Bob Barrows [MVP]]

Right ... but you're not returning any of the data from tblGC, so why
mention it in the query?

 

[Matt]

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

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.

 

[Mike Brind]

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

 

[Matt]

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!