This article demonstrates how to call a parameterized query to an Access database using the ADO Command object and Parameters collection. This article will use VBScript samples run as Active Server Pages (ASP).
NOTE: The samples in this article make use of the Northwind database and will function only if the database (NWind.mdb) exists on the Internet Information Server (IIS) server and an ODBC DSN named Northwind exists and is directed to NWind.mdb.
There are two methods for calling a parameterized query:
- Specifying a SQL statement that uses question marks (?) as parameter placeholders (for example, SELECT Name FROM Table WHERE Name = ?)
- Creating a Query in an Access Database that contains parameters.
For this example I'll show you both methods.
Before you begin, create an Access Query in the Northwind database, called SampleQuery, that is built in SQL view from the following:
SELECT * FROM Products WHERE ProductID = [@productid]
The following sample code will call the Access Query called SampleQuery using the ADO Command object and the Parameters collection.
To use the sample code, create an Active Server Pages (ASP) page in a virtual folder on an machine running IIS. Copy and paste the ASP code into the ASP page. Copy the Adovbs.inc file into this virtual folder (the file can be found in the \programFiles\CommonFiles\System\ADO folder).
<%@ Language=VBScript %>
<HTML>
<BODY>
<!--#include file=adovbs.inc -->
<%
Set objConn = Server.CreateObject("ADODB.Connection")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "dsn=northwind;"
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockOptimistic
Set objCmd.ActiveConnection = objConn
'If a SQL statement with question marks is specified, then the
'CommandType is adCmdText. If a query name is specified, then
'the CommandType is adCmdStoredProc.
objCmd.CommandText = "SampleQuery"
objCmd.CommandType = adCmdStoredProc
'Create the parameter and populate it.
Set objParam = objCmd.CreateParameter("@productid" , adInteger, adParamInput, 0, 0)
objCmd.Parameters.Append objParam
objCmd.Parameters("@productid") = 15 'Return the product with ProductID = 15
'Open and display the Recordset.
objRS.Open objCmd
%>
<table border=1 cellpadding=2 cellspacing=2>
<tr>
<%
For I = 0 To objRS.Fields.Count - 1
Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
Next
%>
</tr>
<%
Do While Not objRS.EOF
Response.Write "<tr>"
For I = 0 To objRS.Fields.Count - 1
Response.Write "<td>" & objRS(I) & "</td>"
Next
Response.Write "</tr>"
objRS.MoveNext
Loop
%>
</table>
<%
objRS.Close
objConn.Close
Set objRS = Nothing
Set objCmd = Nothing
Set objConn = Nothing
%>
</BODY>
</HTML>
To use a SQL statement with question marks as parameter placeholders, use the same sample code but update the CommandText and CommandType properties as in the following example:
objCmd.CommandText = "SELECT * FROM Products WHERE ProductID = ?"
objCmd.CommandType = adCmdText