Getting error Cannot cast DBNull.Value to type 'System.Int32

Question

I am grouping data in datatable and summing up one column using linq but while run time i am getting error stating that

Cannot cast DBNull.Value to type 'System.Int32. Please use a nullable type" Below is my code

Dim resultStyle = invData.Tables(0).AsEnumerable() _
      .GroupBy(Function(v) New With {Key .InvestorStyleID = v.Field(Of Integer)("InstitutionalInvestorStyleID"), Key .StyleName = Not IsNothing(v.Field(Of String)("InstitutionalInvestorStyleName"))}) _
      .Select(Function(v) New With {Key .InvestorStyleID = v.Key.InvestorStyleID, Key .StyleName = v.Key.StyleName, Key .Sum = v.Sum(Function(r) Double.Parse(r.Item("k001ICGeo").ToString()))})

Kindly suggest me how to avoid taking null value.I am getting error on second line

.GroupBy(Function(v) New With {Key .InvestorStyleID = v.Field(Of Integer)("InstitutionalInvestorStyleID"), Key .StyleName = Not IsNothing(v.Field(Of String)("InstitutionalInvestorStyleName"))}).

I don't want to take the null value.

I believe the problem is the InstitutionalInvestorStyleID. Check if this is nullable in database. Try v.Field(Of Integer?)("InstitutionalInvestorStyleID") — shadow
– shadow, Commented Feb 14, 2017 at 12:10

MarmiK · Accepted Answer · 2017-02-15 05:56:06Z

1

DataTables do not store null values, instead, they store DBNull.

Your IsNothing is therefore not helpful.

So, you need to compare with DBNull.Value (a singleton of DBNull).

edited Feb 15, 2017 at 5:56

MarmiK

5,8156 gold badges47 silver badges51 bronze badges

answered Feb 14, 2017 at 12:03

Ricardo Peres

14.8k5 gold badges64 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shadow · Accepted Answer · 2017-02-14 12:15:57Z

0

Try something like this:

Dim resultStyle = invData.Tables(0).AsEnumerable() _
                        .GroupBy(Function(v) New With {Key .InvestorStyleID = v.Field(Of Integer?)("InstitutionalInvestorStyleID").GetValueOrDefault, 
                                                        Key .StyleName = If( String.IsNullOrEmpty(v.Field(Of String)("InstitutionalInvestorStyleName")),"", v.Field(Of String)("InstitutionalInvestorStyleName"))}) _
                        .Select(Function(v) New With {Key .InvestorStyleID = v.Key.InvestorStyleID, Key .StyleName = v.Key.StyleName, Key .Sum = v.Sum(Function(r) Convert.ToDouble(r.Item("k001ICGeo"))))})

answered Feb 14, 2017 at 12:15

shadow

1,9031 gold badge17 silver badges25 bronze badges

1 Comment

Manish Gupta Over a year ago

Thanks for your help. I am not getting any error now but i don't want to take that particular row if the stylename is null or "".How to avoid taking that row.

Rahul Singh · Accepted Answer · 2017-02-14 14:13:51Z

The error message is because you have null values in column which you are trying to cast while grouping, so simply change it to Nullable type. Also, to get only the records which don't have null values apply a filter using Where clause like this:-

Dim resultStyle = invData.Tables(0).AsEnumerable() _
    .GroupBy(Function(v) New With {Key .InvestorStyleID = v.Field(Of Integer?)("InstitutionalInvestorStyleID"), Key .StyleName = Not IsNothing(v.Field(Of String)("InstitutionalInvestorStyleName"))}) _
    .Where(Function(x) x.Key.InvestorStyleID  IsNot Nothing) _
    .Select(Function(v) New With {Key .InvestorStyleID = v.Key.InvestorStyleID, Key .StyleName = v.Key.StyleName, Key .Sum = v.Sum(Function(r) Double.Parse(r.Item("k001ICGeo").ToString()))})

I have also added a sample fiddle to show how it works with some sample data.

Fiddle.

Collectives™ on Stack Overflow

Getting error Cannot cast DBNull.Value to type 'System.Int32

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related