asked on

Handling null values in Command.ExecuteScalar using C#

I am trying to get some values from the database but when no values are returned i get this error" "Specified cast is not valid" but the code works fine when data comes back from the database specially when Checked = 1 is true. How can i overcome with null values in Command.ExecuteScalar?

     using (SqlConnection con = new SqlConnection(strConnString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = @"select DATEDIFF(minute, Min(FullDatetime), Max(FullDatetime)) / 60.0 as hours                 
                      from   myTable
                       where userid = @UserID
                        and DT_Submitted = (select CAST(FLOOR( CAST( GETDATE() AS FLOAT ) )AS DATETIME))
                         and Checked = 1";

                    cmd.Parameters.AddWithValue("@UserID", tempUser.ToString());
                    con.Open();
                    decimal result = (decimal)cmd.ExecuteScalar();
                    lblHours.Text = result.ToString("0.#") + " Hours";

                    con.Close();
                    con.Dispose();
                }
            }

Open in new window

it is failing at this line

decimal result = (decimal)cmd.ExecuteScalar();

Open in new window

Fernando Soto

Hi moe57;

You can change your code like this.

decimal? resultNullable = (decimal?)cmd.ExecuteScalar();
decimal result = 0.0m;

if ( resultNullable.HasValue )
{
    result = resultNullable.Value;    
}

Open in new window

AndyAinscow

Just to explain a little the decimal? means that the variable is a decimal OR a null.

moe57

ASKER

sometimes it is null but also sometimes we get some decimal values too

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

moe57

ASKER

i am still getting this error: Specified cast is not valid.

Fernando Soto

On what line are you getting the exception?
What is the full exception message and inner exception message.
Please post the code as you implemented it in the program.
What is the database data type being returned from the database?

moe57

ASKER

i have resolved my problem

 if (cmd.ExecuteScalar() == DBNull.Value)
                    {
                        lblHours.Text = "you have not selected any hours";
                    }else
                    {
                        decimal result = (decimal)cmd.ExecuteScalar();
                        lblHours.Text = result.ToString("0.#") + " Hours";
                    }

Open in new window

moe57

ASKER

thanks you have helped me in the right direction.

moe57

ASKER

thx