Solved

Handling null values in Command.ExecuteScalar using C#

Posted on 2015-02-21
9
135 Views
Last Modified: 2015-02-23
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

0
Comment
Question by:moe57
  • 5
  • 3
9 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40623302
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40623322
Just to explain a little the decimal? means that the variable is a decimal OR a null.
0
 

Author Comment

by:moe57
ID: 40623860
sometimes it is null but also sometimes we get some decimal values too
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40624215
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
 

Author Comment

by:moe57
ID: 40625545
i am still getting this error:  Specified cast is not valid.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40625603
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
 

Author Comment

by:moe57
ID: 40625608
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
 

Author Comment

by:moe57
ID: 40625609
thanks you have helped me in the right direction.
0
 

Author Closing Comment

by:moe57
ID: 40625673
thx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question