asked on

TypedDataSet Linq query fails with dbnull error

Hi I am having problems figuring out the correct LINQ syntax.

I have the following dataset, where DtHistory.NotesId can be null if there are no notes associatd with it.

I am using the following code to query the dataset;

Dim HistoryQuery = From o In ds.DtHistory
                           Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
                           Join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
                           Order By o.DateAccessed Descending
                           Select New With { _
                                  o.DtHistory_Id, _
                                  ol.PayrollRef, _
                                  ol.Name, _
                                  o.DateAccessed,
                                  nt.Note _
                                    }

For Each res In HistoryQuery

            Dim lvi As ListViewItem = ListView1.Items.Add(res.PayrollRef)
            lvi.SubItems.Add(res.Name)
            lvi.SubItems.Add(res.DateAccessed)
            
Next

Open in new window

But it throws the exception; "The value for column 'NotesId' in table 'DtHistory' is DBNull.".

I have read various suggestions like using ".DefaultIfEmpty()" and the generated property ".IsNoteNull" but I cannot get them to work in my Linq query.

All i want to do is retrieve all items from the history tab with the associated values from the other two tables (if not dbnull)

Anyone able to help?

AndyAinscow

Does this work:
Dim HistoryQuery = From o In ds.DtHistory
Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
LEFT join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
Order By o.DateAccessed Descending
Select New With { _
o.DtHistory_Id, _
ol.PayrollRef, _
ol.Name, _
o.DateAccessed,
nt.Note _
}

ITPOL

ASKER

Just tried it, but no.

"Left" doesn't seem to be a linq keyword

AndyAinscow

Ah well, worth a try. It is how one handles that relationship in SQL.

Fernando Soto

Hi ITPOL;

Seeming that the value of Note can be null/Nothing test it for Nothing and assign it a value if it is Nothing otherwise use the value. Try the code below.

Dim HistoryQuery = From o In ds.DtHistory
                   Join ol In ds.DtCompany On o.CompId Equals ol.DtCompany_Id
                   Join nt In ds.DtNotes On o.NotesId Equals nt.DtNotes_Id
                   Order By o.DateAccessed Descending
                   Select New With { _
                          o.DtHistory_Id, _
                          ol.PayrollRef, _
                          ol.Name, _
                          o.DateAccessed,
                          .Note = If(nt.Note Is Nothing, " ", nt.Note) _
                   }

Open in new window

ITPOL

ASKER

That was one of the things I tried actually. Unfortunately it still throws the exception.

From what I've read its to do with the way typed datasets are generated.

ITPOL

ASKER

Same problem here; but I cant see how to replicate this into this version of linq

http://stackoverflow.com/questions/881225/linq-to-dataset-dbnull-problem-null-reference-exception

ITPOL

ASKER

and here

Fernando Soto

Try changing this line

.Note = If(nt.Note Is Nothing, " ", nt.Note) _

to this

.Note = If(nt.Note Is Nothing, 0, nt.Note) _

Fernando Soto

What I think may be happening is that the object itself is null so try this if the other did not work.

.Net = If(nt Is Nothing, " ", nt.Note) _

ITPOL

ASKER

Thanks Fernando,

What I think may be happening is that the object itself is null so try this if the other did not work.

.Net = If(nt Is Nothing, " ", nt.Note) _

Afraid this didn't work either. I also tried replacing the blank with 0.

ITPOL

ASKER

Its double-bank holiday here in the UK, so I wont look at this again until tuesday. Thanks for your help so far.

Ark

Where o.NotesId.HasValue = True

ITPOL

ASKER

Where o.NotesId.HasValue = True

Won't this filter my results to only rows that have a value? in any case there was no "hasvalue" property on NotesId

AndyAinscow

In your code snippet you never actually use the nt.Note - so do you need the DtNotes table in the query? If not then leave that out and see if that makes a difference.

ITPOL

ASKER

haha, Well spotted but yes, I do.

I want to show it on the form if there is a note associated with the record.

AndyAinscow

OK, thought that might be the case.

At which line of code do you actually get the exception with the DBNull and when?

ITPOL

ASKER

Here;