article

ASP Tip: Simplify Your Database Query

Email
Submitted on: 1/5/2015 8:39:00 PM
By: Hendry Cahyadi (from psc cd)  
Level: Intermediate
User Rating: By 9 Users
Compatibility: ASP (Active Server Pages)
Views: 2357
 
     I will introduce you a less known technique to access a database using ASP. Really simplified your development and saves your time much! Just Read on...

This article has accompanying files
 
				

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!

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Other 3 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.