SQL - Syntax Error in FROM Clause (JOIN)

Question

Just beginning to experiment with the JOIN clause so I can display data from across multiple tables in a gridview. However, I can't for the life of me work out what my error is here. I've tried switching around the order of tables called, columns called and the such to no success. This, I think, is the closest to working I can get the statement. It's basically a straight copy of a supposedly working statement, yet still doesn't work.

Any help is appreciated. (I'm using VB.Net to code, Access '13 as the database (which might be part of the problem; I've read it's very finicky about syntax) and developing with VS 2013.)

@tim: as requested, here's the full sub.

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    If cbSearchType.Text = "Guest Surname" Then

        Dim searchSQL As String = "SELECT bookings.bookingStartDate, bookings.bookingEndDate, guests.guestFirstName, guests.guestSurname, locations.locationName FROM bookings JOIN guests ON guests.guestID = bookings.guestID JOIN locations ON locations.locationID = bookings.locationID WHERE guests.guestSurname = @guestSurname"

        Dim searchCommand = New OleDbCommand(searchSQL, globalVariables.objConnection)
        Dim searchAdapter As New OleDbDataAdapter(searchSQL, globalVariables.objConnection)
        Dim searchDataTable As New DataTable

        searchCommand.Parameters.AddWithValue("guestSurname", tbSearchTextBox.Text)
        searchAdapter.SelectCommand = searchCommand
        searchAdapter.Fill(searchDataTable)

        globalVariables.objConnection.Open()
        searchAdapter.Fill(searchDataTable)
        gvSearchResults.DataSource = searchDataTable
        globalVariables.objConnection.Close()

    ElseIf cbSearchType.Text = "Location" Then

    ElseIf cbSearchType.Text = "Booking Start Date" Then

    ElseIf cbSearchType.Text = "Booking End Date" Then

    End If
End Sub

End Class

Might be "...WHERE guests.guestSurnmae = '" + @guestSurname + "'" - i.e., if guestSurname` is a char or varchar field, you'll need to quote it with single quotes '. The other thing to check is that you have spaces between keywords (FROM, WHERE, etc). Can you post the actual VB.NET code you're using? — Tim
– Tim, Commented Nov 30, 2014 at 2:03
Don't work with Access much anymore, but you'll probably want to use "@guestSurname" instead of "guestSurname" in your Parameters.AddWithValue. Not 100% sure Access supports named parameters either. Try stepping through the debugger and seeing what the text of the OleDbCommand is after you add the parameter - that should show you if it's correct or not. — Tim
– Tim, Commented Nov 30, 2014 at 2:11
I was just reading that Access doesn't accept unqualified JOIN's, so I changed to INNER JOIN and received... — tvellalott
– tvellalott, Commented Nov 30, 2014 at 2:11
@tvellalott - Add the error to your original question. Comments don't support code and formatting very well. — Tim
– Tim, Commented Nov 30, 2014 at 2:11

Gordon Linoff · Accepted Answer · 2014-11-30 02:11:42Z

1

In Access, you need to specify INNER JOIN. You also need parentheses when there are multiple joins. I would write this as:

SELECT b.bookingStartDate, b.bookingEndDate, g.guestFirstName, g.guestSurname, l.locationName
FROM (bookings as b INNER JOIN
      guests as g
      ON g.guestID = b.guestID) INNER JOIN
     locations as l
     ON l.locationID = b.locationID
WHERE g.guestSurname = @guestSurname;

answered Nov 30, 2014 at 2:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tvellalott Over a year ago

This works perfectly, except that my gridview shows duplicate results for some reason.

tvellalott Over a year ago

Ugh, had searchAdapter.Fill(searchDataTable) twice.

Collectives™ on Stack Overflow

SQL - Syntax Error in FROM Clause (JOIN)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related