Parameter queries in Access are usually used to provide users of Access database applications with a way of customising the output of a query, by prompting them for their input. In ASP applications, they offer a number of benefits:
They are considerably faster to code
Consider the following as a typical example of dynamic SQL for creating a new record from user input:
strUpdateCompanySQL = "INSERT INTO Addresses " & _
"(CompanyName, Address1, Address2, Address3, " & _
"City, State, CountryID, Zip, SwitchboardNo, FaxNo," & _
"Email, Website, RecordStatus, LastUpdated) VALUES (" & _
"'" & ChkString(Request.Form("CompanyName")) & "'," & _
"'" & ChkString(Request.Form("address1")) & "'," & _
"'" & ChkString(Request.Form("address2")) & "'," & _
"'" & ChkString(Request.Form("address3")) & "'," & _
"'" & ChkString(Request.Form("city")) & "'," & _
"'" & ChkString(Request.form("state")) & "'," & _
"'" & Request.Form("countryid") & "'," & _
"'" & ChkString(Request.Form("zip")) & "'," & _
"'" & Request.Form("switchboardno") & "'," & _
"'" & Request.Form("faxno") & "'," & _
"'" & ChkString(Request.Form("email")) & "'," & _
"'" & ChkString(Request.Form("website")) & "'," & _
-1 & "," & _
"#" & Now() & "#);"
Not only is this time consuming to type, but it is also quite difficult to debug. Are all the commas, apostophes and quotes in the right places? Have the correct data type delimiters been used? Have all the lines been continued with the underscore character correctly? The first you usually find out that things have gone wrong is when you test the script, only to get a syntax error or a data type error, then you have to response.write your SQL to try to identify where your punctuation or syntax has gone wrong.
They remove the need for you to delimit the data being entered
This actually saves you time in two ways - not only do you not have to ensure that the correct delimiters are in place (quotes around strings, hashes (#) around dates, nothing around numbers), but it also removes the need for ChkString() or some other version of a quote-doubling function for text values that may contain quotes.
They help prevent against SQL Injection attacks
Building SQL clauses dynamically depending on users' submissions is one of the primary routes for SQL injection attacks. A saved parameter query helps prevent unsanitised input.
They reduce maintenance
Having all your queries in one place decreases maintenance. If you have to change the name of a column or its datatype, for example, you no longer have to trawl through asp scripts to make the amendment to scattered SQL statements. You only have to do it in the database.
How to create a saved parameter query
The simplest way to do this is to open up Access, go the Query tab and create a new query in Design View. Close the Show Tables dialogue box and switch to SQL View. Using the example above, type in the first part of the SQL clause:
INSERT INTO Addresses ( CompanyName, Address1, Address2, Address3, City, StateCounty,CountryID, PostCode, SwitchboardNo, FaxNo, Email, Website, RecordStatus, LastUpdated) Values
Now open the brackets and add the parameter place holders. You can use anything you like so long as you don't use existing field names. Just like SQL Server stored procedures you can prefix parameters with the @ sign: @CompanyName, @Address1, @Address2 etc. By default, Access will surround parameters with [ ] brackets if you switch to design view. So the final query will look like this:
INSERT INTO Addresses ( CompanyName, Address1, Address2, Address3, City, StateCounty,CountryID, PostCode, SwitchboardNo, FaxNo, Email, Website, RecordStatus, LastUpdated) Values (@CompanyName, @Address1, @Address2, @Address3, @City, @StateCounty, @CountryID, @PostCode, @SwitchboardNo, @FaxNo, @Email, @Website, @RecordStatus, @LastUpdated);
If you Run the query, Access will prompt you for input for each field. Enter data against each field to test that the query is working. As for debugging, you've just done it. Save the query as something meaningful. This one is saved as qUpdateAddresses. As you save it, you may notice that Access automatically detects that this is an Append Query. Once you have verified that it works, close the database.
In your ASP code, you will still need to validate the inputs, such as email addresses for structure, end dates are after start dates, etc, but you do not need to check for quote marks or delimit the inputs in any way. Since the query was created within Access, the database already knows what data types to expect. Any attempts by hackers to inject SQL statements into the query will be foiled, as the SQL will be treated as part of the parameter value.
CompanyName = Request.Form("CompanyName")
'no need to check for apostophes in e.g. O'Connor's
Address2 = Request.Form("Address2")
Address3 = Request.Form("Address3")
...
...
LastUpdated = Now() 'no need to delimit the datetime value
then, assuming conn is an opened and valid connection object, use the query name as a method against conn, with a comma-separated list of parameters:
conn.qUpdateAddresses CompanyName,Address1,Address2,....RecordStatus,LastUpdated conn.close : set conn = nothing
The process for UPDATE queries is exactly the same as for INSERT queries, in that you develop and test the query in Access, then call it as a method against the connection object.
SELECT queries are slightly different. You still create and save the query in Access as for UPDATE and INSERT. Here's a typical example that might be used as part of a login script:
SELECT UserID FROM AuthorisedUsers WHERE Username=@Username AND UserPassword= @Password
This type of query needs to return a recordset , so you must explicitly create the recordset object first, then pass the query as a method on the connection object as before, but appending the list of values with the name of the recordset:
UserName = Request.Form("UserName")
Password = Request.Form("Password")
set rs = Server.CreateObject("ADODB.Recordset")
conn.qFindUser UserName, Password, rs
If you can't create the saved parameter query directly in the database, you can create one programmatically using the DDL Create Procedure statement. Be aware, if you are using Access 2000, and you use DDL to create the saved query, you will not be able to see it in the Query pane within Access.
Alternatively, you can use ? as parameter markers within your code instead:
<%
sSQL = "INSERT INTO Addresses (CompanyName, Address1," & _
" Address2, Address3...etc) VALUES (?,?,?,?...etc)"
arParams = array(CompanyName,Address1,Address2,Address3... etc)
set conn = createobject("ADODB.Connection")
conn.open "<your connection string>"
set cmd=createobject("ADODB.Command")
cmd.CommandText=sSQL
set cmd.ActiveConnection=conn
cmd.Execute ,arParams,129
set cmd=nothing
conn.close: set conn=nothing
%>
This doesn't give you quite the same overhead savings benefits that saved parameter queries offer: the query is compiled every time it is run; and it introduces an additional overhead in terms of bringing an extra COM object (Command) into play, but it still protects you from some of the weaknesses of dynamic SQL, and the values don't need to be delimited.
