SQL execute scalar returning NULL

Question

queryShelf = "SELECT * FROM shelftable WHERE ShelfId= @ShelfId";

//Create Mysql Command
MySqlCommand cmd = new MySqlCommand(queryShelf, connection);

cmd.Parameters.Add(new MySqlParameter("@ShelfId", MySqlDbType.VarChar)).Value = MainWindow.shelfIds[i];

//ExecuteScalar will return one value
int Count = int.Parse(cmd.ExecuteScalar() + "");

Answer 1

ExecuteScalar is used to return a single value, you are selecting complete records. So normally you use ExecuteReader and use Read to get all records.

But actually you can use ExecuteScalar with SELECT *. What happens is that the first column of the first row in the result set is returned, or a null reference if the result set is empty.

Since you get NULL it seems that the filter doesn't return a record.

Since you want a count you could change your query to:

queryShelf = "SELECT COUNT(*) FROM shelftable WHERE ShelfId= @ShelfId";
// ...
int Count = (int) cmd.ExecuteScalar();

Now you never get null but the count of records, 0 if no record exists with this ShelfId.