Henrik Blixt
Note from Scott Mitchell
Parameterized queries are a great asset when developing database applications.
They allow you to modularize your code, so that it's easier to read and
maintain. Another great benefit of parameterized queries is that they run
faster than queries which you explicity type in each time.
You can use parameterized queries in both MS-SQL and MS-Access. This article discusses how to use parameterized queries in Access; if you'd like to see how to do it in SQL, read Using Parameterized Queries.
Scott Mitchell |
Here is a sample for a parameterized stored procedure in MS Access. The SQL in Access is: PARAMETERS [inid] Text; SELECT ... and on.
And the source is:
<%@ Language=VBScript %>
<%
Dim cnn1
Dim cmdNyttSvarID
Dim prmNyttSvarID
Dim rstNyttSvarID
Dim strID
Dim strCnn
Dim strSize
Dim i
Dim tmpFields
Dim strName
Dim strTable
' Open connection.
Set cnn1 = Server.CreateObject ("ADODB.Connection")
strCnn = "DSN=agenda21"
cnn1.Open strCnn
cnn1.CursorLocation = adUseClient
' Open command object with one parameter.
Set cmdNyttSvarID = Server.CreateObject ("ADODB.Command")
cmdNyttSvarID.CommandText = "nyttsvarid"
cmdNyttSvarID.CommandType = adCmdStoredProc
' Get parameter value and append parameter.
' The value for the one parameter in this example
strID = "00001"
strSize = Len(strID)
' Parametername
strName = "inid"
Set prmNyttSvarID = cmdNyttSvarID.CreateParameter(strName, adVarChar, adParamInput,strSize,strID)
cmdNyttSvarID.Parameters.Append prmNyttSvarID
prmNyttSvarID.Value = strID
' Create recordset by executing the command.
Set cmdNyttSvarID.ActiveConnection = cnn1
Set rstNyttSvarID = cmdNyttSvarID.Execute
i = 1
' Dump the returned recordset to the client
'set rstNyttSvarID = Server.CreateObject ("ADODB.RECORDSET")
'set tmpField = rstNyttSvarID.Fields.Item (1).Value
Do While Not rstNyttSvarID.EOF
Response.Write ("<BR>")
For Each tmpField In rstNyttSvarID.Fields
Response.Write (tmpField.Name & ":" & tmpField.Value & ",")
Next
rstNyttSvarID.MoveNext
Loop
rstNyttSvarID.Close
cnn1.Close
%>
Alter 4Guys reader Peter J. writes: "First and foremost, your article uses a DSN to access the MS Access database, which is unstable under 24x7 web site conditions. Follow this link for details: http://www.adopenstatic.com/faq/whyOLEDB.asp "Second, you make the following claim: "Another great benefit of parameterized queries is that they run faster than queries which you explicity type in each time." Conceptually, this makes sense, since stored queries are supposedly compiled for better performance. Indeed, this seems to be the case for SQL Server and Oracle. But in the case of Access, I think the overhead of running a stored query from ASP will cancel out any small benefit you may get. In my case, I ran timed examples both ways, and my particular query was just as fast in the ASP page, as it was stored in the database. "Have you had any experience where the query is faster in the Access database than it is in the ASP page? I would encourage you to research this topic, and if appropriate, change the ASP page to reflect your results. I will likely run a few tests myself when time permits." |
Happy Programming!