Graph Paper

Upload
<FONT FACE="arial,verdana" SIZE="2">
<P>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. </P>
<P>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) </P>
<P><CODE STYLE="FONT-SIZE: 10pt; BACKGROUND-COLOR: #e0e0e0"><SPAN STYLE="BACKGROUND-COLOR: yellow">&lt;%</SPAN><BR>... <BR>sEmpId = 1<BR> sHireDate = "1 
January 1993" <BR>sCountry = "USA" <BR>strSql = 
"SELECT FirstName, LastName, Title FROM Employees WHERE ((EmployeeID &gt;"&nbsp; 
&amp; sEmpId &amp; " AND HireDate &gt;#" &amp; sHireDate &amp; #) AND Country =  
'" &amp; sCountry &amp; "')"  
<BR> ... 
<BR>
<SPAN STYLE="BACKGROUND-COLOR: yellow">%&gt;</SPAN></CODE></P>
<P>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!</P>
<P>&nbsp;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: </P>
<P><CODE STYLE="FONT-SIZE: 10pt; BACKGROUND-COLOR: #e0e0e0">sCountry = "USA'" <SPAN STYLE="COLOR: green">'Notice at an aposthrope at the end - this is not 
allowed...</SPAN><BR>sCountry = Replace(sCountry,"'","''") <SPAN STYLE="COLOR: green">'So Escape it by Replacing 
all single aposthrope with double aposthrope</SPAN></CODE></P>
<P>  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&nbsp;is the demonstration of parameterized query&nbsp;(available for download)&nbsp; 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&nbsp;the code in html version below,&nbsp;please download 
the accompanying file (full documented), you will be more comfortable to&nbsp;read it&nbsp;using your&nbsp;favorite editor.</P>
<CODE STYLE="FONT-SIZE: 10pt; BACKGROUND-COLOR: #e0e0e0">
<P><SPAN STYLE="BACKGROUND-COLOR: yellow">&lt;%</SPAN></P>Option Explicit <BR>
 
<BR>
Dim oCmd, oRs, sSQL, sEmpId, sHireDate, sCountry, sCompName, sPhone, iShipperId
<BR>Dim sCnnString, sDBPath, iRec <BR>
  
<BR>
<SPAN STYLE="COLOR: green">
'This is a simple script that demonstrates the harness and easiness of parameterized query
<BR>
'The First one demonstrates select query operation
<BR>
'The Last demonstrates update query operation
<BR><BR>
'Define connection string, the database file is located at the same directory with the script
<BR>
'The database file is NWind.mdb (MS Access type, available 
if you install Visual Studio): <BR>
       
</SPAN>
sDBPath = Server.MapPath("NWind.mdb")
<BR>
sCnnString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" &amp; sDBPath
<BR>
<SPAN STYLE="COLOR: green"><BR>'Populate a Command Object</SPAN>
<BR>
set oCmd = server.CreateObject("ADODB.Command")
<BR>
oCmd.ActiveConnection = sCnnString
<BR>
<SPAN STYLE="COLOR: green"><BR>'Demo1: Select Query Operation
<BR>
'Define Input values
<BR>
'For simplicity sake, I hardcoded these values.</SPAN>
<BR>
sEmpId = 1 <BR>
sHireDate = "1 January 1993" <BR>
sCountry = "USA" <BR>
<SPAN STYLE="COLOR: green"><BR>'Define Our Query in a variable (sSQL) <BR>
'Notice that we get rid of query signs such as quote (') or pound (#) <BR>
'We just use question mark (?) as the placeholder for all the input values, no matter what the type it is. <BR>
</SPAN>sSQL = "SELECT FirstName, 
LastName, Title FROM Employees " &amp; _ 
<BR >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " WHERE (EmployeeID 
&gt; ? AND HireDate &gt; ? AND Country =  
   ?)" &lt; BR    
      
	     &gt;    
<SPAN STYLE="COLOR: green"><BR>'Put our query in the command object and Invoke Execute method to get the recordset
<BR>
'We Associate the query parameter with the real values in the array function. 
<BR>
'If you wish you can use a safe array type variable instead of array function 
<BR>
'Remember that put your values in the same order as you defined the parameter(?) in your query.
<BR>
'Notice that in here we call Execute method with parentheses (as function) since we will get the return value,ie the recordset object<BR>
</SPAN>
oCmd.CommandText = sSQL
<BR>
set oRs = oCmd.Execute (,Array(sEmpId,sHireDate,sCountry))
<BR>
<SPAN STYLE="COLOR: green"><BR>'Display a header</SPAN> <BR>Response.Write 
("&lt;H3&gt;Query Results:&lt;/H3&gt;") <BR><BR>
 
<SPAN STYLE="COLOR: green">'Loop over the recordset and print out each value</SPAN>
<BR>do while not oRs.EOF
 <BR> &nbsp;&nbsp; Response.Write oRs(0) &amp; " 
" &amp; oRs(1) &amp; " - " &amp; oRs(2) &amp; "&lt;BR&gt;"
 <BR>&nbsp;&nbsp; oRs.MoveNext
<BR>loop 
<BR><BR>
<SPAN STYLE="COLOR: green">'Clean up Recordset Object
oRs.Close<BR>
set oRs = nothing<BR>
<BR>'Demo2: Update Query operation
'Define input values</SPAN>
<BR>
sCompName = "Max's Express" <SPAN STYLE="COLOR: green">'Notice at the single aposthrope - we don't have to escape it!</SPAN><BR>
sPhone = "(503)505-1001"<BR>
iShipperId = 2<BR>
<SPAN STYLE="COLOR: green"><BR>'This is just a dummy update operation</SPAN><BR>
sSQL = "UPDATE Shippers SET 
CompanyName =  ?, Phone = ? WHERE ShipperId &gt; ?"<BR>
<SPAN STYLE="COLOR: green"><BR>'Put the query into command object<BR>
'Invoke Execute method to run the query<BR>
'We pass a variable that will hold the number of successful operation and an array function to associate our parameter with real values<BR>
'Notice that since we just run update type query(no return values) so we don't use parentheses.<BR>
</SPAN>
oCmd.CommandText = sSQL <BR>
oCmd.Execute iRec,Array(sCompName,sPhone,iShipperId)<BR>
<SPAN STYLE="COLOR: green"><BR>'Display Message indicated the number of sucessful update operations</SPAN><BR>
Response.Write ("&lt;H4&gt;" &amp; CStr(iRec) &amp; " Records Successfully Updated&lt;/H4&gt;")<BR>
<BR>
<SPAN STYLE="COLOR: green">'Clean up 
Command Object</SPAN>
<BR>set oCmd = nothing <BR><BR>
<SPAN STYLE="COLOR: green">'Last thing to ponder: <BR>
'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!</SPAN>

<P><SPAN STYLE="BACKGROUND-COLOR: yellow">%&gt;</SPAN></P>
</CODE>
<P>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. </P>
<P>So..Ready to make your life a bit easier? Parameterized your Query! 
</P>
</FONT>