Microsoft KB Archive/200190

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

Article ID: 200190

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q200190



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 %>  

<% 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 %>

<% objRS.Close objConn.Close Set objRS = Nothing Set objCmd = Nothing Set objConn = Nothing %>   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

Keywords: kbhowto KB200190

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.