SQL Server Parameters

[jamesclarke112]

I have no problem with the coding of sql parameters, but could someone give me an explanation or a good link of why I should use them.

Consider if I had a variable called

dim strName as String =  "John"

I could then do either

sqlCmd.CommandText = "SELECT * FROM contacts WHERE name = '" & strName & "'"

or

sqlCmd.CommandText = "SELECT * FROM contacts WHERE name = @name"
sqlCmd.Parameters.Add(New SqlCeParameter("@name", strName))

Thankyou

[jmcilhinney]

Increase readability and reduce the chance of errors. Try writing an SQL statement where you update 20 columns with string concatenation.

Increased security. A clever user might enter a value that is legitimate SQL code that wipes out your entire database. In the example above, see what happens if strName contains the following:

"SomeName'; DELETE FROM Table1; SELECT * FROM contacts WHERE name = 'SomeName"

You'd end with:

"SELECT * FROM contacts WHERE name = 'SomeName'; DELETE FROM Table1; SELECT * FROM contacts WHERE name = 'SomeName'"

and you'd delete every record from Table1 and you'd have no idea why. If you use a parameter then the value doesn't become part of the actual statement so there's no risk of this type of SQL injection attack.

[jamesclarke112]

Thanks for the info. Now I have a coding question

I have a 2 queries each with around 10 paramaters. The second query shares about 8 ofthese parameters with the other 2 changing. Currently after the first query I am doing

sqlCmd.Parameters.Clear()

and then adding them all back in (with the 2 different ones) again. Is there a way of clearing just the 2 parameters and kepping the others.

Thanks

[techgnome]

Parameters.Remove .... should do the trick.... although I've always re-created the command object and parameters everytime, so I don't know for sure that it'll work.

-tg

[jmcilhinney]

The Parameters property is just a collection, so it essentially works like any other collection. Add, Remove and Clear are standard functionality for a collection because they are members if the IList interface, that pretty much every collection implements.