CODEWITHSUNDEEP
asp.netasp-classic
TheCarver
TheCarver

Reputation: 19723

ASP / How do parameterized queries work?

Recently I have been writing parameterized queries like this:

SQL = " INSERT INTO myTable (column1, column2, column3) VALUES (?, ?, ?); "

Set newParameter = cmdConn.CreateParameter("@column1", ad_Integer, ad_ParamInput, Len(input1), input1)
cmdConn.Parameters.Append newParameter
Set newParameter = cmdConn.CreateParameter("@column2", ad_Integer, ad_ParamInput, Len(input2), input2)
cmdConn.Parameters.Append newParameter
Set newParameter = cmdConn.CreateParameter("@column3", ad_Integer, ad_ParamInput, Len(input3), input3)
cmdConn.Parameters.Append newParameter

And I was of the understanding, that where I've used @column1 when appending a new parameter, this was allocating that parameter to that particular column in the query... but have recently been informed otherwise. This then caused some confusion and have a couple of questions.

Take the above parameter, @column1. How does that parameter know that it's going to be used in the first question mark (?) of the query? Do I have to append the parameters in the same order as the question marks, or doesn't it matter which order? Also, if I wanted to add the users input in to two columns, do I use two parameters or can I use the same one?

Any help gratefully received. This has been on my mind for weeks, so really looking forward to an explanation :)

Upvotes: 0

Views: 708

Answers (4)

Stephen Quan
Stephen Quan

Reputation: 26179

When you use the following, you haven't bound the parameters by name, so they must be populated by their ordinal position, but, it was luckily that you bound the name parameters in the right order:

SQL = " INSERT INTO myTable (column1, column2, column3) VALUES (?, ?, ?); "

When you bind parameters by name, you can do tricksy things like reuse them, saving time and memory:

SQL = " INSERT INTO myTable (column1, column2, column3) VALUES (@a, @b, @a); "

Upvotes: 1

Michiel van Oosterhout
Michiel van Oosterhout

Reputation: 23094

Also note that you can use named parameters in which case the order in which you append the parameters no longer matters, since they are matched by name. In ADO.NET using the SQL Server provider:

Dim CommandText as String = "SELECT * FROM Table WHERE ColumnA = @A AND ColumnB = @B"
Dim command As New SqlCommand(CommandText)

''#Parameters added in reverse order, still works

command.Parameters.Add(new SqlParameter("@B", 42))
command.Parameters.Add(new SqlParameter("@A", "Test"))

But this doesn't work with the OLE DB .NET Provider:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Upvotes: 1

Emmanuel N
Emmanuel N

Reputation: 7449

In parameterized queries, the database replaces the question marks with the parameters in the order in which they are added.

So column1 parameter will go into question mark 1, column2 parameter will go into question mark 2, and column3 parameter will go into question mark 3

For more check

Upvotes: 5

Matt
Matt

Reputation: 4795

If you are using question marks, parametrs must be appended to the Parameters collection in the order in which you want them to be used in your query.

Use Append to add the other parameters into the Parameters collection in the order of definition. Source: ADO collections documentation, number 2

Each parameter in the query requires one parameter to be appended to the Parameters collection. In your example query, the 3 parameters in your query, be they user input or not, mean that 3 parameters must exist in the Parameters collection, and they must have been appended in the order in which they appear in the query string.

Upvotes: 1

Related Questions