CODEWITHSUNDEEP
c#sql-server-2008c#-4.0stored-proceduresado.net
user2599269
user2599269

Reputation: 571

ExecuteScalar() method is not working properly

I have the following function;

public int GetLoginClaim(IsValidLogin Obj)
{
    SqlConnection DBCon = new SqlConnection(ConString);
    SqlCommand CmdSelect = new SqlCommand("IsValidLogin", DBCon);
    CmdSelect.CommandType = CommandType.StoredProcedure;
    DBCon.Open();
    try
    {
        CmdSelect.Parameters.AddWithValue("@UserName", Obj.Username);
        CmdSelect.Parameters.AddWithValue("@Password", Obj.Password);

        return (int)CmdSelect.ExecuteScalar();
    }
    catch
    {
        throw;
    }
    finally
    {
        CmdSelect.Dispose();
        DBCon.Close();
        DBCon.Dispose();
    }
}

And the following stored procedure on which it depends;

USE [SMania]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[IsValidLogin]
@UserName varchar(32),
@Password varchar(32)
As
if exists(Select * From NewClientTB Where UserName = @UserName And Password = @Password)
 return(1)
else
 return(0)

The stored procedure is working correctly when executed on sql server, but when called from the function i posted above, it gives error. The above function is in my data access layer. So in the stack trace, i am having the following error on the above function: NullReferenceException: Object reference not set to an instance of an object. Can anyone fix this problem?

Upvotes: 0

Views: 1864

Answers (1)

dmay
dmay

Reputation: 1325

You need different approach for SPs with return status

Check the example on this MSDN article http://msdn.microsoft.com/en-us/library/ms378371(v=sql.90).aspx

If you want to use ExecuteScalar you need to replace return(X) with select X in SP because it "returns the first column of the first row in the result set" but there is no result set in your SP only return value.

This is how you can get return status in C#

CmdSelect.Parameters.AddWithValue("@UserName", Obj.Username);
CmdSelect.Parameters.AddWithValue("@Password", Obj.Password);

var return_state = CmdSelect.Parameters.Add("@ReturnVal", SqlDbType.Int);
return_state.Direction = ParameterDirection.ReturnValue;

CmdSelect.ExecuteNonQuery();
return (int)return_state.Value;

Upvotes: 2

Related Questions