getting the return value from a stored procedure

[Guest]

Hello,
I have a stored procedure that returns a value but does not select any rows as shown below
How can I access the value of this return value from the database using datasets or dataadapters.

Thanx in advance

Geri

rocedure in question
CREATE PROCEDURE SPSelectUserID as

DECLARE @USERNAME varchar(50)
DECLARE @USERID int

EXEC SPSelectUserLogin @USERNAME = @USERNAME OUTPUT
SELECT @USERID = UserID FROM JobControl WHERE UserName = @USERNAME

RETURN @USERID

 

[Jay B. Harlow [MVP - Outlook]]

Geraldine,

How can I access the value of this return value from the database using

datasets or dataadapters.
Short answer: You don't.

Long answer: You need to use the Command object of the respective client you
are using.

Something like (untested):
Dim command As New SqlClient.SqlCommand("SPSelectUserID",
theConnection)
Dim parm As SqlClient.SqlParameter
parm = command.Parameters.Add("@ReturnValue", SqlDbType.Int)
parm.Direction = ParameterDirection.ReturnValue
command.Parameters.Add("@USERNAME", SqlDbType.VarChar, 50)

command.ExecuteNonQuery()

Dim userid As Integer = DirectCast(parm.Value, Integer)

Hope this helps
Jay

 

[Armin Zingler]

Hello,
I have a stored procedure that returns a value but does not
select any rows as shown below
How can I access the value of this return value from the database
using datasets or dataadapters.

[...]

Doesn't seem to be a VB.NET language problem. Please turn to
microsoft.public.dotnet.framework.adonet

 

[Patrick Steele [MVP]]

Hello,
I have a stored procedure that returns a value but does not select any rows as shown below
How can I access the value of this return value from the database using datasets or dataadapters.

"HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual Basic .NET"
http://support.microsoft.com/default.aspx?scid=kb;en-us;308049