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() + "");

What is your question exactly? What is the first column of the first row that query returns? — Soner Gönül, Jun 15 '15 at 11:36
Don't use * in the query. You need to return a value, not an "array" of values — Marko Juvančič, Jun 15 '15 at 11:37

score 1 · Accepted Answer · answered Jun 15 '15 at 11:38

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.

SQL execute scalar returning NULL

1 Answers1