object of executescalar always null 1

[copeZero]

Hi i've been fighting with this one, no matter what i do i can't get the return value, this i swhat i have:

This is the storeProc which does update the table with the parameter in given in the c# code:

CREATE PROCEDURE dbo.sp_StoreSec
(
@MemberID int,
@SecurStr nvarchar(300),
@Pass nvarchar(40),
@Question nvarchar(50),
@Answer nvarchar(40)
)
AS
SET NOCOUNT ON

INSERT INTO [_Credent] (MID,CredtStr) VALUES (@MemberID, @SecurStr)


UPDATE [_Info] SET pw=@Pass, sQ=@Question, sA=@Answer, isTemp=0
WHERE MID=@MemberID


SELECT TOP 1 Gender +'|'+ Convert(VarChar(7), PersonalID) +'|'+ Convert(VarChar(7), EMRID)
FROM [_MemberDemographics] 
WHERE MemberID = @MemberID
RETURN
GO

In the code behind page i have:

...
SqlCommand cmd = new SqlCommand("sp_StoreSec", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = Convert.ToInt32(Session["MemberID"]);

cmd.Parameters.Add("@SecurStr", SqlDbType.NVarChar).Value = (ipaddress + "^" + hostName + "^" + OS + "^" + browser);

cmd.Parameters.Add("@Pass", SqlDbType.NVarChar).Value = Request.Form["p"].ToString();

cmd.Parameters.Add("@Question", SqlDbType.NVarChar).Value = Request.Form["q"].ToString();

cmd.Parameters.Add("@Answer", SqlDbType.NVarChar).Value = Request.Form["a"].ToString();

try
{
connection.Open();
object SessionStr = cmd.ExecuteScalar();
string dbstr = SessionStr.ToString();
connection.Close();

no matter what i try object SessionStr is always null, where as it should be the value from the select statement in the stored procedure. Any clues as to why i can't get a value returned?

Much appreciated.

 

[AlexCuse]

First of all, the way you are trying to return the output, execute scalar will not work (it will only work if you return a rowset, NOT a param). If you want to do parameters try changing your proc to this (changes in bold, comments added):

CREATE PROCEDURE dbo.sp_StoreSec
(
@MemberID int,
@SecurStr nvarchar(300),
@Pass nvarchar(40),
@Question nvarchar(50),
@Answer nvarchar(40) ,
[b]@SomeVal varchar(666) OUTPUT[/b] --need to specify this as output parameter
)
AS
SET NOCOUNT ON

INSERT INTO [_Credent] (MID,CredtStr) VALUES (@MemberID, @SecurStr)


UPDATE [_Info] SET pw=@Pass, sQ=@Question, sA=@Answer, isTemp=0
WHERE MID=@MemberID


SELECT [b]@SomeVal = [/b]TOP 1 Gender +'|'+ Convert(VarChar(7), PersonalID) +'|'+ Convert(VarChar(7), EMRID)
FROM [_MemberDemographics]
WHERE MemberID = @MemberID
--add an order by here, or TOP 1 is meaningless

RETURN
--get rid of the GO

I am not 100% sure on this (I normally use a rowset and executeScalar()) but I believe you will need to add @SomeVal as an OUTPUT parameter to your command object, then get its' value AFTER executing (Just Execute(), not ExecuteScalar()).

If you want to use ExecuteScalar to get the value, change your proc to this:

CREATE PROCEDURE dbo.sp_StoreSec
(
@MemberID int,
@SecurStr nvarchar(300),
@Pass nvarchar(40),
@Question nvarchar(50),
@Answer nvarchar(40)
)
AS
SET NOCOUNT ON

INSERT INTO [_Credent] (MID,CredtStr) VALUES (@MemberID, @SecurStr)


UPDATE [_Info] SET pw=@Pass, sQ=@Question, sA=@Answer, isTemp=0
WHERE MID=@MemberID


SELECT TOP 1 Gender +'|'+ Convert(VarChar(7), PersonalID) +'|'+ Convert(VarChar(7), EMRID)
FROM [_MemberDemographics]
WHERE MemberID = @MemberID

Note there is no RETURN (that is actually the only change I made). This means it will return the rowsets of any select statements in the proc (in this case only one). Using this modified proc, ExecuteScalar() should work for you.

I hope this helps,

Alex



[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[copeZero]

Thanks Alex, you taught me well!

 

[AlexCuse]

Glad it helped

Output parameters are (IMO) one of the more awkward things in SQL. I always thought the return should just give you a rowset (like you did too, apparently).


[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom