Using the SQL Command object, how can you check to see if the result set is empty?

7 thoughts on “Using the SQL Command object, how can you check to see if the result set is empty?”

1. user November 30, -0001 at 12:00 am

   Why not at the SQL put ISNULL(@Return,0). Then,everything can be convert to Int32.

   Reply ↓
2. user November 30, -0001 at 12:00 am

   DbCommand.ExecuteScalar() returns the first column of the first row, or null if the result is empty. Your problem is caused by Convert.ToInt32() because it returns 0 for null.

   You have to check the value returned by ExecuteScalar() for null and only call Convert.ToInt32() if it is not null.

   Object result = command.ExecuteScalar();
   
   if (result != null)
   {
      Int32 value = Convert.ToInt32(result);
   }
   else
   {
      // Handle the empty result set case
   }
   

   Reply ↓
3. user November 30, -0001 at 12:00 am

   Check out the definition of ExecuteScalar. It returns an Object, which will have a null reference if the result set is empty.

   The reason you are seeing zero is that Convert.ToInt32 returns a zero when given null. You need to check the return value from ExecuteScalar before you convert it to an int.

   Reply ↓
4. user November 30, -0001 at 12:00 am

   As you can see in here you can check if the result is null :

   Return Value Type: System.Object
   The first column of the first row in
   the result set, or a null reference
   (Nothing in Visual Basic) if the
   result set is empty.

   Reply ↓
5. user November 30, -0001 at 12:00 am

   Execute scalar will return the first column of the first row in the result set. If there are no results it should return null.

   My guess is that your stored procedure is returning a count and not the dataset, which is why you are seeing a 0.

   Reply ↓
6. user November 30, -0001 at 12:00 am

   ExecuteScalar returns null if the result set is empty (as @fallen888, et al. have said). The reason you are seeing zero is that Convert.ToInt32 returns a zero when given null. You need to check the return value from ExecuteScalar before you convert it to an int.

   Reply ↓
7. user November 30, -0001 at 12:00 am

   cmd = new SqlCommand("usp_test", cn);
   cmd.CommandType = CommandType.StoredProcedure;
   cn.Open();
   Object outPut=cmd.ExecuteScalar();
   if(outPut!=null)
   TestOuput = Convert.ToInt32(outPut);
   else
   //Return Empty Set
   cn.Close();
   
   Response.Write(TestOutput);
   

   Reply ↓