Classic ASP: Using parameters -- Help needed please!

[Joey Martin]

2 questions. Obviously I am new to using parameters. I am switching over
all my old hackable sql queries to something more secure.

So, let me ask something.

I have something like
select * from table where name=? and email=?

How do I do a LIKE statement? I tried name like ? but my search results
did not work.


Also....

let's say I have
sql=select * from table where name=?
if strCity<> "" then
sql=sql & city=?
end if
if strState<> "" then
sql=sql & state=?
end if

How do I set the Parameters?
Typically I would do something like
Set param1 = objCommand.CreateParameter ("name", adVarChar,
adParamInput, 50)
param1.value = strName
objCommand.Parameters.Append param1
Set param2 = objCommand.CreateParameter ("city", adVarChar,
adParamInput, 50)
param2.value = strCity
objCommand.Parameters.Append param2
Set param3 = objCommand.CreateParameter ("state", adVarChar,
adParamInput, 50)
param3.value = strState
objCommand.Parameters.Append param3



But since if a user does not enter anything as CITY that part of the
querystring will be ignored, wouldn't it mess up which parameter was
associated with each part of the querystring? Does that make sense?

In my case above, if both City and State were submitted, Name would be
Param1, City would be Param2 and State would be Param3. But, if city is
not entered, I would only need 2 parameters but STATE will have been
assigned as Param3


I hope someone can make some sense out of all this.

Thanks!!

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Post by Joey Martin
2 questions. Obviously I am new to using parameters. I am switching over
all my old hackable sql queries to something more secure.
So, let me ask something.
I have something like
select * from table where name=? and email=?
How do I do a LIKE statement? I tried name like ? but my search results
did not work.

Show us the code, and we may be able to tell you. It should not make any
difference.

Post by Joey Martin
let's say I have
sql=select * from table where name=?
if strCity<> "" then
sql=sql & city=?
end if
if strState<> "" then
sql=sql & state=?
end if
How do I set the Parameters?
Typically I would do something like
Set param1 = objCommand.CreateParameter ("name", adVarChar,
adParamInput, 50)
param1.value = strName
objCommand.Parameters.Append param1
Set param2 = objCommand.CreateParameter ("city", adVarChar,
adParamInput, 50)
param2.value = strCity
objCommand.Parameters.Append param2
Set param3 = objCommand.CreateParameter ("state", adVarChar,
adParamInput, 50)
param3.value = strState
objCommand.Parameters.Append param3
But since if a user does not enter anything as CITY that part of the
querystring will be ignored, wouldn't it mess up which parameter was
associated with each part of the querystring? Does that make sense?

I think the best is to add the parameters as you jog along:

if strCity<> "" then
sql=sql & city=?
Set param2 = objCommand.CreateParameter ("city", adVarChar, adParamInput, 50)
param2.value = strCity
objCommand.Parameters.Append param2
end if



--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Joey Martin]

I think the second part of my question was answered, so I'll stick with
the first part.

This works:
select * from table where member=? and pw=?

But, how does I use LIKE instead of =
when I am using these parameters. The rest of the code I am find with.
It's just this query section.



*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Post by Joey Martin
I think the second part of my question was answered, so I'll stick with
the first part.
select * from table where member=? and pw=?
But, how does I use LIKE instead of =
when I am using these parameters. The rest of the code I am find with.
It's just this query section.

You use LIKE just like any other operator. If you have code that does
not work, please post it, so we can spot what the problem is.

[Joey Martin]

I have no problem specifying the query as

where email=?

but I cannot get "where area like ?" to work. For example if someone
enter information into a text box and I want to search the AREA field
where the result is LIKE what they typed in.




*** Sent via Developersdex http://www.developersdex.com ***

[Joey Martin]

Disregard.

I figured it out myself...

like '%' + ? + '%'


*** Sent via Developersdex http://www.developersdex.com ***