HOWTO: Call a Parameterized Query to an Access Database with ADO

ID: Q200190

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0
Microsoft Access versions 7.0, 97

SUMMARY

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).

MORE INFORMATION

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

Additional query words:

Keywords : kbAccess97 kbADO150 kbADO200
Version : WINDOWS:1.5,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto