object cannot be cast from dbnull to other types

Question

1.00/5 (1 vote)

See more:

SQL

I am making a method but it is giving an error like 'Object cannot be cast from DBNull to other types' below is my code:

{
     SqlConnection con = new SqlConnection(@"Data Source=123-PC;Initial Catalog=bcounts;Persist Security Info=True;User ID=Saba;Password=123");
        con.Open();
        SqlCommand cmd = new SqlCommand("select Accountno,Acct_Name,Dr_amount,Cr_amount from Gl_Transaction where Value_Date between '" + fromdate + "' and '" + todate + "' order by Accountno ASC" , con);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet("trial");

        adp.Fill(ds);
        decimal crsum = 0;
        decimal drsum = 0;
        decimal balance = 0;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {

            if (Convert.ToDecimal(dr[2]) > 0)
            {
                balance = balance + Convert.ToDecimal(dr[2]);
                drsum += Convert.ToDecimal(dr[2]);
            }
            else
            {
                balance = balance - Convert.ToDecimal(dr[3]);
                crsum += Convert.ToDecimal(dr[3]);
            }
        }

Posted 16-Jul-14 6:29am

Member 10690757

Add a Solution

Comments

[no name] 16-Jul-14 12:32pm

Okay so your query is returning null values and you are trying to convert a null value to something else. You can't do that.

Member 10690757 16-Jul-14 12:35pm

in ssms this query is working fine

[no name] 16-Jul-14 12:53pm

Whether it works in SSMS or not is irrelevant. This is not SSMS, this is .NET code and the query is not the same. Use a proper parameterized query and your problem might just resolve itself.

Member 10690757 16-Jul-14 13:37pm

how ?

Member 10690757 16-Jul-14 13:38pm

Kindly give me the solutin

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-07-16T08:11:00

Although Wes correctly says you should use parametrized queries, that will not solve you problem. But it might well save your database from SQL Injection attack which can easily damage or destory your database. Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:

C#

SqlCommand cmd = new SqlCommand("select Accountno,Acct_Name,Dr_amount,Cr_amount from Gl_Transaction where Value_Date between @FD AND @TD order by Accountno ASC" , con);
cmd.Parameters.AddWithValue("@FD", fromdate);
cmd.Parameters.AddWithValue("@TD", todate);

But that won't solve you immediate problem, whinch is pretty simple: one or more of your Dr_amount or Cr_amount values is not specified in your DB (it's a null value) so the database returns a special result DBNull.Value to indicate that.
You need to test for it before you use the actual value.

C#

decimal debit = 0;
if (dr[2] != DBNull.Value)
   {
   debit = Convert.ToDecimal(dr[2]);
   }
decimal credit = 0;
if (dr[3] != DBNull.Value)
   {
   credit = Convert.ToDecimal(dr[3]);
   }
if (debit > 0)
   {
   balance = balance + debit;
   drsum += debit;
   }
else
   {
   balance = balance - credit;
   crsum += credit;
   }