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 Functionany 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