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


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 file into this virtual folder (the file can be found in the \programFiles\CommonFiles\System\ADO folder).

<%@ Language=VBScript %>

<!--#include -->

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>
For I = 0 To objRS.Fields.Count - 1
Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
Do While Not objRS.EOF
Response.Write "<tr>"
For I = 0 To objRS.Fields.Count - 1
Response.Write "<td>" & objRS(I) & "</td>"
Response.Write "</tr>"

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

ID članka: 200190 - posljednja izmjena: 14. kol 2008. - verzija: 1

Povratne informacije