getting return value from stored proc

[JohnE]

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

 

[RonaldoOneNil]

Obviously not tested, but should dim_result simply not be on the line above ?
......
dim_result = RunSQLServerStoredProcedure(dimSQL)
CurrentProject.Connection.Execute (dimSQL)
If dim_result > 0 Then
.......

 

[Krzysztof Naworyta]

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



:

| 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

 

[JohnE]

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]

I didn't notice that you used mdb. I thought about adp...
So change
cmd.ActiveConnection = CurrentProject.Connection
to connection object that points to MSSQL
and do not create parameters, just refresh them:

cmd.parameters.refresh
cmd.parameters(0).Value=...
cmd.parameters(1).Value=...

cmd.execute

msgbox cmd.parameters(2).Value

--
KN



JohnE wrote:
| 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