ASP Tip: Simplify Your Database Query

				
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 
January 1993" 
sCountry = "USA" 
strSql = 
"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 
allowed...
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.

<%
Option Explicit 

 


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 
Response.Write 
("<H3>Query Results:</H3>") 


 
'Loop over the recordset and print out each value

do while not oRs.EOF
 
    Response.Write oRs(0) & " 
" & oRs(1) & " - " & oRs(2) & "<BR>"
 
   oRs.MoveNext

loop 



'Clean up Recordset Object
oRs.Close

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 

oCmd.Execute iRec,Array(sCompName,sPhone,iShipperId)


'Display Message indicated the number of sucessful update operations

Response.Write ("<H4>" & CStr(iRec) & " Records Successfully Updated</H4>")



'Clean up 
Command Object

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!