ado.net / execute reader / sp question

[Gary Howlett]

Hi,

I have a stored procedure which has a couple of input parameters and out
parameters as below...

If in C# .Net2.0 using SQL ADO.NET if I use ExecuteReader I get the results
of the user table but if in the code i use
cmdTest.Parameters["@Token"].Value; its always null.

Basically can I use output paramaters when using Execute reader? If I use
ExecuteNonQuery it works fine. Maybe I just need a bit of better explaining.
All help much appreciated.

Thanks

Gary

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[LoginDBUser2]

@Username varchar(50),

@EncPassword varchar(50),

@Token uniqueidentifier output,

@LastLogin datetime output

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- If user exists return a new GUID token

IF EXISTS (SELECT * FROM Users WHERE Username = @Username AND EncPassword =
@EncPassword)

BEGIN

-- Get the old logon time before we update it

Select @LastLogin = [LastLogin] FROM Users Where Username = @Username AND
EncPassword = @EncPassword

UPDATE Users SET [LastLogin] = getdate() WHERE (Username = @Username AND
EncPassword = @EncPassword)

--Get a New ID

SET @Token = NewID()

--Return all the Users Details

SELECT * FROM Users WHERE Username = @Username AND EncPassword =
@EncPassword

END

ELSE

BEGIN

SET @Token = null

END

END

 

[Shawn Wildermuth (C# MVP)]

Hello Gary,

Yes, Output Params work with ExecuteReader, but are only valid *after* all
the resultsets are complete.


Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
http://adoguy.com