How do I test for DBNull so as not to crash with "Column .... does not belong to table row"?

Question

I am reading 2 tables in T-SQL like so:

Select r.UID,c.Forename,c.Surname,c.DOB From c LEFT OUTER JOIN r on........

Then in VB.NET I loop through the dataset like so:

For Each drR In dsR.Tables(0).Rows......Next

However when I test like so:

If Convert.IsDBNull(drR("r.UID")) Then

Or

String.IsNullOrEmpty(drR("r.UID"))
 Convert.IsDBNull(r.UID))

I crash with

Column 'UID' does not belong to table row

when the second table r has no record.

I did try both r.UID and UID.

To recoup: All is fine when I have a record in the second table, but what must I do when I do not? How do I test for DBNull so as not to crash with "Column .... does not belong to table row"?

P.S. Regardng the 2 answers below: I have to test for UID so as to know whether there is a record in the 2nd table or not.

Answer 1

change your query to: Select isnull(r.UID,''),c.Forename,c.Surname,c.DOB From c LEFT OUTER JOIN r on

Answer 2

You could use the AS alias to specify the column name if needed combined with lvo's suggestion

SELECT ISNULL(r.UID,'') AS 'UID' ...etc...

In that way the column has a name and therefore would eliminate the test for checking the column name UID if it is present on another table.

Hope this helps, Best regards, Tom.

Answer 3

Put a break after the dataset is filled. Then in your watch window add the following to see what the column name is (assuming r.UID is the first column then columns(0) ) - i.e. columns(columnnumber - 1)

dsR.Tables(0).columns(0).ColumnName

Answer 4

You need to do a few thing to get your code flying.

First consider side stepping your problem by using an INNER JOIN instead of the LEFT OUTER JOIN.

OR Split the problem and work with two sets.

A set with UIDs:

select r.Uid, c.Forename, c.Surname, c.DOB
  from c
 inner join r 
    on c.forename=r.forename and c.surname=r.surname

A set without UIDs:

select c.Forename, c.Surname, c.DOB
  from c
  left outer join r 
    on c.forename=r.forename and c.surname=r.surname
 where r.Uid is null

But if you need to do it in one go with all the rows from the LEFT Table then you will need to check for DBNull and here is how to do that:

If Not IsDBNull(drR("UID")) Then
    'Success
    Debug.Print(drR("UID"))
Else
    'Failure    
End If

This is another way to do the same check:

If Not TypeOf drR("UID") Is DBNull Then
    'Success
    Debug.Print(drR("UID"))
Else
    'Failure    
End If

also if you are in a long tight loop you may gain performance by indexing the column:

    Dim dt as DataTable = DAL.GetYourDataTable()
    Dim ixUID As Integer = dt.Columns.IndexOf("UID")
    For Each dr As DataRow In dt.Rows
        If Not IsDBNull(dr(ixUID)) Then
            'Success
            Debug.Print(dr(ixUID))
        Else
            'Failure
        End If
    Next