Output value stored procedure dotnet

C

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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
474,056
Messages
2,570,441
Members
47,119
Latest member
NoeliaIrby

Latest Threads

Top