My Web App is coming along pretty well. But I've had trouble regarding an INNER JOIN.
I've checked the SQLite Statement in SQLiteStudio and it returns perfectly what I'm looking for with no errors, with both Patient & Doctor data, but it doesn't work in my Xojo Program. I've ensured that all column names are different (even the ID columns) as I heard that can produce issues.
Dim stmt As SQLitePreparedStatement = Session.db.Prepare("SELECT Patient.ID, Patient.Forename, Patient.Surname, Patient.DateOfBirth, Patient.Address, Patient.Allergies, Patient.Notes, Patient.Type, Doctor.DocID, Doctor.DoctorName, Doctor.DoctorAddress FROM Patient INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID WHERE Patient.ID=?") stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) stmt.Bind(0, patientID) Dim rs As RecordSet = stmt.SQLSelect Return rs
Dim rs As RecordSet rs = GetPatientNumber(patID) If rs <> Nil Then txtID.Text = patID txtPatientForeName.Text = rs.Field("Forename").StringValue txtPatientSurname.Text = rs.Field("Surname").StringValue txtDOB.Text = rs.Field("DateOfBirth").StringValue txtAddress.Text = rs.Field("Address").StringValue txtAllergies.Text = rs.Field("Allergies").StringValue txtDoctorName.Text = rs.Field("DoctorName").StringValue txtDoctorAddress.Text = rs.Field("DoctorAddress").StringValue End If
No errors are returned, but the Text Fields that I am trying to fill just end up completely blank.
The SQLite statement returns fine outside of Xojo.
If I remove the 'INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID' It successfully fills the text fields from the Patient table.
This leads me to believe that there is a problem with the way I've Implemented INNER JOIN here. What's confusing is that it works fine in SQLiteStudio, so is there some extra ruling regarding INNER JOIN use in Xojo?
I should also mention that when I add the 'INNER JOIN Doctor ON Patient.DoctorID = Doctor.DocID' part, none of the Patient text fields get filled like they were before.