Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Call a Parameterized Query to an Access Database with ADO

How To Call a Parameterized Query to an Access Database with ADO

View products that this article applies to.

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

↑ Back to the top

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

↑ Back to the top

Keywords: KB200190, kbhowto

↑ Back to the top

Article Info
Article ID : 200190
Revision : 4
Created on : 6/29/2004
Published on : 6/29/2004
Exists online : False
Views : 451