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

Oh! Fixed it myself, in the SQL statement I've ommited the spaces between words that make up the names of the fields and it seems to work fine now!

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.ACE.OLEDB.12.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, 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, Known_Medical_Condition, Notes) VALUES ('" & Patient_IDTextBox.Text & "', '" & TitleTextBox.Text & "', '" & ForenameTextBox.Text & "', '" & SurnameTextBox.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 & "', '" & Known_Medical_ConditionTextBox.Text & "', '" & NotesTextBox.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("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("Known_Medical_Condition") = Known_Medical_ConditionTextBox.Text
            dsNewRow.Item("Notes") = NotesTextBox.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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.