Hi all,
I've got a small problem with a database I'm working on, the project was given to me by college kids & their Teacher to complete. They created the database using Access 2007 and I'm left building a Windows App user interface in Visual Basic. Anyway I can't figure out why I keep getting this error [Syntax error in INSERT INTO statement] I've checked the spelling & names of everything, if anyone can see were I went wrong please let me know, Thanks very much.
Private Sub Btn_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Save.Click
Dim da As New OleDb.OleDbDataAdapter
Dim cb As New OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim sql As String
Dim inc As Integer = 0
dbProvider = "Provider = Microsoft.JET.OLEDB.4.0;"
dbSource = "Data Source = C:\Documents and Settings\user\Desktop\thornhill2011\malachy\WindowsApplication1\WindowsApplication1\Patient Database.mdb;Persist Security Info = True;"
con.ConnectionString = dbProvider & dbSource
con.Open()
sql = "SELECT * FROM Patient"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Patient Database")
Try
dsNewRow = ds.Tables("Patient Database").NewRow()
sql = "INSERT INTO [Patient] (Patient ID, Title, Forename, Surname, Date of Birth, Gender, Occupation, Home Address, Town, Post Code, Home Telephone, Mobile No, Next of Kin, Relationship, Emergency No, Allergies, Intolerance to Medicine, Regular Medication and Doses, GP, Known Medical Condition, Notes, Last Appointment Date) VALUES ('" & Patient_IDTextBox.Text & "', '" & TitleTextBox.Text & "', '" & ForenameTextBox.Text & "', '" & SurnameTextBox.Text & "', '" & Date_of_BirthDateTimePicker.Text & "', '" & GenderTextBox.Text & "', '" & OccupationTextBox.Text & "', '" & Home_AddressTextBox.Text & "', '" & TownTextBox.Text & "', '" & Post_CodeTextBox.Text & "', '" & Home_TelephoneTextBox.Text & "', '" & Mobile_NoTextBox.Text & "', '" & Next_of_KinTextBox.Text & "', '" & RelationshipTextBox.Text & "', '" & Emergency_NoTextBox.Text & "', '" & AllergiesTextBox.Text & "','" & Intolerance_to_MedicineTextBox.Text & "', '" & Regular_Medication_and_DosesTextBox.Text & "', '" & GPTextBox.Text & "', '" & Known_Medical_ConditionTextBox.Text & "', '" & NotesTextBox.Text & "', '" & Last_Appointment_DateDateTimePicker.Text & "')"
dsNewRow.Item("Patient ID") = Patient_IDTextBox.Text
dsNewRow.Item("Title") = TitleTextBox.Text
dsNewRow.Item("Forename") = ForenameTextBox.Text
dsNewRow.Item("Surname") = SurnameTextBox.Text
dsNewRow.Item("Date of Birth") = Date_of_BirthDateTimePicker.Text
dsNewRow.Item("Gender") = GenderTextBox.Text
dsNewRow.Item("Occupation") = OccupationTextBox.Text
dsNewRow.Item("Home Address") = Home_AddressTextBox.Text
dsNewRow.Item("Town") = TownTextBox.Text
dsNewRow.Item("Post Code") = Post_CodeTextBox.Text
dsNewRow.Item("Home Telephone") = Home_TelephoneTextBox.Text
dsNewRow.Item("Mobile No") = Mobile_NoTextBox.Text
dsNewRow.Item("Next of Kin") = Next_of_KinTextBox.Text
dsNewRow.Item("Relationship") = RelationshipTextBox.Text
dsNewRow.Item("Emergency No") = Emergency_NoTextBox.Text
dsNewRow.Item("Allergies") = Patient_IDTextBox.Text
dsNewRow.Item("Intolerance To Medicine") = Intolerance_to_MedicineTextBox.Text
dsNewRow.Item("Regular Medication and Doses") = Regular_Medication_and_DosesTextBox.Text
dsNewRow.Item("GP") = GPTextBox.Text
dsNewRow.Item("Known Medical Condition") = Known_Medical_ConditionTextBox.Text
dsNewRow.Item("Notes") = NotesTextBox.Text
dsNewRow.Item("Last Appointment Date") = Last_Appointment_DateDateTimePicker.Text
ds.Tables("Patient Database").Rows.Add(dsNewRow)
Dim cmd As New OleDbCommand
With cmd
.Connection = con
.CommandText = sql
cmd.ExecuteNonQuery()
End With
MsgBox("New Patient details have been added")
con.Close()
Catch ex As System.Exception
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try
End Sub