Join Statements Giving Me Fits ( vb.net 2010 )

[DarknessVB]

I have a form set up and on that form is a DataGridView, on that DataGridView I would like to display all of a customer's transactions.

I am having a problem where the debugger is throwing me a "Syntax error in JOIN operation." and I can't proceed.

' Select all of the transactions for the currently displayed patron
        strSQL = _
            "     SELECT ITEMS.TITLE, MEDIAS.TYPE, TRANSACTIONS.CHECKOUT, TRANSACTIONS.DUE, TRANSACTIONS.CHECKIN " & _
            "       FROM TRANSACTIONS, ITEMS, MEDIAS " & _
            " INNER JOIN ITEMS ON ITEMS.ID = TRANSACTIONS.ITEM " & _
            " INNER JOIN MEDIAS ON MEDIAS.ID = ITEMS.MEDIA " & _
            "   ORDER BY CHECKOUT "

        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(ds, "PatronTransactions")

I have tried revamping the sql code a dozen times and still can't make any more progress on it.

I tried thinning it down to a single join just to test:

SELECT ITEMS.TITLE, TRANSACTIONS.ID
      FROM TRANSACTIONS, ITEMS
INNER JOIN ITEMS ON ITEMS.ID = TRANSACTIONS.ITEM

But I still get the same issues.

I checked all the table names and field names and everything is correct, so I am not sure where the issue is coming in.

I've searched thru all the join stuff on the board, but I am still coming up empy handed.

It has to be something simple, or I wouldn't be having so much problem with it.

[DarknessVB]

I was still fiddling with it and I have made a 2 table join work with the following syntax:

SELECT ITEMS.TITLE, TRANSACTIONS.CHECKOUT 
      FROM TRANSACTIONS 
INNER JOIN ITEMS 
        ON TRANSACTIONS.ITEM = ITEMS.ID

However I still can not get a three table join to work using a similar set of code:

SELECT ITEMS.TITLE, TRANSACTIONS.CHECKOUT, MEDIAS.TYPE 
      FROM TRANSACTIONS 
INNER JOIN ITEMS 
        ON TRANSACTIONS.ITEM = ITEMS.ID
INNER JOIN MEDIAS 
        ON  ITEMS.MEDIA = MEDIAS.ID

I tested the following code just to make sure that I wasn't botching that up some way:

SELECT MEDIAS.TYPE, ITEMS.TITLE 
      FROM ITEMS 
INNER JOIN MEDIAS 
        ON ITEMS.MEDIA = MEDIAS.ID

and it worked as intended, so it isn't a syntax error in naming conventions or anything like that, apparently it is an issue with a 3 table join that is causing the fits.

So, what's the secret to 3 table joins in VB?

[DarknessVB]

Still Chugging along while waiting for insight in hopes that I can stumble on it myself.

I researched sql joins and I found some articles where instead of specifying the inner joins it's all just lumped together.

When I do it this way I get an error of "No value given for one or more required parameters." on my da.fill(...)

strSQL = _
            "    SELECT MEDIAS.TYPE, ITEMS.TITLE, TRANSACTIONS.DUEDATE " & _
            "      FROM TRANSACTIONS, ITEMS, MEDIAS " & _
            "     WHERE TRANSACTIONS.ITEM = ITEMS.ID " & _
            "       AND ITEMS.MEDIA = MEDIAS.ID "
            
        da = New OleDbDataAdapter(strSQL, cn)

        da.Fill(ds, "PatTrns") << Error Happens Here With This Type Of Join

[DarknessVB]

The last problem I posted was caused by my failure in typing the correct name to a field.

Final conclusion

Does Not Work:

SELECT ...
         FROM ...
   INNER JOIN ...
   INNER JOIN ...

If you are going to join multiple tables use the following format:

strSQL = _
            "    SELECT MEDIAS.TYPE, ITEMS.TITLE, TRANSACTIONS.DUEDATE " & _
            "      FROM TRANSACTIONS, ITEMS, MEDIAS " & _
            "     WHERE TRANSACTIONS.ITEM = ITEMS.ID " & _
            "       AND ITEMS.MEDIA = MEDIAS.ID "

Problem solved.