Solved

Handling null values in Command.ExecuteScalar using C#

Posted on 2015-02-21
9
129 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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