Test Parameter Values for ADODB Connection


If you’re running ADODB to run a stored procedure on say SQL Server.  It’s helpful to know if the parameters you’re expecting to be passed actually are.  Add the For Each code block below just prior to the .Execute and you’ll see the param values in the immediate window

 Set cmd = New ADODB.Command
 With cmd
 .ActiveConnection = CurrentProject.Connection
 .CommandText = "spInsertOrUpdate_OPTJurisCode"
 .Parameters.Append .CreateParameter("RETURN_VALUE", adDouble, adParamReturnValue)
 .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, lngID)
 .Parameters.Append .CreateParameter("@SourceID", adInteger, adParamInput, CInt(gblSource))
 .Parameters.Append .CreateParameter("@ActionType", adVarChar, adParamInput, 4, gblactionType)
 .Parameters.Append .CreateParameter("@StateID", adInteger, adParamInput, , Me.cmbState)
 .Parameters.Append .CreateParameter("@City", adVarChar, adParamInput, 75, Me.txtCity)
 .Parameters.Append .CreateParameter("@Zip", adVarChar, adParamInput, 7, Me.txtZip)
 .Parameters.Append .CreateParameter("@JurisCode", adVarChar, adParamInput, 50, Me.txtJurisCode)
 .CommandType = adCmdStoredProc
 .CommandTimeout = 0
 
 'Test Param Values
 For Each prm In cmd.Parameters
 Debug.Print prm.Name & " : " & prm.Value
 Next
 
 .Execute
 End With

 

Recent Content