ADO Parameters - how to clear

Question

_{Wednesday, August 10, 2011 3:31 AM}

I have an Access proc that loops over all the comboboxes on a form and calls a SQLServer stored procedure to fill each combobox list. The sproc takes up to 2 arguments, although each loop only passes 1. When I get to the 3rd combobox, I get an error that "too many arguments were specified". It appears that the ADO parameter list is not being cleared out at the beginning of each loop, even though I Dimension it to "new", and by the 3rd pass it has exceeded the 2-parameter specification. My question is...why isn't the parameter list being flushed out when I specify " Dim ADOParms As New ADODB.parameter"? Is there another way to flush the list?

For Each ctl In ctlCollection
        If ctl.ControlType = acComboBox Then
            ctlName = ctl.Name 'there must be a corresponding SQL object with a prefix of "vw_" + ctlName or "udp_"+ctlName

Dim ADOParms As New ADODB.parameter
Dim ADOParmsRetVal As New ADODB.parameter
Dim ADOCommand As New ADODB.Command
Dim SQLConnect As New ADODB.Connection
SQLConnect.Open MasterConnectString
ADOCommand.ActiveConnection = SQLConnect 'connection string
ADOCommand.CommandType = adCmdStoredProc
ADOCommand.CommandText = sprocName
Set ADOParms = ADOCommand.CreateParameter("InputControlName", adVarChar, adParamInput, 200, ctlName)
ADOCommand.Parameters.Append ADOParms
If InputArgs > "" Then
 Set ADOParms = ADOCommand.CreateParameter("InputArg", adVarChar, adParamInput, 200, InputArgs)ADOCommand.Parameters.Append ADOParms
End If
Dim rSQL As New ADODB.Recordset
Set rSQL = ADOCommand.Execute
do stuff
   rSQL.Close
   SQLConnect.Close
  End If
Next ctl

Darrell H Burns

All replies (2)

_{Wednesday, August 10, 2011 5:34 AM ✅Answered}

You append a new parameter in each iteration of the For ... Next loop in the line

ADOCommand.Parameters.Append ADOParms

 
Keep in mind that the line

Dim ADOCommand As New ADODB.Command

 
is executed only once, not in each iteration even though this declaration is within the loop. You could replace the latter line with

Dim ADOCommand As ADODB.Command
Set ADOCommand = New ADODB.Command

 
to reset ADOCommand (and thus clear the parameters).

Also, there doesn't seem to be a need to open and close the connection in each iteration of the loop, you could open it before, and close it after the loop.

Regards, Hans Vogelaar

_{Wednesday, August 10, 2011 11:08 AM}

Yep, that did it. Thanx Hans!Darrell H Burns