ok so i'm trying to set up my program to insert a new record to my database i keep getting a insert syntax error

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click


        '************************************************
        '*                                                                                           **
        '*   will first verify that all necessary information needed      **
        '*                              to make a legitimate                             **
        '*      record has been entered and request user to verify    **
        '*               that all information entered is correct                  **
        '*                                                                                           **
        '* after all verifying all information is entered and correct     **
         '*                            will use a for loop                                  **
        '*      and the currencyManager to enter all information        **
        '*                             into Clients DB table                              **
        '*                                                                                           **
        '*  once all information has been entered into a database a** 
       '*                                 dialog box will open                            **
        '*        asking client if they would like to enter a new client  **
        '*                             if they answer yes all                             **
        '*fields will be cleared and if they say no this form will close**  
        '*                                                                                           **
        '*************************************************




        Dim FName As String = txtFName.Text.Trim
        Dim LName As String = txtLName.Text.Trim
        Dim MI As String = txtMI.Text.Trim
        Dim verify As Int16 = 0
        Dim counter As Int16 = 0





        'Verifying all information needed is entered
        If rbMale.Checked = False And rbFemale.Checked = False Then
            MessageBox.Show("Please enter clients gender", "Choose Gender", MessageBoxButtons.OK, MessageBoxIcon.Error)

        ElseIf DOBTextBox.Text.Length = 0 Or Not IsDate(DOBTextBox.Text) Then
            MessageBox.Show("Please specify D.O.B.", "Add D.O.B.", MessageBoxButtons.OK, MessageBoxIcon.Error)


        ElseIf FName.Length = 0 Or LName.Length = 0 Then
            MessageBox.Show("Please enter client's full name", "Client Name", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Else
            MsgBox(FName & " " & MI & " " & LName, MsgBoxStyle.Critical)

            client.Client.pName = FName & " " & MI & " " & LName
            verify = 2
        End If





        If verify = 2 Then
'GOES THROUGH EACH TEXT BOX AND ADDS TO ARRAY clientInfo()
            For counter = 0 To 31
                Select Case counter


                    Case 1
                        clientInfo(counter) = FName


                    Case 2
                        clientInfo(counter) = txtMI.Text.Trim


                    Case 3
                        clientInfo(counter) = LName


                    Case 4

'DATE/TIME FIELD
                        clientInfo(counter) = FormatDateTime(DOBTextBox.Text, DateFormat.ShortDate)


                    Case 5

                        If rbMale.Checked Then
                            clientInfo(counter) = "M"

                        Else
                            clientInfo(counter) = "F"
                        End If


                    Case 6
                        clientInfo(counter) = txtAddress1.Text.Trim


                    Case 7
                        clientInfo(counter) = txtAddress2.Text.Trim


                    Case 8
                        clientInfo(counter) = txtCity.Text.Trim


                    Case 9
                        clientInfo(counter) = txtState.Text.Trim


                    Case 10
                        clientInfo(counter) = txtZip.Text.Trim


                    Case 11
                        clientInfo(counter) = txtHomePhone.Text.Trim


                    Case 12
                        clientInfo(counter) = txtWorkPhone.Text.Trim

                    Case 13
                        clientInfo(counter) = " "

                    Case 14
                        clientInfo(counter) = txtSpouseFName.Text.Trim

                    Case 15
                        clientInfo(counter) = txtSpouseMI.Text.Trim

                    Case 16
                        clientInfo(counter) = txtSpouseLName.Text.Trim

                    Case 17
'DATE/TIME FIELD
                        If IsDate(SpouseDOB.Text) Then
                            clientInfo(counter) = FormatDateTime(SpouseDOB.Text, DateFormat.ShortDate)
                        Else
                            clientInfo(counter) = " "
                        End If
                    Case 18
                        If rbSpouseMale.Checked Then
                            clientInfo(counter) = "M"

                        ElseIf rbSpouseFemale.Checked Then
                            clientInfo(counter) = "F"
                        End If


                    Case 19
                        clientInfo(counter) = txtSpouseCell.Text.Trim


                    Case 20
                        clientInfo(counter) = txtSpouseWork.Text.Trim


                    Case 21
                        clientInfo(counter) = txtChild1FName.Text.Trim


                    Case 22
                        clientInfo(counter) = txtChild1LName.Text.Trim


                    Case 23
                        'DATE/TIME FIELD
                        If IsDate(Child1DOB.Text) Then
                            clientInfo(counter) = FormatDateTime(Child1DOB.Text, DateFormat.ShortDate)
                        Else
                            clientInfo(counter) = " "
                        End If

                    Case 24
                        If rbChild1Female.Checked Then
                            clientInfo(counter) = "F"

                        ElseIf rbChild1Male.Checked Then
                            clientInfo(counter) = "M"
                        End If


                    Case 25
                        clientInfo(counter) = txtChild2FName.Text.Trim


                    Case 26
                        clientInfo(counter) = txtChild2LName.Text.Trim


                    Case 27
                        'DATE/TIME FIELD
                        If IsDate(Child2DOB.Text) Then
                            clientInfo(counter) = FormatDateTime(Child2DOB.Text, DateFormat.ShortDate)
                        Else
                            clientInfo(counter) = " "
                        End If

                    Case 28
                        If rbChild2Female.Checked Then
                            clientInfo(counter) = "F"

                        ElseIf rbChild2Male.Checked Then
                            clientInfo(counter) = "M"
                        End If


                    Case 29
                        clientInfo(counter) = txtChild3FName.Text.Trim


                    Case 30
                        clientInfo(counter) = txtChild2LName.Text.Trim


                    Case 31
                        If IsDate(Child3_DOBTextBox.Text) Then
                            clientInfo(counter) = FormatDateTime(Child3_DOBTextBox.Text, DateFormat.ShortDate)
                        Else
                            clientInfo(counter) = " "
                        End If

                    Case 32
                        If rbChild3Female.Checked Then
                            clientInfo(counter) = "F"

                        ElseIf rbChild3Male.Checked Then
                            clientInfo(counter) = "M"
                        End If

                End Select

            Next

            'CALLS CLIENT'S SUB PROCEDURE AND PASSES ARRAY clientInfo
            client.add(clientInfo)

end if

--------------------------------------------------------------------------------------
   Public Sub add(ByVal info())


        '***********************************************
        '*                                                                                       **
        '*   THIS FUNCTION WILL ADD AN ARRAY (OF ANY SIZE)    **
        '*                   INTO A DESIRED DATABASE TABLE               **
        '*                                                                                       **
        '*     WILL USE DYNAMIC ARRAY value THAT WILL HAVE    **
        '*       ALL PERSON'S INFORMATION STORED WITHIN         **
        '*                                  IT.                                                  **
        '*                                                                                       **
        '*                                                                                       **
        ***********************************************

        'DATASET AND ADAPTER
        Dim dsC As New dsClient1
        Dim daClient As New OleDb.OleDbDataAdapter("SELECT * FROM Client;", connection)



        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Office.accdb;Jet OLEDB:Database Password=teocaltiche"


        Dim oleDbCnn As OleDb.OleDbConnection
        Dim oleDbCmd As OleDb.OleDbCommand


        oleDbCnn = New OleDb.OleDbConnection(connString)
        'USES FUNCTION insert() TO CREATE SQL COMMAND
        oleDbCmd = New OleDb.OleDbCommand(Insert(info), oleDbCnn)



        cTable = New dsClient1.ClientDataTable
        clientRow = cTable.NewRow

        daClient.Fill(dsC)


        oleDbCmd.Connection.Open()
        oleDbCmd.ExecuteNonQuery()
        oleDbCmd.Connection.Close()





        oleDbCmd.Connection.Open()
        oleDbCmd.ExecuteNonQuery()
        oleDbCmd.Connection.Close()





    End Sub

-------------------------------------------------------------------------------------

    Private Function Insert(ByVal info) As String

        Dim counter As Integer
        Dim cmd As String = "INSERT INTO Client ( [ID], [FirstName], " _
& "[MI], [LastName], [DOB], [Gender], [Address], [Address2], " _
& " [City], [State], [Zip], [HPhone], [WPhone], [PicLocation], " _
& "[SpouseFName], [SpouseMI], [SpouseLName], [SpouseDOB], " _
& "[SpouseGender], [SpouseCellPhone], [SpouseWorkPhone], " _
& "[Child1FirstName], [Child1LastName], [Child1DOB], " _
& "[Child1Gender], [Child2FirstName], [Child2LastName], " _
& "[Child2DOB], [Child2Gender], [Child3FirstName], " _ 
& "[Child3LastName], [Child3 DOB], [Child3Gender] ) VALUES ( , "



        If info.Length < 0 Then
            MsgBox("ERROR: EMPTY ARRAY", MsgBoxStyle.Critical)
            End
        End If



        'FOR STATEMENT TO FINISH INSERT SQL STATEMENT
            For counter = 1 To UBound(info)


                'ERROR HANDLING
                If String.IsNullOrEmpty(info(counter)) Or IsDBNull(info(counter)) Then

                    cmd += " , "

                Else
                    cmd += info(counter) & ", "
                End If

            Next
        End With


        cmd += ")"



        Return cmd
    End Function

any help would be great. i know it's alot of code to go through but i just can't figure out what or where the problem is

Edited 6 Years Ago by stuncrazy: n/a

This are the issues in ur code,

1. where did u specified the connection string value in this red colr var

Dim daClient As New OleDb.OleDbDataAdapter("SELECT * FROM Client;", connection)

2.I just modified ur code,

'DATASET AND ADAPTER        
            Dim dsC As New dsClient1        
            Dim daClient As New OleDb.OleDbDataAdapter("SELECT * FROM Client;", connection)
            daClient.Fill(dsC)   
            connection.Close()

            Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Office.accdb;Jet OLEDB:Database Password=teocaltiche"          
            Dim oleDbCnn As OleDb.OleDbConnection        
            Dim oleDbCmd As OleDb.OleDbCommand          
            oleDbCnn = New OleDb.OleDbConnection(connString)  
            oleDbCnn.open()

            'USES FUNCTION insert() TO CREATE SQL COMMAND        
            oleDbCmd = New OleDb.OleDbCommand(Insert(info), oleDbCnn)
            oleDbCmd.ExecuteNonQuery()     

            cTable = New dsClient1.ClientDataTable        
            clientRow = cTable.NewRow         
            oleDbCnn.Close()

Let me what issue u r facing exactly

This article has been dead for over six months. Start a new discussion instead.