L
Lucky Hyena
Guest
Hello, I am trying to conduct a multi-table SQL Query to scrape up top marks on a school project. Here is my code so far:
'Split dataToBeQueried into patient_id and appointment_date
Dim splitterArray() As String 'create a string that will be treated as an array of characters
splitterArray = dataToBeQueried.Split(" ") 'split the array on every space
Dim patientIdSearch As Integer = Int(splitterArray(0))
Dim otherVar As Integer = Int(splitterArray(1))
Dim con As New OleDbConnection
con.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileDirectory & fileName)
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
con.Open()
ds.Tables.Add(dt) 'all this code establishes a connection to the database that is being queried
da = New OleDbDataAdapter("SELECT Surname, Last_Appointment_Date FROM Patient WHERE Patient_ID =" & patientIdSearch &
" UNION SELECT Appointment_Time_And_Time, Treatment_Cost FROM Appointments WHERE Appointment_ID =" & otherVar, con) 'feed in SQL query
Try 'if it works populate Data Grid View
da.Fill(dt)
DataGridView4.DataSource = dt.DefaultView
Catch ex As Exception 'if exception is thrown
MsgBox(ex.Message)
End Try
con.Close() 'close the connection
The actual code doesn't throw any exceptions or anything. I am trying to create a Query so that I can type in a patientID and an appointmentID so that I can return a joint table that will show me the patient's surname and their last appointment from the table Patient and their next appointment and its cost from the table Appointments. I know that data types can be important in SQL so here are the data types for each column:
Surname: String
Last_Appointment_Date: Date/Time Stamp
Appointment_Time_And_Time: Date/Time Stamp
Treatment_Cost: Currency
However, when I run the code, with valid records in each table, it only return the Surname and the Lat_Appointment_Date columns. It doesn't display the other two. What am I doing wrong? Thanks
Continue reading...
'Split dataToBeQueried into patient_id and appointment_date
Dim splitterArray() As String 'create a string that will be treated as an array of characters
splitterArray = dataToBeQueried.Split(" ") 'split the array on every space
Dim patientIdSearch As Integer = Int(splitterArray(0))
Dim otherVar As Integer = Int(splitterArray(1))
Dim con As New OleDbConnection
con.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileDirectory & fileName)
Dim da As New OleDbDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable
con.Open()
ds.Tables.Add(dt) 'all this code establishes a connection to the database that is being queried
da = New OleDbDataAdapter("SELECT Surname, Last_Appointment_Date FROM Patient WHERE Patient_ID =" & patientIdSearch &
" UNION SELECT Appointment_Time_And_Time, Treatment_Cost FROM Appointments WHERE Appointment_ID =" & otherVar, con) 'feed in SQL query
Try 'if it works populate Data Grid View
da.Fill(dt)
DataGridView4.DataSource = dt.DefaultView
Catch ex As Exception 'if exception is thrown
MsgBox(ex.Message)
End Try
con.Close() 'close the connection
The actual code doesn't throw any exceptions or anything. I am trying to create a Query so that I can type in a patientID and an appointmentID so that I can return a joint table that will show me the patient's surname and their last appointment from the table Patient and their next appointment and its cost from the table Appointments. I know that data types can be important in SQL so here are the data types for each column:
Surname: String
Last_Appointment_Date: Date/Time Stamp
Appointment_Time_And_Time: Date/Time Stamp
Treatment_Cost: Currency
However, when I run the code, with valid records in each table, it only return the Surname and the Lat_Appointment_Date columns. It doesn't display the other two. What am I doing wrong? Thanks
Continue reading...