Re: getting return value from stored proc

Tech-Archive recommends: Fix windows errors by optimizing your registry

KN, thanks for the info. I enlisted your way and it keep erroring out on the
..execute line. The error indicates;

"Error -2147217887 (Multiple-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done.) in procedure
......"

Here is the current code that I have.

Dim param As New ADODB.Parameter
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.RISpecForPartNumberHasResults"

Set param = cmd.CreateParameter("@part_number", adVarChar, adParamInput, 12,
Me.PartNumber)
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@part_rev", adVarChar, adParamInput, 8,
Me.cboViewRevision)
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@cnt", adInteger, adParamOutput)
cmd.Parameters.Append param

cmd.Execute

Dim x As Long
x = cmd.Parameters("cnt")

Me.txtDimResult = x

The txtbox is there to see what is returned for debugging. As mentioned, it
is the execute line and I am not able to determine the cause from the error
message.

Any further info I should know?

Thanks.... John




"Krzysztof Naworyta" wrote:

I do not know what is RunSQLServerStoredProcedure function, but if this is
something like connection.execute you can not get output parameters (IMO)
Run your stored procedure via command object:

Dim cmd as command
set cmd = new command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "dbo.YourStoredProc"
'.CommandTimeout = 0

.Parameters.Append _
.CreateParameter("@Param1", adInteger, adParamInput,,Me.PartNumber )
.Parameters.Append _
.CreateParameter("@Param2", adVarWChar, adParamInput, 4000,
Me.cboViewRevision )
.Parameters.Append _
.CreateParameter("@Returns", adInteger, adParamOutput)

.execute

MsgBox .Parameters("@Returns").Value

End With
End If

--
KN



"JohnE" wrote:

| Hello. I have a mdb connected to sql2005 backend. In the backend there
is a
| stored proc that scans 4 tables for the 2 parameters passed in from the
front
| end. The stored proc then returns a value of either 0 (nothing found) or
1
| (info found). On the front end in a form there is a button that when the
| user clicks it, part of the click event is passing the params and calling
the
| stored proc.
|
| Below is part of the code that is in the button event. This is the part
| that passes the params in and calls the stored proc. Don't let the
'dimSQL =
| ...' line and the 'Call ..." line throw you off. The Call line is used
| throughout the app and has been used for years. The same with the dimSQL
| line (or similar to it). Both work well. It is getting the return value
| back for the If...Then statement.
|
| Dim dimSQL As String
| Dim nondimSQL As String
| Dim dim_result As Long
| Dim nondim_result As Long
|
| dimSQL = "RISpecForPartNumberHasResults '" & PartNumber & "', '" &
| cboViewRevision & "'"
| Call RunSQLServerStoredProcedure(dimSQL)
| ' dim_result = CurrentProject.Connection.Execute (dimSQL)
|
| If dim_result > 0 Then
|
| What my issue is centers around getting the return value from the stored
| proc. How does it get returned back to the front end to be used in the
| If...Then statement?
|
| Thanks in advance for any help on this.
| John


.

Quantcast