Linq to strongly typed DataTable and Null issue for date column

Question

Here is my code

 Dim dr2 As PBRuleData.PBRuleRow
                        Dim query As IEnumerable(Of PBRuleData.PBRuleRow) = From s In Me.PBRule.PBRule.AsEnumerable() _
                                                                            Where s.PBRuleId = .PBRuleId And s.StartDate <= .PBDate _
                                                                            And (s.IsFinishDateNull Or (s.FinishDate > .PBDate)) Select s

                        If query.Count > 0 Then
                            dr2 = query.ElementAt(0)
                            If Not (dr2.IsHoursNull) Then
                                If dr2.IsBilling Then
                                    dblHoursBILL += CType(dr2.Hours, Double)
                                Else
                                    dblHoursPAY += CType(dr2.Hours, Double)
                                End If
                            End If
                        End If

Even though I have put s.IsFinishDateNull condition to avoid null exception but its still causing exception The value for column 'FinishDate' in table 'PBRule' is DBNull. this linq query is an alternate to this datatable query which works fine.

Dim dr As DataRow()
                        Dim sQuery As String
                        sQuery = "PBRuleId={0} AND StartDate<=#{1:MM/dd/yy}# AND (FinishDate>=#{1:MM/dd/yy}# OR FinishDate IS NULL)"
                        dr = Me.PBRule.PBRule.Select(String.Format(sQuery, .PBRuleId, .PBDate))

                        If dr.Length > 0 Then
                            If Not (dr(0).Item("Hours") Is DBNull.Value) Then
                                If dr(0).Item("IsBilling").ToString().ToLower = "true" Then
                                    dblHoursBILL += CType(dr(0).Item("Hours").ToString(), Double)
                                Else
                                    dblHoursPAY += CType(dr(0).Item("Hours").ToString(), Double)
                                End If
                            End If
                        End If

Can anyone help

score 0 · Accepted Answer · answered Sep 17 '14 at 13:04

0

Use OrElse instead of Or.

Or operator evaluates both sides of the expressions regardless of the result of the left hand side.

answered Sep 17 '14 at 13:04

Ripple

1,257
1
9
15

Thanks this worked. can u also help me to convert this sql to linq select * from tblPBRule where PBRuleId = 364 and StartDate < = '20140902' and finishdate is null and PBCodeid in (select PBCodeid from tblpbrule where PBHourstypeid IN (3,4,5,6)) or shall i ask another question on so – 3355307 Sep 17 '14 at 14:06
You're welcome. If you are struggling with `IN` clause, see [Linq version of SQL “IN” statement](http://stackoverflow.com/questions/896123/linq-version-of-sql-in-statement/896156#896156). Or better create a new question. – Ripple Sep 17 '14 at 14:15

Linq to strongly typed DataTable and Null issue for date column

1 Answers1