ExecuteScalar gives NullReferenceException

Question

Please look at this code when it goes for ExecuteScalar it gives an error : NullReferenceException

I tested it inside sql server and it worked

    selectedPassengerID = 0;

    //SqlCommand command = GenericDataAccess.CreateCommand();


    // 2nd test
    string connectionString = "";
                        SqlConnection conn;

        connectionString = ConfigurationManager.ConnectionStrings["ConnST-MHM"].ConnectionString;
        conn = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand();
    command.CommandType = CommandType.StoredProcedure ;
    command.Connection = conn;
    command.CommandText = "SearchForPassenger";

    SqlParameter param;

    param = command.CreateParameter();
    param.ParameterName = "@name";
    param.Value = pName; // Session[""];
    param.DbType = DbType.String;
    command.Parameters.Add(param);


    param = command.CreateParameter();
    param.ParameterName = "@flightDate";
    param.Value = date; 
    param.DbType = DbType.String;
    command.Parameters.Add(param);

    param = command.CreateParameter();
    param.ParameterName = "@ticketNo";
    param.Value = ticketNumber; 
    param.DbType = DbType.Int32;
    command.Parameters.Add(param);

    int item;

    command.Connection.Open();
    item = (int)command.ExecuteScalar();

Answer 1

I have encapsulated most of my SQL logic in a DAL. One of these DAL methods pulls scalar Ints using the following logic. It may work for you:

  object temp = cmnd.ExecuteScalar();
  if ((temp == null) || (temp == DBNull.Value)) return -1;
  return (int)temp;

I know that you have entered a lot of code above but I think that this is really the essence of your problem. Good luck!

Answer 2

ExecuteScalar returns null if no records were returned by the query (eg when your SearchForPassenger stored procedure returns no rows).

So this line:

item = (int)command.ExecuteScalar();

Is trying to cast "null" to an int in that case. That'll raise a NullReferenceException.

As per Mark's answer that just poppped up, you need to check for null:

object o = command.ExecuteScalar();
item = o == null ? 0 : (int)o;