Parameterized Queries in Access

Parameterized queries are a great asset when developing database applications. They allow you to modularize your code, so that it’s easier to read and maintain. Another great benefit of parameterized queries is that they run faster than queries which you explicity type in each time. You can use parameterized queries in both MS-SQL and MS-Access. This article discusses how to use parameterized queries in Access; if you’d like to see how to do it in SQL.


Here is a sample for a parameterized stored procedure in MS Access. The SQL in Access is: PARAMETERS [inid] Text; SELECT … and on.

And the source is:

<%@  Language=VBScript %>
<%
	Dim cnn1
	Dim cmdNyttSvarID
	Dim prmNyttSvarID
	Dim rstNyttSvarID
	Dim strID
	Dim strCnn
	Dim strSize
	Dim i
	Dim tmpFields
	Dim strName
	Dim strTable
	
	' Open connection.
	Set cnn1 = Server.CreateObject ("ADODB.Connection")
	strCnn = "DSN=agenda21"
	cnn1.Open strCnn
	cnn1.CursorLocation = adUseClient
	
	' Open command object with one parameter.
	Set cmdNyttSvarID = Server.CreateObject ("ADODB.Command")
	cmdNyttSvarID.CommandText = "nyttsvarid"
	cmdNyttSvarID.CommandType = adCmdStoredProc
	
	' Get parameter value and append parameter.
	' The value for the one parameter in this example
	strID = "00001"
	strSize = Len(strID)
	
	' Parametername
	strName = "inid"
	
	Set prmNyttSvarID = cmdNyttSvarID.CreateParameter(strName, adVarChar, adParamInput,strSize,strID)
	cmdNyttSvarID.Parameters.Append prmNyttSvarID
	prmNyttSvarID.Value = strID
	
	' Create recordset by executing the command.
	Set cmdNyttSvarID.ActiveConnection = cnn1
	Set rstNyttSvarID = cmdNyttSvarID.Execute
	i = 1
	
	' Dump the returned recordset to the client
	'set rstNyttSvarID = Server.CreateObject ("ADODB.RECORDSET")
	'set tmpField = rstNyttSvarID.Fields.Item (1).Value 
	
	Do While Not rstNyttSvarID.EOF
		Response.Write ("<BR>")
		
		For Each tmpField In rstNyttSvarID.Fields
			Response.Write (tmpField.Name & ":" & tmpField.Value & ",")
		Next
		
		rstNyttSvarID.MoveNext
	Loop
	
	rstNyttSvarID.Close
	cnn1.Close
%>

Using Parameterized Queries
Tutorial on how to call stored procedures through ASP, and how to use parameterized stored procedures using Acive Server Pages.

You can call store procedures in ASP like this

<%
set conn=server.createObject(ADODB.Connection)
conn.open "dsn=local uid= pwd="

set cmd=server.createobject("ADODB.Command")
set cmd.activeconnection=conn
set cmd.commandtext="StoreProcedure name"
set cmd.commandtype=adcmdStoredProc

'Now you can pass the parameters as of your like
cmd.parameters(1).value="hello"

'And so on...
cmd.parameters(50).value="gfg"
%>

and your result from store procedure is in

<%=cmd.parameters(0).value%>

if you want it in resultset than

<% set q=cmd.execute %>
<% =q(0)%>
<% =q(1)%>

where 0 and 1 are fields of table

(You can also use the associative names (the string representations for your recordset columns) that you’re use to using.)

Comments are closed.