By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,311 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,311 IT Pros & Developers. It's quick & easy.

Output value stored procedure dotnet

P: n/a
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

Aug 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.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

Aug 17 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.