Output parameter not returning a value

[vivienne.netherwood]

I am developing an Access Project front end with a SQL server database.
I have written a stored procedure that returns a record set and also a
value via an output parameter.

The procedure is as follows
CREATE PROCEDURE qslCheckShiftTimes
@Ward NVARCHAR(6),
@Shift NVARCHAR(10),
@Exists TINYINT OUTPUT
AS

SELECT *
FROM tblWardShift
WHERE
tblWardShift.WardCode=@Ward
AND tblWardShift.strShift = @Shift

SELECT @Exists = @@ROWCOUNT

RETURN
GO

The test form has the following code to call the procedure
Private Sub Command0_Click()

Dim oConn As New ADODB.Connection
Dim oCmdTime As New ADODB.Command
Dim oRs As New ADODB.Recordset
Dim sConn As String
Dim iTime As Integer
sConn = "Provider='SQLOLEDB';Data Source='Aph-Ework-Dev';" & _
"Initial Catalog='NB2KunsecuredSQL';Integrated
Security='SSPI';"

oConn.Open sConn
oCmdTime.CommandText = "qslCheckShiftTimes"
oCmdTime.CommandType = adCmdStoredProc
Set oCmdTime.ActiveConnection = oConn

oCmdTime.Parameters("@Ward") = "AW43"
oCmdTime.Parameters("@Shift") = "Early"
oCmdTime.Parameters("@Exists") = iTime

oRs.Open oCmdTime, , adOpenForwardOnly, adLockReadOnly
MsgBox ("Result = " & Str(iTime))
MsgBox (oRs.Fields(1))

oRs.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing
End Sub

The procedure call returns one row, but the @Exists parameter returns 0
when it should return 1. When I debug the procedure in Query Analyser
on the server, the output parameter is correctly set to 1. I can also
run the procedure from the frontend (but can't work out how to see what
parameter value is returned).
I know that I could work around this problem without using an output
parameter, but am just learning SQL server and need to know what I am
doing wrong. I wondered if the output parameter needs to initially be
set to null somehow.
Thanks in advance.

[Justin Hoffman]

<vi*****************@whnt.nhs.uk> wrote in message
news:11********************@g47g2000cwa.googlegrou ps.com...

I am developing an Access Project front end with a SQL server database.
I have written a stored procedure that returns a record set and also a
value via an output parameter.

The procedure is as follows
CREATE PROCEDURE qslCheckShiftTimes
@Ward NVARCHAR(6),
@Shift NVARCHAR(10),
@Exists TINYINT OUTPUT
AS

SELECT *
FROM tblWardShift
WHERE
tblWardShift.WardCode=@Ward
AND tblWardShift.strShift = @Shift

SELECT @Exists = @@ROWCOUNT

RETURN
GO

The test form has the following code to call the procedure
Private Sub Command0_Click()

Dim oConn As New ADODB.Connection
Dim oCmdTime As New ADODB.Command
Dim oRs As New ADODB.Recordset
Dim sConn As String
Dim iTime As Integer
sConn = "Provider='SQLOLEDB';Data Source='Aph-Ework-Dev';" & _
"Initial Catalog='NB2KunsecuredSQL';Integrated
Security='SSPI';"

oConn.Open sConn
oCmdTime.CommandText = "qslCheckShiftTimes"
oCmdTime.CommandType = adCmdStoredProc
Set oCmdTime.ActiveConnection = oConn

oCmdTime.Parameters("@Ward") = "AW43"
oCmdTime.Parameters("@Shift") = "Early"
oCmdTime.Parameters("@Exists") = iTime

oRs.Open oCmdTime, , adOpenForwardOnly, adLockReadOnly
MsgBox ("Result = " & Str(iTime))
MsgBox (oRs.Fields(1))

oRs.Close
Set oRs = Nothing
oConn.Close
Set oConn = Nothing
End Sub

The procedure call returns one row, but the @Exists parameter returns 0
when it should return 1. When I debug the procedure in Query Analyser
on the server, the output parameter is correctly set to 1. I can also
run the procedure from the frontend (but can't work out how to see what
parameter value is returned).
I know that I could work around this problem without using an output
parameter, but am just learning SQL server and need to know what I am
doing wrong. I wondered if the output parameter needs to initially be
set to null somehow.
Thanks in advance.

You haven't set up the parameters properly. Here is a bit of code which
shows how the parameters are created so we know which one is the return
value. The code below comes from a web page (hence vbscript lack of data
types and limited error handling) but it should point you in the right
direction:

Function spPassword(cnn, Usr, OldPwd, NewPwd)

On Error Resume Next

Dim cmd

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
.Parameters.Append .CreateParameter("ReturnValue", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@old", adVarChar, adParamInput,
30, OldPwd)
.Parameters.Append .CreateParameter("@new", adVarChar, adParamInput,
30, NewPwd)
.Parameters.Append .CreateParameter("@loginame", adVarChar, adParamInput,
30, Usr)
.CommandType = adCmdStoredProc
.CommandText = "sp_password"
Set .ActiveConnection = cnn
.Execute , , adExecuteNoRecords
End With

If Err.Number = 0 then
If cmd.Parameters(0) = 0 then spPassword = True
End if

Set cmd = nothing

End Function

[lylefair]

1. Do you need the recordset?
2. Should you assign the parameter @Exists to iTime or Vice Versa?
3. When should you do this assignment, before or after you open the
recordset?
4. It is unnecessary to close and release ADODB objects.

5. This should be sufficient to get Rowcount (but I always create the
Parameter Objects(s) explicitly).

ALTER PROCEDURE StoredProcedure1
@parameter int
AS
SELECT * FROM [4060148Transactions]
WHERE DebitAccountID = @parameter
RETURN @@RowCount

Dim c As ADODB.Command
Set c = New ADODB.Command
With c
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "StoredProcedure1"
.Parameters("@parameter") = 21
.Execute
MsgBox .Parameters(0)
End With

[vivienne.netherwood]

Thanks Justin
I was hoping that because I was using Access Project as a front end
that it was not necessary to create and append the parameters in the
front end - especially as I will need a lot of parmeters. (I thought
this may only apply to VB frontends)
I don't understand why you need to redefine the parameters when you
they are available for inspection via the locals pane.

[vivienne.netherwood]

Problem solved
This is not due to the parameters not being created properly - that
solution did not work.
The problem was because I was trying to return an output parameter and
a datset at the same time.

[Justin Hoffman]

<vi*****************@whnt.nhs.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Problem solved
This is not due to the parameters not being created properly - that
solution did not work.
The problem was because I was trying to return an output parameter and
a datset at the same time.

The solution I posted came from a currently functioning website - it does
work - honest. What you might have noticed from it is that there is no
recordset needed as I am only trying to get the value of the return
parameter. From the look of your procedure, you could have done the same.
While you don't need to explicitly use .CreateParameter, most of the coding
samples state that this is the most efficient way to do this. Don't forget
that you could write a sub which will generate the code by itself and this
can save you work if you have a lot of these to do. In the example sub
below, code is written for you to touch up afterwards
Public Sub CodeParameters(strProcName As String)

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngCount As Long
Dim strLine As String

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI;"
cnn.Open

Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cnn
.CommandText = strProcName
.CommandType = adCmdStoredProc
.Parameters.Refresh
End With

For lngCount = 0 To cmd.Parameters.Count - 1

With cmd.Parameters(lngCount)

strLine = ".Parameters.Append .CreateParameter(""" & .Name &
""", " & _
.Type & ", " & _
.Direction & ", " & _
.Size & ", MyValue)"

End With

Debug.Print strLine

Next lngCount
Exit_Handler:

On Error Resume Next

Set prm = Nothing

Set cmd = Nothing

cnn.Close

Set cnn = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

***Note
To help readability, you could put the enumerated constants in like this:

Select Case .Direction
Case adParamInput: strLine = strLine & "adParamInput, "
Case adParamOutput: strLine = strLine & "adParamInput, "
Case adParamInputOutput: strLine = strLine & "adParamInputOutput, "
Case adParamReturnValue: strLine = strLine & "adParamReturnValue, "
Case adParamUnknown: strLine = strLine & "adParamUnknown, "
Case Else: strLine = strLine & "~CompileError~, "
End Select