Accessing Stored Procedure from ADO Control

[AOLBoy]

I have used an ADO control to retrieve data via a stored procedure in my SQL Server database.

The stored procedure requires a single parameter (char string), but I can't seem to find the syntax for passing the parameter. Any help would be much appreciated.

 

[nath]

Hi there,

you have to create a command object with a stored procedure command type, then create a parameter object for each parameter you have and add it to you parameters collection of your command object. Then associate the value to the parameter to the command object and execute the command object. In code it looks something like:

dim prm as ADODB.Parameter
dim cmd as ADODB.command

Set cmd = New ADODB.Command
With cmd
  Set .ActiveConnection = myConnectionObject
      .CommandType = adCmdStoredProc
      .CommandText = myProcedureName
      
  Set prm = .CreateParameter("myParam", adVarChar, adParamInput, 12)
  .Parameters.Append prm
  
  .Parameters("myParam").Value = ParamExample1
  .Execute
end With

Hope this helps,

Nath

 

[AOLBoy]

Thanks for that Nath, but my problem is within the ADO control. On the Record Source tab of the Prperty Pages I have set the command type to 4 - adCmdStoredProc. In the Table or Stored Procedure Name I need to enter the Stored Procedure name along with the parameter value, such as

SP 'A1'

where SP is the Stored Procedure name and 'A1' is the parameter.

Any ideas?

 

[tsh73]

There is a FAQ
"Using Parameters in ADO Stored Procedures"

http://www.tek-tips.com/faqs.cfm?spid=222&sfid=2067

Not sure it can help here