parameterised queries vb - SQL Server Forums

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.

All Forums

Development Tools

Other Development Tools

parameterised queries vb

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

Posted - 2008-05-20 : 11:21:59

I have asked this question in another forum and have some more answers there:
http://www.dbforums.com/showthread.php?p=6339230#post6339230

Thanks!

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