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!
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).