Using Operators with two different Types

Question

I have a DataView that is already created. I built a new DataTable from the existing DataView and made that data a DataView to export in an XlSX. I am trying to do check and see if that last column has an Integer that is greater than 0. If it does it should delete the row. Since these are two different types as one is an object and the other is a double it is not as simple as I thought.

I have looked into the answer on Operator '<' cannot be applied to operands of type 'object' and 'int'

In most cases this would have done it, However somewhere it returns a DBNull.Value as stated. If i try to parse it doesn't appear to like it and it wont build.

private DataView CreateExhaustionDataView()
{
    DataTable dt = BuildFufillmentDataTable();
    DataView dv = new DataView(dt, "", "Department,Date", DataViewRowState.CurrentRows);

    DataTable result = dv.ToTable(true,"Department","Date","Remaining");
    var dr = result.NewRow();
    if (Convert.ToDouble(dr["Remaining"]) >= 0.0)
    {
        dr.Delete();
    }
    DataView dvresult = new DataView(result);
    return dvresult;
}

I receive this error message when I try to convert:

System.InvalidCastException: 'Object cannot be cast from DBNull to other types.' I only receive this error when it goes to export the data. As far as parsing it does not build at all.

Any tips or routes I missed I would greatly appreciate it!

1) This won't compile: `if ( >= 0.0)`. Please provide code that will compile. 2) On exactly what line is the exception being thrown? — 15ee8f99-57ff-4f92-890c-b56153, Oct 11 '19 at 20:13
I updated the code it only errors out at the If statement line. — Average Joe, Oct 11 '19 at 20:15
Before passing `dr["Remaining"]` to Convert.ToDouble(), check if `dr["Remaining"] == DBNull.Value` first. If it equals DBNull.Value, it's null and you'll get this exception. You won't get a regular `null` back from a database column on a null value, just that special constant. — 15ee8f99-57ff-4f92-890c-b56153, Oct 11 '19 at 20:16
Hey wait a minute, you're creating a new row full of nulls and looking at that. Of course it's null. You want the first row in result: `var firstrow = result.Rows.FirstOrDefault()`, and check if `firstrow` is null. — 15ee8f99-57ff-4f92-890c-b56153, Oct 11 '19 at 20:18

15ee8f99-57ff-4f92-890c-b56153 · Accepted Answer · 2019-10-11T21:56:52.400

2

It turns out OP wanted, in essence, to filter result.

You're not looking at the first row, you're creating a new row with the same columns as your DataTable, and looking at that. That new row won't even be in the DataTable until you explicitly add it.

var dr = result.NewRow();

The values in dr are all null, since you just created the poor thing. You're also not checking to see if the column is null, which you ought to do.

if (result.Rows.Count > 0)
{
    var firstrow = result.Rows[0];

    if (firstrow["Remaining"] != DBNull.Value 
        && Convert.ToDouble(firstrow["Remaining"]) >= 0.0)
    {
        //  Or maybe firstrow.Delete(), I don't have time to test this. 
        //  If one doesn't work, try the other. 
        result.Rows.Remove(firstrow);
    }
}

edited Oct 11 '19 at 21:56

answered Oct 11 '19 at 20:22

15ee8f99-57ff-4f92-890c-b56153

36,786
7
62
114

1

Neither way removes the row if it has an integer greater than 0 but this does at least make it to where it doesn't throw an exception and points me in the right direction. Thank you! – Average Joe Oct 11 '19 at 20:54
1

Okay Found out what it is. This literally only does the very first row. If i wanted it to go through and do this to all rows would it have to be placed into a for loop? – Average Joe Oct 11 '19 at 21:38
1

@AverageJoe yes, you’d need a loop. I guess I misread the intention of your code. Also, are you wanting to delete from the result object, or the actual DB table? – 15ee8f99-57ff-4f92-890c-b56153 Oct 11 '19 at 21:39
Basically I am just trying to make the XLSX file come out with just the negative numbers in the ["Remaining"] Column. It can stay in the table and removing it from the result would be fine. Would it be more efficient to just remove it from the whole DB table? – Average Joe Oct 11 '19 at 21:42
1

I would expect removing it from result to be much simpler. I think you might be able to set a filter on it too — even simpler. – 15ee8f99-57ff-4f92-890c-b56153 Oct 11 '19 at 21:46

score 0 · Answer 2 · edited Oct 14 '19 at 15:25

I found a way to simply sort the data table without using the For loop as discussed earlier. I commented the For Loop out to test the sorting and actually works just as well without as much lines of code.

private DataView CreateExhaustionDataView()
{
    DataTable dt = BuildFufillmentDataTable();
    DataView dv = new DataView(dt, "", "Department,Date", DataViewRowState.CurrentRows);
    DataTable result = dv.ToTable(true, "Department", "Date", "Remaining");
    /*for (int i= result.Rows.Count-1; i >= 0; i--)
    {
        if (result.Rows.Count > 0)
        {
            var firstrow = result.Rows[i];

            if (firstrow["Remaining"] != DBNull.Value
                && Convert.ToDouble(firstrow["Remaining"]) > 0.0)
            {
                firstrow.Delete();
            }
        }
    }*/ 
    DataView dvresult = new DataView(result, "Remaining <= 0", "Department, Date", DataViewRowState.CurrentRows);
    return dvresult;
}

Using Operators with two different Types

2 Answers2