CODEWITHSUNDEEP
c#sqlsql-serverwinforms
Marek
Marek

Reputation: 3575

ExecuteScalar if DBNull issue

I was trying to handle DbNull exception like this:

string sql_com_sumcastka = "SELECT SUM(price) AS sumprice FROM kliplat WHERE akce='" + zakce.Text + "' AND year=" + year;
            SqlCommand sc2 = new SqlCommand(sql_com_sumprice, spojeni);
            spojeni.Open();

            if (sc2 != DBNull.Value)
            {
                int result = Convert.ToInt32(sc2.ExecuteScalar());
            }
            else
            {
                int result = 0;
            }
            spojeni.Close();

            string sql_com_update_sum = "UPDATE zajezd SET s_prijmy=@s_prijmy WHERE akce='"+zakce.Text+"' AND year="+year;
            SqlCommand sc3 = new SqlCommand(sql_com_update_sum,spojeni);

            sc3.Parameters.AddWithValue("@s_prijmy", result );
            spojeni.Open();
            sc3.ExecuteNonQuery();
            spojeni.Close();

But as I don't know how to properly handle if result is DBNull I get this erros: Operator '"=' cannot be applied to operands of type system.Data.SqlClient.SqlCommand and System.Dbnull

and

The name 'result' does not exist in the current context

My problem is this line of code:

if (sc2 != DBNull.Value)
        {
            int result = Convert.ToInt32(sc2.ExecuteScalar());
        }
        else
        {
            int result = 0;
        }

Thanks for helping.

Upvotes: 2

Views: 10747

Answers (4)

Steve
Steve

Reputation: 216323

ExecuteScalar doesn't return DBNull (unless..., please read comments below) but null and you need to test the return value of ExecuteScalar not the SqlCommand that executes the command

   int sumOfPrice = 0;
   object result = sc2.ExecuteScalar();
   if(result != null)
       sumOfPrice = Convert.ToInt32(result);

From MSDN

ExecuteScalar returns 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.

As a side note, do not use string concatenation to build a command text to pass to your database. You risk Sql Injection and parsing erroros. Use instead a parameterized query like this

string sql_com_sumcastka = "SELECT SUM(price) AS sumprice FROM kliplat " + 
                           "WHERE akce=@zak AND year=@year";
SqlCommand sc2 = new SqlCommand(sql_com_sumprice, spojeni);
sc2.Parameters.AddWithValue("@zak", zakce.Text);
sc2.Parameters.AddWithValue("@year", year);

Upvotes: 6

xdemon19
xdemon19

Reputation: 1

I know the answer is late but this is the simplest answer that I have found 

SELECT (SUM(price),0) FROM kliplat

If the result is null it will be replaced with 0

Upvotes: 0

Scott Chamberlain
Scott Chamberlain

Reputation: 127573

Here is the correct way to do that

var objResult = sc2.ExecuteScalar();

if (objResult != DBNull.Value && objResult != null )
{
    int result = (int)objResult; //you can just do a normal cast, "SUM(X)" returns a int.
}
else
{
    int result = 0;
}

Upvotes: 6

Tejs
Tejs

Reputation: 41246

A SqlCommand can never be compared to DBNull.Value in your example. What it sounds like you want is to check the result of your execute scalar invocation to see if that is null:

var result = sc2.ExecuteScalar();

if(result as DBNull == null)
{
    // value is not null, do processing
}

Upvotes: 0

Related Questions