Solved

Handling null values in Command.ExecuteScalar using C#

Posted on 2015-02-21
9
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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