SQLCommand.ExecuteScalar() - why it throws a System.NullReferenceException?

Question

Hi,

Could anyone notice what could be wrong with the following function:

public string Login(string username, string password)
    {
        string result = "";
        string select = "SELECT user_id FROM [user] WHERE username = @username AND password = @password";
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(select, conn);
        cmd.Parameters.AddWithValue("username", username);
        cmd.Parameters.AddWithValue("password", password);
        int userID = 0;
        try
        {
            conn.Open();
            userID = (int)cmd.ExecuteScalar();
            if(userID > 0)
            {
                result = addSession(userID);
            }
        }
        catch(Exception ex)
        {
            string sDummy = ex.ToString();
        }
        return result;
    }

Don't know why the line `userID = (int)cmd.ExecuteScalar(); throws an exception.

Thanks

Answer 1

You should consider modifying this segment of code:

try
{
    conn.Open();
    userID = (int)cmd.ExecuteScalar();
    if(userID > 0)
    {
        result = addSession(userID);
    }
 }
 catch(Exception ex)
 {
    string sDummy = ex.ToString();

 }
 finally // add this to ensure the connection is closed!
 {
     if (conn != null)
       conn.Close();
 }

Answer 2

Not sure, but you might need the "@" for the parameter names:

...AddWithValue("@username", username);

Answer 3

Is it possible that the scalar is null if the supplied credentials are not found in the database?

Answer 4

Most likely there is no row in the table with that user/password. The docs for ExecuteScalar say that it returns null if the result set is empty, and you can't cast null to int.