| | |
| | |
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. | |
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 demonstrat
' es the harness and easiness of parameter
' ized query
'The First one demonstrates select query
' operation
'The Last demonstrates update query oper
' ation
'Define connection string, the database
' file is located at the same directory wi
' th the script
'The database file is NWind.mdb (MS Acce
' ss 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 s
' uch as quote (') or pound (#)
'We just use question mark (?) as the pl
' aceholder for all the input values, no m
' atter 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 t
' he real values in the array function.
'If you wish you can use a safe array ty
' pe variable instead of array function
'Remember that put your values in the sa
' me order as you defined the parameter(?)
' in your query.
'Notice that in here we call Execute met
' hod with parentheses (as function) since
' we will get the return value,ie the reco
' rdset 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<
' BR>
'We pass a variable that will hold the n
' umber of successful operation and an arr
' ay function to associate our parameter w
' ith real values
'Notice that since we just run update ty
' pe query(no return values) so we don't u
' se 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 typ
' es 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!
| |  Download 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 Winzipto 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
|
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. |
Other 3 submission(s) by this author
| | | | | Report Bad Submission | | | | | Your Vote! | |
See Voting Log | | | | Other User Comments | 11/18/2001 5:41:23 PM:Mark Focas
This code does not work using SQL
Server 7 or Access2000 on windows 2000.
It comes up with the following
error:
No value given for one or more
required parameters
| 11/28/2001 1:04:57 AM:Hendry Cahyadi
Ooops I'm too late..
Mark
Focas,
I tested my script on Win2000
Professional and my script works well
to either SQL Server 7 or
Access2000/XP
The error message that
you got usually comes up when you
forget to associate the value to the
parameter or you define the number of
parameters more than the number of the
actual values.
Please let me know if
you need such more help, I would please
to help you.
| 3/29/2002 9:20:25 AM:Tom Roberts
Demo1 works great, however I can not
get demo2 working. I get an ASP error
when executing the line oCmd.Execute
iRec,Array(sCompName,sPhone,iShipperId).
Here is the error I am
getting:
Error Type:
Microsoft JET
Database Engine (0x80004005)
Operation
must use an updateable query.
Any
idea's on how to fix this would be
greatly appretiated.
Thanks
| 4/17/2002 3:55:48 AM:Alain Chang
The ADODB.command method seems to limit
strings to less than 255 chars. So it
doesn't work well to fill memo type
entries. Too bad. How else can you
escape double quotes in an entry larger
than 255 chars? Thanks, Newbie1
| | | Add Your Feedback! | Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
| |
|
