[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Handling null values in Command.ExecuteScalar using C#

Posted on 2015-02-21
9
Medium Priority
?
222 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 64

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 45

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 64

Accepted Solution

by:
Fernando Soto earned 1500 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 64

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

591 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