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.SqlCo nnection(strReg istry)
Dim iDataAdapter As New SqlClient.SqlDa taAdapter
Dim iDataSet As New DataSet
Dim err As String
Dim cmd As New SqlCommand

Try

iConn.Open()
cmd.Connection = iConn
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = SQLStr

Dim myParam1 As SqlParameter =
cmd.Parameters. Add("@Threshhol d", SqlDbType.VarCh ar, 50)
myParam1.Direct ion = ParameterDirect ion.Input
myParam1.Value = String.Empty

Dim myParam2 As SqlParameter =
cmd.Parameters. Add("@Return", SqlDbType.VarCh ar, 50)
myParam2.Direct ion = ParameterDirect ion.Output

iDataAdapter = New SqlClient.SqlDa taAdapter
iDataAdapter = New SqlDataAdapter( cmd)
cmd.ExecuteNonQ uery()
iDataAdapter.Fi ll(iDataSet, "tblRoleReturne d")
Dim s As String = cmd.Parameters( "@Return").Valu e
SQLDSReturn = iDataSet

**********STORE D PROCEDURE****** *************** *************** *********

CREATE PROCEDURE OADsp_SelectUna ssignedReferral s

@Return varchar(50) output,
@Threshhold int

as

Set @Threshhold = 60

SELECT * FROM tbl_Referrals
WHERE (AssignedStaffI D IS NULL AND TimeAssigned IS NULL)
OR (ReferralStaffI D IS NULL AND
DateDiff(SECOND ,(TimeAssigned) ,(GETDATE())) @Threshhold)

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

[David Jessee]

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

"csgraham74 " wrote:

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.SqlCo nnection(strReg istry)
Dim iDataAdapter As New SqlClient.SqlDa taAdapter
Dim iDataSet As New DataSet
Dim err As String
Dim cmd As New SqlCommand

Try

iConn.Open()
cmd.Connection = iConn
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = SQLStr

Dim myParam1 As SqlParameter =
cmd.Parameters. Add("@Threshhol d", SqlDbType.VarCh ar, 50)
myParam1.Direct ion = ParameterDirect ion.Input
myParam1.Value = String.Empty

Dim myParam2 As SqlParameter =
cmd.Parameters. Add("@Return", SqlDbType.VarCh ar, 50)
myParam2.Direct ion = ParameterDirect ion.Output

iDataAdapter = New SqlClient.SqlDa taAdapter
iDataAdapter = New SqlDataAdapter( cmd)
cmd.ExecuteNonQ uery()
iDataAdapter.Fi ll(iDataSet, "tblRoleReturne d")
Dim s As String = cmd.Parameters( "@Return").Valu e
SQLDSReturn = iDataSet

**********STORE D PROCEDURE****** *************** *************** *********

CREATE PROCEDURE OADsp_SelectUna ssignedReferral s

@Return varchar(50) output,
@Threshhold int

as

Set @Threshhold = 60

SELECT * FROM tbl_Referrals
WHERE (AssignedStaffI D IS NULL AND TimeAssigned IS NULL)
OR (ReferralStaffI D IS NULL AND
DateDiff(SECOND ,(TimeAssigned) ,(GETDATE())) @Threshhold)

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