Output value stored procedure dotnet

[csgraham74]

Hi Guys,

Im attempting to get an output value from my stored procedure. Im
pulling my hair out at this one. any help appreciated.


my code is as follows:

Dim cnstring As String
Dim iConn As New SqlClient.SqlConnection(strRegistry)
Dim iDataAdapter As New SqlClient.SqlDataAdapter
Dim iDataSet As New DataSet
Dim err As String
Dim cmd As New SqlCommand

Try

iConn.Open()
cmd.Connection = iConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = SQLStr

Dim myParam1 As SqlParameter =
cmd.Parameters.Add("@Threshhold", SqlDbType.VarChar, 50)
myParam1.Direction = ParameterDirection.Input
myParam1.Value = String.Empty

Dim myParam2 As SqlParameter =
cmd.Parameters.Add("@Return", SqlDbType.VarChar, 50)
myParam2.Direction = ParameterDirection.Output

iDataAdapter = New SqlClient.SqlDataAdapter
iDataAdapter = New SqlDataAdapter(cmd)
cmd.ExecuteNonQuery()
iDataAdapter.Fill(iDataSet, "tblRoleReturned")
Dim s As String = cmd.Parameters("@Return").Value
SQLDSReturn = iDataSet

**********STORED PROCEDURE*********************************************

CREATE PROCEDURE OADsp_SelectUnassignedReferrals

@Return varchar(50) output,
@Threshhold int

as

Set @Threshhold = 60

SELECT * FROM tbl_Referrals
WHERE (AssignedStaffID IS NULL AND TimeAssigned IS NULL)
OR (ReferralStaffID IS NULL AND
DateDiff(SECOND,(TimeAssigned),(GETDATE())) > @Threshhold)



IF @@ERROR>0
BEGIN
RETURN 99
END
ELSE
BEGIN
RETURN 1
END
GO

 

[Guest]

You're not trying to get the output parameter, prior to running through your
result set generated by the select statement, are you?