question about storage proc

J

jeorme

Hello,

Excuse me for my litteral english, I am french.

I need help about ADODB in VBA ACCESS 97.

I have a storage procedure in SQL Server with an OUTPUT parameter.

In my VBA program , I want to take back this Output parameter But I dont
know how to do.
Ex:

Set objCommand = New ADODB.Command

Set objCommand.ActiveConnection = objConn
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "recup"
objCommand.Execute , , ADODB.ExecuteOptionEnum.adExecuteNoRecords


If someone has an example !!!


thank you
 
J

JohnFol

Jerome, I found the following article that could be used as a start . .

Getting Return Values
Stored procedures often pass values back to the application that called
them. They can do so by passing the value using a parameter or by defining
and passing a return value.

To get values returned by procedures

1.. Create parameters whose Direction property is set to Output or
InputOutput (if the parameter is used in the procedure to both receive and
send values). Make sure the data type of the parameter matches the expected
return value.
2.. After executing the procedure, read the Value property of the
parameter being passed back.
To get a procedure's return value

1.. Create a parameter and set its Direction property is set to
ReturnValue.
Note The parameter object for the return value must be the first item
in the Parameters collection.
2.. Make sure the parameter's data type matches the expected return value.
Note Update, Insert, and Delete SQL statements return an integer value
indicating the number of records affected by the statement. You can get this
value as the return value of the ExecuteNonQuery method. For more
information, see Executing Updates or Database Commands using a Data
Command.
The following example shows how to get the return value of a stored
procedure called CountAuthors. In this case, it is assumed that the first
parameter in the command's Parameters collection is named "retvalue" and
that is configured with a direction of ReturnValue.

' Visual Basic
Dim cntAffectedRecords As Integer
' The CommandText and CommandType properties can be set
' in the Properties window but are shown here for completeness.
OleDbcommand1.CommandText = "CountAuthors"
OleDbCommand1.CommandType = CommandType.StoredProcedure
OleDbConnection1.Open()
OleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()
cntAffectedRecords = CType(OleDbCommand1.Parameters("retvalue").Value,
Integer)
MessageBox.Show("Affected records = " & cntAffectedRecords.ToString)
 
Top