Using variable in SELECT statement

I'm pretty new to asp but have coded before (many years ago) and
understand databases. I am trying to use a variable in a SELECT
statement, which I have done elsewhere in the same script, but can't
get this particular bit to work.

I'm retrieving a property record and then picking up a photo for each
property record I find. This code works:


'Set query string to extract just records with appropriate property
type
propstring1 = "'" & Session("lsproptype") & "'"
propstring2 = "SELECT * FROM property WHERE PropType = " & propstring1
proprs.Open propstring2, conn

' Loop through records found and display
DO UNTIL proprs.EOF
Set photors = conn.Execute ("SELECT Photopath1 FROM photopath WHERE
PropID = 16")
proprs.MoveNext
LOOP


But if I try to amend the SELECT in the loop to use a variable instead
of the constant "16", I have problems. I've also tried other ways of
writing the statement with the constant in to help get the syntax right
but no luck. Here's what I've tried so far:


'This statement does not work
photostring = "SELECT Photopath1 FROM photopath WHERE PropID = 16"
Set photors = conn.Execute photostring

'Neither does this using either of the photostring2 options
photostring1 = proprs("PropertyID")
photostring2 = "SELECT Photopath1 FROM photopath WHERE PropID = " &
(photostring1)
photostring2 = "SELECT Photopath1 FROM photopath WHERE PropID = 16"
photors.Open photostring2, conn

'Neither does this
photostring = "(" & "SELECT Photopath1 FROM photopath WHERE PropID =
16" & ")"
Set photors = conn.Execute photostring

'Neither does this
Set photors = conn.Execute _
('SELECT Photopath1 FROM photopath WHERE PropID = 16')

'Neither does this
Set photors = conn.Execute ("SELECT Photopath1 FROM photopath WHERE
PropID = '" & proprs("PropertyID") & "'")

'Neither does this
Set photors = conn.Execute ("SELECT Photopath1 FROM photopath WHERE
PropID = " & PropertyID)

'Neither does this
photoid = proprs("PropertyID")
Set photors = conn.Execute ("SELECT Photopath1 FROM photopath WHERE
PropID = " & photoid)


Any help very welcome - don't know what else to try!

Re: Using variable in SELECT statement

Shas wrote:
> I'm pretty new to asp but have coded before (many years ago) and
> understand databases.

So what database are you using?

> I am trying to use a variable in a SELECT
> statement, which I have done elsewhere in the same script, but can't
> get this particular bit to work.
>
> I'm retrieving a property record and then picking up a photo for each
> property record I find. This code works:
>
>
> 'Set query string to extract just records with appropriate property
> type
> propstring1 = "'" & Session("lsproptype") & "'"
> propstring2 = "SELECT * FROM property WHERE PropType = " & propstring1
> proprs.Open propstring2, conn
>
Ughhh! Dynamic sql!

Read:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
or
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Then read these for alternatives that are safe from sql injection attacks:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

Using Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

SQL Server
http://tinyurl.com/jyy0

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

Re: Using variable in SELECT statement

Thanks Bob, that was really useful. The parameterised SQL works really
well (I'm using MS Access) and it's so much simpler. I'm now going
through all the asp scripts I've created so far and changing to this
method.
Shas.