stored procedures

[batsheva]

Does anybody know how to use ADO to pass parameters to a stored procedure connecting to Sybase?

I have a strored procedure that takes about 70 parameters and I don't want make it in to one long string on the command line.
Thanks!

[Joe Keller]

Here is some GREAT code taken directly from the MSDN it should help out alot.

USE pubs
GO
CREATE PROCEDURE myProc
@outparm int OUTPUT
@inparm int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GO

An ADO code program that executes the stored procedure myProc is shown here.

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant

Private Sub spStart()

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr

' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc

' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1

' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2

' Set up an input parameter.
Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:"))
param3.Value = royalty

' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.Execute

Dim i As Integer
While Not rs.EOF
For Each fldloop In rs.Fields
Debug.Print rs.Fields(i)
i = i + 1
Next fldloop
Debug.Print ""
i = 0
rs.MoveNext
Wend

' Need to close recordset before getting return
' and output parameters.
rs.Close

Debug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.Close

End Sub

Good Luck
Joe

[batsheva]

I have tried out the code you sent. I am attaching what I did. I am getting the following error message.
"Runtime error 3708. Parameter objects in not properly defined. Inconsistent or incomplete information was provided."
Can you please tell me what I am doing wrong?

Set cmd.ActiveConnection = cnDevConnection
cmd.CommandText = "get_cust_sales_detail"
cmd.CommandType = adCmdStoredProc

' Set up an input parameter.
Set param1 = cmd.CreateParameter("fbot_custid", adInteger, adParamInput)
cmd.Parameters.Append param1

' Set up 2nd input parameter.
Set param2 = cmd.CreateParameter("bloomUserNum", adChar, adParamInput)
cmd.Parameters.Append param2

param1.Value = -1
param2.Value = "1234567"

' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.Execute
Set MSHFlexGrid1.DataSource = rs


the error message shows up at the point where i try to create a second input parameter.

Thanks!!

[Joe Keller]

I guess the only thing I can figure that would be wrong is that you havent defined the second parameter in your Stored Procedure to be the proper type.
Joe