How do I solve this error. Object cannot be cast from dbnull to orther types

Question

0.00/5 (No votes)

See more:

I have a problem here i want to get the subtotal of each **BusType**. My problem now is that i am getting this error `Object cannot be cast from DBNull to orther types`. I do get the result on DataTable when i put the Breakpoint on `return ds` when i am trying to proceed it display that error `Object cannot be cast from DBNull to orther types` . How can i solve this.
Needed Output

SQL

BusType        |BusName   | BusL  |BusInterest|BusAdmin| BusPenalty|TotalBusCollected
1 - ACCOUNTING |HIGHVELD  |-23.91 | 0         |-22.84  | 0         |-46.75
1 - ACCOUNTING |BHP       |-50.81 |-79.21     |-76     |-20.02     |-226.04
--------------------------+-------+-----------+--------+-----------+-----------------
Subtotal                  |-74.72 |-79.21     |-98.84  |-20.02     |-272.79
--------------------------+-------+-----------+--------+-----------+-----------------
2 - FOOD         |SAB       |-14.18 |-435.97    |-2.57   |-67.55     |-520.27
2 - FOOD         |DISTIL    |-43.05 |0          |-66,59  |0          |-109.64
--------------------------+-------+-----------+--------+-----------+-----------------
Subtotal                     |-57.23 |-435.97    |-69.16  |-67.55     |-629.91
--------------------------+-------+-----------+--------+-----------+-----------------
3 - MINING     |ANGLOGOLD |-4.43  |0          |-72     |0          |-76.43
--------------------------+-------+-----------+--------+-----------+-----------------
Subtotal                 |-4.43  |0          |-72     |0          |-76.43

Code

C#

var query = (from _transaction in _entities.Transactions
    join _cd in _entities.Organisations on _transaction.Refno equals _cd.Refno
    join _b in _entities.BusinessType on _transaction.BusinessTypeCode equals _b.BusinessTypeCode
              group new
              {
                  _trans = _transaction,
                   cd = _cd,
               }
               by new { _transaction.BusinessTypeCode,_transaction.Refno, _cd.BusinessName, _b.Description } into _group
               orderby _group.Key.BusinessTypeCode
               select new
                       {
                          BusinessTypeCode = _group.Key.BusinessTypeCode,
                          BusType = _group.Key.BusinessTypeCode + " - " +_group.Key.Description,
                          BusName = _group.Key.BusinessName,
                          BusL = _group.Sum(x=&gt;x._trans.BusL),
                          BusInterrest = _group.Sum(x =&gt; x._trans.BusInterrest),
                          BusAdmin = _group.Sum(x =&gt; x._trans.BusAdmin),
                          BusPenalty = _group.Sum(x =&gt; x._trans.BusPenalty),
                          TotalBusCollected =_group.Sum(x=&gt;x._trans.TotalBusCollected)
                       });
                DataTable dt=new DataTable();
                DataSet ds = new DataSet();
                ds.Tables.Add(query.CopyToDataTable());
                ds.Tables[0].TableName = "Table1";
    double BusL=0; double BusInterrest=0; double BusAdmin=0;
    double BusPenalty=0; double TotalBusCollected=0; string Title = string.Empty;
for(int I=0; I&lt;=dt.Rows.Count - 1; i++)
{
  if(i &gt; 0)
  {
     if(dt.Rows[i]["BusType"].ToString().ToLower() != dt.Rows[i]]["BusType"].ToString().ToLower())
     {
        dt.Rows.InsertAt(dt.NewRow(),i);
        dt.Rows[i]["BusType"]=Title,
        Title = string.Empty;        
        dt.Rows[i]["BusL"]=BusL;
        BusL=0;
        dt.Rows[i]["BusInterrest"]=BusInterrest;
        BusInterrest=0;
        dt.Rows[i]["BusAdmin"]=BusAdmin;
        BusAdmin=0;
        dt.Rows[i]["BusPenalty"]=BusPenalty;
        BusPenalty=0;
        dt.Rows[i]["TotalBusCollected"]=TotaBusCollected;
        TotalBusCollected=0;
        i++;
     }
   }
   Title = "Subtotal for "+dt.Rows[i]["BusType"];
   BusL +=Convert.ToDouble(dt.Rows[i]["BusL"]);
   BusInterrest+=Convert.ToDouble(dt.Rows[i]["BusInterrest"]);
   BusAdmin+=Convert.ToDouble(dt.Rows[i]["BusAdmin"]);
   BusPenalty+=Convert.ToDouble(dt.Rows[i]["BusPenalty"]);
   TotalBusCollected+=Convert.ToDouble(dt.Rows[i]["TotalBusCollected"])
}
return ds;

What I have tried:

double BusL=0; double BusInterrest=0; double BusAdmin=0;
double BusPenalty=0; double TotalBusCollected=0; string Title = string.Empty;
for(int I=0; I<=dt.Rows.Count - 1; i++)
{
if(i > 0)
{
if(dt.Rows[i]["BusType"].ToString().ToLower() != dt.Rows[i]]["BusType"].ToString().ToLower())
{
dt.Rows.InsertAt(dt.NewRow(),i);
dt.Rows[i]["BusType"]=Title,
Title = string.Empty;
dt.Rows[i]["BusL"]=BusL;
BusL=0;
dt.Rows[i]["BusInterrest"]=BusInterrest;
BusInterrest=0;
dt.Rows[i]["BusAdmin"]=BusAdmin;
BusAdmin=0;
dt.Rows[i]["BusPenalty"]=BusPenalty;
BusPenalty=0;
dt.Rows[i]["TotalBusCollected"]=TotaBusCollected;
TotalBusCollected=0;
i++;
}
}
Title = "Subtotal for "+dt.Rows[i]["BusType"];
BusL +=Convert.ToDouble(dt.Rows[i]["BusL"]);
BusInterrest+=Convert.ToDouble(dt.Rows[i]["BusInterrest"]);
BusAdmin+=Convert.ToDouble(dt.Rows[i]["BusAdmin"]);
BusPenalty+=Convert.ToDouble(dt.Rows[i]["BusPenalty"]);
TotalBusCollected+=Convert.ToDouble(dt.Rows[i]["TotalBusCollected"])
}
return ds;

Posted 23-Mar-16 3:22am

Member 10389383

Updated 23-Mar-16 4:41am

v3

Add a Solution

2 solutions

Solution 1

A DataRow-cell (usually, and here, apparently) contains DBNull instead of null. (To be exact, it contains the single instance of the class DBNull.)

If you didn't expect this possibility, you might have a data integrity problem and you might want to check if you want to make some columns non-nullable and insert 0's (zeroes) instead of nothing (null).

If you want to allow nulls, you have to take care for that possibility. The best way for that, which also replaces your Convert.ToDouble(..)-calls is to use the generic Field<T>(column)-method of DataRow:

C#

double busL = dt.Rows[i].Field<double>("BusL");

This will return the default value for double (which is 0) in case the row-cell contains DBNull and the contained double-value otherwise.

Posted 23-Mar-16 3:45am

Sascha Lefèvre

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)

Member 10389383 · Accepted Answer · 2016-03-23T04:36:00

Solution 2

@Sacha Lefevre I am still geting the same error

C#

Title = "Subtotal for "+dt.Rows[i].Field<string>("BusType");
BusL +=dt.Rows[i].Field<double>("BusL");
BusInterrest+=dt.Rows[i].Field<double>("BusInterrest");
BusAdmin+=dt.Rows[i].Field<double>("BusAdmin");
BusPenalty+=dt.Rows[i].Field<double>("BusPenalty");
TotalBusCollected+=dt.Rows[i].Field<double>("TotalBusCollected");

Posted 23-Mar-16 4:36am

Member 10389383

Updated 23-Mar-16 4:37am

v2

Comments

Sascha Lefèvre 23-Mar-16 10:44am

Please use the button "Have a Question or Comment" below my solution if you want me to get notified of your comment, I just saw this by chance.

Please run your program in debug mode and observe which line the exception message window is pointing to and tell me.