CODEWITHSUNDEEP
c#asp.netsqlado.net
EverTheLearner
EverTheLearner

Reputation: 7200

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

Using the ExecuteScalar method in the SQL Command object, how can you check to see if the result set is empty? I am using ASP.net, C#, and MS SQL 2008. Right now when I run the following code the Response.Write returns a 0 when the resultset is empty. But I would like to differentiate between 0 and empty resultsets because there are actual 0 values in my database.

Here is the current code behind:

cmd = new SqlCommand("usp_test", cn);
cmd.CommandType = CommandType.StoredProcedure;

cn.Open();
TestOuput = Convert.ToInt32(cmd.ExecuteScalar());
cn.Close();

Response.Write(TestOutput);

Thank you.

Upvotes: 7

Views: 11919

Answers (6)

Suman Banerjee
Suman Banerjee

Reputation: 1961

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);

Upvotes: 0

bdukes
bdukes

Reputation: 156005

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.

Upvotes: 0

Kon
Kon

Reputation: 27441

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.

Upvotes: 12

Daniel Brückner
Daniel Brückner

Reputation: 59685

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
}

Upvotes: 3

Daniel
Daniel

Reputation: 1793

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.

Upvotes: 0

Canavar
Canavar

Reputation: 48088

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.

Upvotes: 0

Related Questions