Writing Query for accessing information in a database is one of the most
frequently tasks that ASP programmers do. In here, I will introduce you a less
known technique by ASP programmers that's called Parameterized Query. But,
Firstly let's we recap our old habit when doing query to a database.
Do you Remember that there's a rule of thumb when building your query? You
have to use query mark as a sign for your data. For example take a look at the
snippet code below. This is a simple string query that's define select query to
an Employees table in a Northwind database: (Note that I don't write the data
access code, I assume most of you know it)
sEmpId = 1
sHireDate = "1
sCountry = "USA"
"SELECT FirstName, LastName, Title FROM Employees WHERE ((EmployeeID >"
& sEmpId & " AND HireDate >#" & sHireDate & #) AND Country =
'" & sCountry & "')"
In the above query I define a string query in a variable, named strSql. This
time I want to build dynamic sql query, i.e. I embedded some variables whose
values may come from user input (already put in the sEmpId, sHireDate, and
sCountry variables). Notice that for variable which is a string type (sCountry),
I have to enclose it with an apostrophe sign (') and for date-time variable type
(sHireDate), I have to enclose it with a pound sign (#) and for numeric type
variable (sEmpId) you have no special mark sign for it. If you hardcoded the
value into your string query, you still have to follow these rules. As I said
before that this is a rule of thumb, so you have to follow it or your query
won't work at all!
So What's wrong with this? First, you have to remember all the signs
for the appropriate data type and you have to put it rightly (enclosed it in a
well-formed manner, also be careful of spaces between the sign and query
keywords). Next, the other bad thing is these sign markers are different for
each database! If you rewrite that query for MS SQL Server database then the
query mark sign for date-time type variable is not a pound sign (#) but an
aposthrope (') So not only you have to remember diffent sign for different data
type, you also have to remember the signs for different database type. Not just
that ... the worst beast here is when you have to build a complex query, that's
made up over ten lines or even hundred of lines, then you will have difficulty
to read such a query which mixed up with all those signs. Also you tend to have
trouble when you want to fix it. And the last thing, since we use many special
signs so you must always remember to escape your data from query mark signs. For
instance, if one of your data contains an aposthrope (') within it, you have to
escape it and most ASP Programmers do like this one:
sCountry = "USA'" 'Notice at an aposthrope at the end - this is not
sCountry = Replace(sCountry,"'","''") 'So Escape it by Replacing
all single aposthrope with double aposthrope
Now, Comes Parameterized Query. To get rid of all those
trouble maker, we can utilize ADO Parameterized Query feature. It's very easy and can also make your
life easier. The parameterized query is simply just a query that's embedded with
one or more parameters and the sign for each parameter is a question
mark (?). Later, we will associate all the parameters with the actual value we needed.
Below is the demonstration of parameterized query (available for download) and I still use the
above query. This time, I write it in full code and add a little stuff. I demonstrate how to use a select query
and an updateable query type. Note that if you can't read the code in html version below, please download
the accompanying file (full documented), you will be more comfortable to read it using your favorite editor.
Dim oCmd, oRs, sSQL, sEmpId, sHireDate, sCountry, sCompName, sPhone, iShipperId
Dim sCnnString, sDBPath, iRec
'This is a simple script that demonstrates the harness and easiness of parameterized query
'The First one demonstrates select query operation
'The Last demonstrates update query operation
'Define connection string, the database file is located at the same directory with the script
'The database file is NWind.mdb (MS Access type, available
if you install Visual Studio):
sDBPath = Server.MapPath("NWind.mdb")
sCnnString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & sDBPath
'Populate a Command Object
set oCmd = server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = sCnnString
'Demo1: Select Query Operation
'Define Input values
'For simplicity sake, I hardcoded these values.
sEmpId = 1
sHireDate = "1 January 1993"
sCountry = "USA"
'Define Our Query in a variable (sSQL)
'Notice that we get rid of query signs such as quote (') or pound (#)
'We just use question mark (?) as the placeholder for all the input values, no matter what the type it is.
sSQL = "SELECT FirstName,
LastName, Title FROM Employees " & _
" WHERE (EmployeeID
> ? AND HireDate > ? AND Country =
?)" < BR
'Put our query in the command object and Invoke Execute method to get the recordset
'We Associate the query parameter with the real values in the array function.
'If you wish you can use a safe array type variable instead of array function
'Remember that put your values in the same order as you defined the parameter(?) in your query.
'Notice that in here we call Execute method with parentheses (as function) since we will get the return value,ie the recordset object
oCmd.CommandText = sSQL
set oRs = oCmd.Execute (,Array(sEmpId,sHireDate,sCountry))
'Display a header
'Loop over the recordset and print out each value
do while not oRs.EOF
Response.Write oRs(0) & "
" & oRs(1) & " - " & oRs(2) & "<BR>"
'Clean up Recordset Object
set oRs = nothing
'Demo2: Update Query operation
'Define input values
sCompName = "Max's Express" 'Notice at the single aposthrope - we don't have to escape it!
sPhone = "(503)505-1001"
iShipperId = 2
'This is just a dummy update operation
sSQL = "UPDATE Shippers SET
CompanyName =?, Phone = ? WHERE ShipperId > ?"
'Put the query into command object
'Invoke Execute method to run the query
'We pass a variable that will hold the number of successful operation and an array function to associate our parameter with real values
'Notice that since we just run update type query(no return values) so we don't use parentheses.
oCmd.CommandText = sSQL
'Display Message indicated the number of sucessful update operations
Response.Write ("<H4>" & CStr(iRec) & " Records Successfully Updated</H4>")
set oCmd = nothing
'Last thing to ponder:
'If you change the database to other types which supports
parameterized query, such as SQL Server,
..you don't have to change any of your query and code!
Well..., Easy isn't it? You don't have to remember many signs for different
data types, all you have to do is just remembering a question mark sign (?) and
this sign is consistent regardless what database type you use. You also don't
have to do escape for mark signs. Your query is also more readable and
maintainable, even if your query lines are more than ten lines, it's still
easier and make sense to read it.
So..Ready to make your life a bit easier? Parameterized your Query!