OleDbException: Syntax error in ORDER BY clause

Question

I got this error upon testing on how to get the latest database entry by selecting the required columns then specifying the unique id which is the room number ordered by the primary id, ID.

This is my code:

  Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\patientinfo.accdb"
    Conn.Open()

  Dim statement As String = " SELECT room_number, patient_name,patient_age,date_confinement,type_sickness, type_fluid, bottle_used, drop_rate FROM tblPatientInfo WHERE room_number=1 ORDER BY ID DESC LIMIT 1"
  RetrieveInfos(statement)

  End Sub

  Public Sub RetrieveInfos(ByRef statement As String)
    Dim cmd As OleDbCommand = New OleDbCommand
    With cmd
        .CommandText = statement
        .CommandType = CommandType.Text
        .Connection = Conn
        .ExecuteNonQuery()


        '--read records in access database----------------
        Dim reader As OleDbDataReader = cmd.ExecuteReader
        While reader.Read
            lblName.Text = (reader.GetString(0))
            lblAge.Text = (reader.GetInt32(1).ToString)
            lblDate.Text = (reader.GetDateTime(2).ToString)
            lblSickness.Text = (reader.GetString(3))
            lblFluid.Text = (reader.GetString(4))
            lblBottle.Text = (reader.GetInt32(5).ToString)
            lbldrops.Text = (reader.GetInt32(6).ToString)

        End While
      reader.Close()

    End With
End Sub

Any help would be appreciated. thanks!

What's the connection string (provider)? – Joachim Isaksson Sep 12 '12 at 17:25 — Joachim Isaksson, Sep 12 '12 at 17:25

score 3 · Accepted Answer · answered Sep 12 '12 at 18:02

The "LIMIT 1" clause is suspect. Suggest the query be modified to use "SELECT TOP 1 ....."

Dim statement As String = " SELECT TOP 1 room_number, patient_name,patient_age,
                            date_confinement,type_sickness, type_fluid, bottle_used, drop_rate FROM 
                            tblPatientInfo WHERE room_number=1 ORDER BY ID DESC"

(query wrapped with hard returns here for clarity).

score 2 · Answer 2 · answered Sep 12 '12 at 18:14

2

limit is specific to MySql.

For Access or SQL Server:

Select top 1 from ...
Order by ...

For Oracle:

Select *
from
(
    Select ...
    Order by ...
)
Where row_num = 1

answered Sep 12 '12 at 18:14

Paul Michaels

16,185
43
146
269

OleDbException: Syntax error in ORDER BY clause

2 Answers2