Use ADO Parameters in loop

[Tod]

Is it possible to use ADO Parameter objects in a loop? If
so, what am I doing wrong.

Here's some sample code:

SampleArray("Brian","David","Mark")

For MyNames = 0 To UBound(SampleArray)
With cm
.ActiveConnection = cn
.CommandText = "qryAccessQuery"
.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("NameField",
adVarChar, adParamInput, 5)
..Parameters.Append pm1
pm1.Value = SampleArray(MyNames)
End With

rs.Open cm
'.......code to use recordset
rs.close

next MyNames

I can get through the loop once okay. But I need to
somehow clear the parameter so it can accept a new value.
All I've managed to do is continue appending parameters.

How's this done?

tod

 

[Rob Bovey]

Hi Tod,

You create the Command object and all the required Parameter objects
before you go into the loop. Then you just modify the value of the
parameter(s) inside the loop like so:

cm.ActiveConnection = cn
cm.CommandText = "qryAccessQuery"
cm.CommandType = adCmdStoredProc
Set pm1 = cm.CreateParameter("NameField", adVarChar, adParamInput, 5)
cm.Parameters.Append pm1

For MyNames = 0 To UBound(SampleArray)
pm1.Value = SampleArray(MyNames)
rs.Open cm
'.......code to use recordset
rs.Close
Next MyNames

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Jean-Paul Viel]

Hi,

If I understand well, you want to run a stored procedure that need one input
parameter , Brian then David and last Mark. The way your loop is done you
append a new parameter each time you go throw the loop. Create your
parameter outside the loop and then assign the proper value inside, then
your record set will have the right information.