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

moe57Asked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Yes, and because there is a possibility that the return value can be a null you need to guard against it so that the end user does not have the program crash on them. There for my solution which does that.

Repost of my previous solution

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

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

Open in new window

0
 
Fernando SotoRetiredCommented:
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

0
 
AndyAinscowFreelance programmer / ConsultantCommented:
Just to explain a little the decimal? means that the variable is a decimal OR a null.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
moe57Author Commented:
sometimes it is null but also sometimes we get some decimal values too
0
 
moe57Author Commented:
i am still getting this error:  Specified cast is not valid.
0
 
Fernando SotoRetiredCommented:
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?
0
 
moe57Author Commented:
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

0
 
moe57Author Commented:
thanks you have helped me in the right direction.
0
 
moe57Author Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.