ExecuteScalar() method is not working properly

Question

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?

yes, check the object and see if obj.username or obj.password is null — user710502, Jul 30 '13 at 01:49
Try using select instead of return `Select 1 else Select 0` and in .net you can use ExecuteReader() and use data[0] value — Nilesh, Jul 30 '13 at 02:13
Almost all cases of `NullReferenceException` are the same. Please see "[What is a NullReferenceException in .NET?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-in-net)" for some hints. — John Saunders, Jul 30 '13 at 03:33

score 2 · Accepted Answer · edited Jul 30 '13 at 03:31

2

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;

edited Jul 30 '13 at 03:31

John Saunders

160,644
26
247
397

answered Jul 30 '13 at 01:48

dmay

1,340
8
23

That example is probably in java. I could not get the idea how to do it in C# using ado.net – user2599269 Jul 30 '13 at 01:53
Exception disappeared but does not return 1 on successful match – user2599269 Jul 30 '13 at 02:09
I just tried this code with simplified procedure with `if(@UserName=@Password)` and it worked correctly for me. I mean it returned 1 for same values and 0 for different. Here is the code http://pastebin.com/p6sNiujC I used LinqPad to test it. – dmay Jul 30 '13 at 02:33

ExecuteScalar() method is not working properly

1 Answers1