ADO Command Object, automatic population of parameters collection.

[steveblum]

I am using the ADO command object in VB to access SQL-Server. I set the commandtext property of the command object to a stored procedure call with several undefined parameters, by using a &quot;?&quot; for each of those parameters. This automatically checks the stored procedure and sets up the parameters collection to have the right number of parameters with the correct types.<br>
<br>
However, on certain procedures, the parameters collection gets two parameters for each parameter in my stored procedure, plus an extra one at the begining that is an output parameter call &quot;RETURN_VALUE&quot;. I don't know why this happens, but it is keeping me from being able to call the stored procedure correctly.<br>
<br>
Any suggestions?<br>
<br>
Thanks, Steve

 

[ag]

Hi,<br>
<br>
Do you have to be able to call the stored procedure in this way or can you use the parameters collection?<br>
<br>
If so, try something like this?<br>
<br>
Set prm = cmd.CreateParameter(&quot;YourParameterName&quot;, adVarChar,_ adParamInput, 25)<br>
cmd.Parameters.Append prm<br>
prm.Value = ValueYouWantToPass<br>
<br>
where the datatypes etc are substituted with your requirements as per the online help.<br>
<br>
HTH<br>
<br>
Andrew<br>

 

[steveblum]

Andrew,<br>
<br>
Thanks for the response. Actually, the app is a utility that acts very generically, calling stored procedures based on data in a SQL Server table. It doesn't know for each procedure what the datatypes are without using the method I described. It actually is a very effective method and we have been using it successfully for several months until this bug cropped up.<br>
<br>
We discovered this is a bug in SQL Server 7 when creating stored procedures. When I first built the procedure, I used the word &quot;RETURN&quot; to send a return value back to the calling program. But we decided not to add the code to the VB app to check the return value, so we took it out of the procedure. Apparently SQL Server set some flag or something indicating the procedure needed an extra parameter for the return value, and never set it back when we removed the word RETURN.<br>
<br>
We dropped the procedure and added it back with the same name, and the problem still happened. But copying the code into a new procedure fixed the problem! It is a repeatable bug in SQL Server 7.<br>
<br>
Steve

 

[ag]

Don't you just love the bugs....:&gt;<br>
<br>
Andrew