Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2008-05-19 : 09:27:59
|
Hello,
I am using ADO 2.8 and am trying to pass parameters to a query I have written. I am using a parameterised query as the parameter comes from user input, so this will protect is against SQL injection. The code is:
-----------------------------------------------------
Dim Cmd As ADODB.Command
Dim para As ADODB.Parameter
Set Cmd = New ADODB.Command
Cmd.CommandType = adCmdText
Cmd.NamedParameters = True
Cmd.CommandText = "" & _
"SELECT PersonName " & _
"FROM People " & _
"WHERE PersonName LIKE @PersonLike"
Set personlike = New ADODB.Parameter
personlike.Name = "@PersonLike"
personlike.Type = adVarChar
personlike.Size = 255
personlike.Value = Me.TextBox1 'User Input
personlike.Direction = adParamInput
Cmd.Parameters.Append personlike
Rs.Open Source:=Cmd, CursorType:=adOpenDynamic, LockType:=adLockReadOnly
----------------------------------------------------------------------
The problem I encounter is that I am told the parameter @PersonLike needs to be declared, and the only way I get around it is changing "@PersonLike" to "?" in the SQL statement. For this query, this isn't too much of an issue, but for more complex queries with several parameters, using a question mark for each on will make the SQL difficult to read and maintain.
Does anyone know how I can link parameters to their position in the SQL statement by name?
Thanks,
Mike |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-19 : 09:39:22
|
A hint:
Use stored procedures with parameters. Do never use concatenated string to communicate with the database.
E 12°55'05.25"
N 56°04'39.16" |
 |
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-05-19 : 10:23:38
|
You don't have to use stored procedures, I would rather store the SQL in the application. In fact, there's no real benifit in using stored procedures, it's more of a preference where you'd rather store the SQL. I'm not using concantented strings as user input is passed as a parameter still.
Thanks |
|
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2008-05-19 : 11:09:18
|
| just a quick thought @PersonLike vs @GroupLike ... try using the same parameter name in the sql definitiion and the parameter object ... might explain why the ? worked |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-05-20 : 05:02:24
|
| Sorry, that was a typo. I was changing my query to make it more general and forgot to change that. I have edited the orginal post now. |
|
|
|
michael.appleton
Posting Yak Master
160 Posts |
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-05-20 : 11:23:04
|
And also for a more VB orientated answer:
http://www.vbforums.com/showthread.php?p=3231936#post3231936 |
|
|
|
|
|
|
parameterised queries vb