How do I access all data from a SQL query where left join is used?

Question

I'm creating a method that populates some textboxes with data retrieved from an Access database using table adapters.

The GetCasesWithUserFromCaseId method return a single case with the username which is found by left joining my cases table with my users table.

For some reason I get a NullReferenceException when trying to access the joined data (userdata) from the users table but data from the cases table works. I'm sure that all fields is set for all users and all cases-tablerows.

If this doesn't work how do I then alternately get my data? I've attached an image showing my simple database.

The sql-statement:

SELECT *
FROM cases
LEFT JOIN users ON cases.caseCreatedBy = users.userId
WHERE caseId = caseNum

The C# code:

public void populateBoxes(int caseId)
{
   caseDBTableAdapters.casesTableAdapter casesAdapter = 
        new caseDBTableAdapters.casesTableAdapter();
   caseDB.casesDataTable cases;
   cases = casesAdapter.GetCasesWithUserFromCaseId(caseId);

   foreach (caseDB.casesRow casesRow in cases)
   {
      tbCaseName.Text = casesRow.caseName;
      tbOwner.Text = casesRow.usersRow.firstName.ToString();
   }
}

Answer 1

Well, the point is: if you do a LEFT OUTER JOIN on your Users table, then this statement here is dangerous:

foreach (caseDB.casesRow casesRow in cases)
{
    tbCaseName.Text = casesRow.caseName;
    tbOwner.Text = casesRow.usersRow.firstName.ToString();  <== !!!!!
}

With a LEFT OUTER JOIN, there's a chance that there are no users for your case - so casesRow.usersRow would be null.

You need to check for that!

foreach (caseDB.casesRow casesRow in cases)
{
    tbCaseName.Text = casesRow.caseName;

    if(casesRow.usersRow != null)
    {
        tbOwner.Text = casesRow.usersRow.firstName.ToString();
    }
}

Answer 2

You mentioned that the GetCasesWithUserFromCaseId() method is joining the cases and users tables with a left join. Is the exemption you are getting a NullReferenceExemption?

I would guess that the output of your access method is resulting in

caseName    firstName
----------  ----------
Bildsoe     NULL

if that is the case, then in your code line:

tbOwner.Text = casesRow.usersRow.firstName.ToString();

the usersRow property of the casesRow is likely to be null. You will have to check if the property is null before you try to reference it.