[RESOLVED] ADODB Command - Now Populating paramteters automatically!?

[simonm]

Hi there,

I have some legacy VB6 code that uses ADODB commands to execute stored procedures in an SQL database.

It's suddenly stopped working. On investigation, it seems that the parameters are being created automatically as soon as I specified the CommandName (i.e. the stored procedure). Our existing code adds the parameters manually so now we've ended up with twice as many parameters and the code execution obviously fails.

So, I'm wondering if anyone else has experienced this? Is it something in Windows that has changed recently?

[TysonLPrice]

I haven't. Could you post the code?

[Zvoni]

Maybe a change of the Provider?

Are you using the correct CommandTypeEnum?
https://learn.microsoft.com/en-us/sq...l-server-ver16

Any updates on the SQL-Server?

Is the Parameters-Collection "empty" before calling the SP?
https://learn.microsoft.com/en-us/sq...l-server-ver16

In General: Any changes between "working" and not "working"?
https://learn.microsoft.com/en-us/sq...l-server-ver16

[simonm]

        Set cmUpdate = New command
        Set cmUpdate.ActiveConnection = cnSource
        cmUpdate.CommandType = adCmdStoredProc
        cmUpdate.CommandTimeout = 120
        cmUpdate.CommandText = strProcName
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("ID", adInteger, adParamInput, , rsSource(strKeyName))
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("LastRead", adBinary, adParamInput, 8, rsSource!LastUpdate_ts)
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("Result", adInteger, adParamOutput)

As soon as the line in red is executed, the parameters are now fully populated automatically. So the later parameter creation code just duplicates them.

No code changes and no changes to SQL Server recently (that I am aware of)...

[Zvoni]

        Set cmUpdate = New command
        Set cmUpdate.ActiveConnection = cnSource
        cmUpdate.CommandType = adCmdStoredProc
        cmUpdate.CommandTimeout = 120
        cmUpdate.CommandText = strProcName
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("ID", adInteger, adParamInput, , rsSource(strKeyName))
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("LastRead", adBinary, adParamInput, 8, rsSource!LastUpdate_ts)
        cmUpdate.Parameters.Append cmUpdate.CreateParameter("Result", adInteger, adParamOutput)

As soon as the line in red is executed, the parameters are now fully populated automatically. So the later parameter creation code just duplicates them.

No code changes and no changes to SQL Server recently (that I am aware of)...

Sounds like a leftover

Check the Paremeters-Collection BEFORE executing your red line

Is it empty? Check the Count-Property

And try:
Set cmUpdate = Nothing as the first line (before setting it to a New Command)

[simonm]

Ah, so my bad. I was on a false trail - simply interrogating the parameters collection forces it to refresh and populate from the database (if it can).

Thanks for your help and suggestions.