system.data.oledb.oledbexception syntax error in from clause

[Galbatorix]

hello guys, i have an sql statement that is supposed to be returning data from two tables that i have joined. Here is the code below:

  sql = "SELECT rooms.roomID, rooms.roomNumber, rooms.roomStatus, " & _
              "roomType.roomType, roomType.adultRate, roomType.childrenRate, roomType.roomTypeID FROM rooms " & _
              "JOIN roomType on rooms.roomType = roomType.roomTypeID"

        Dim daRooms As New OleDb.OleDbDataAdapter(sql, con)
        Dim dsRooms As New DataSet
        Try
            connectDatabase()
            daRooms.Fill(dsRooms, "rooms")
            DisconnectDB()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Clipboard.SetText(ex.ToString)
        End Try
        With DataGridView1
            .DataSource = dsRooms
            .DataMember = "rooms"
            .BorderStyle = BorderStyle.None
            .AllowUserToResizeRows = False
            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .MultiSelect = False
            .DefaultCellStyle.BackColor = Color.AliceBlue
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Gray
        End With

Below is the error tha i am getting in th eexception:

System.Data.OleDb.OleDbException: Syntax error in FROM clause.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at Albergo.frmRooms.frmRooms_Load(Object sender, EventArgs e) in C:\Users\gman\Documents\Visual Studio 2008\Projects\Albergo\Albergo\frmRooms.vb:line 14

Please help out ...

[Reverend Jim]

Try

sql = "SELECT rooms.roomID, rooms.roomNumber, rooms.roomStatus,roomType.roomType, " _
    & "       roomType.adultRate, roomType.childrenRate, roomType.roomTypeID " _
    & "  FROM rooms INNER JOIN roomType " _
    & "    ON rooms.roomType = roomType.roomTypeID"

[Galbatorix]

Well, it now generates the following error

System.Data.OleDb.OleDbException: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at Albergo.frmRooms.frmRooms_Load(Object sender, EventArgs e) in C:\Users\gman\Documents\Visual Studio 2008\Projects\Albergo\Albergo\frmRooms.vb:line 14

[QVeen72]

Hi,

Your SQL statement contains "roomType.roomType"
Make sure, "roomType" Table, has "roomType" Column Name

Regards
Veena

[Galbatorix]

Thanks guys, it worked at last.