Member Avatar for 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 ...

  • 3 Contributors
  • 4 Replies
  • 795 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by Galbatorix
Member Avatar for 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"
Member Avatar for 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
Member Avatar for QVeen72

Hi,

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

Regards
Veena

Member Avatar for Galbatorix

Thanks guys, it worked at last.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.