Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO Parameters in loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO Parameters in loop
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 * "Tod" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO Parameters in loop
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. -- JP http://www.solutionsvba.com "Tod" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parameters | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Too few parameters | Excel Discussion (Misc queries) | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |