Question How to solve syntex Error In INSERT Statement in a 3-tier Application us Ms Access

[KingSoh]

I am working on a data entry application using vb2005 with Ms access as the database and the application is 3-tier application. When I add data from three to four textboxes, they are added to to the database, However when I increase the data and added comboboxes, I get syntex error in Insert Statement with reference to my DAL. Below is the code I Used. Could someone figure out for me where am going wrong. Below is my code:
Public Shared Function GetConnection() As OleDbConnection
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source= C:\MyPatientsData.mdb"
Return New OleDbConnection(connectionString)
End Function
Dim InsertCmd As New OleDbCommand
Public Sub Insert_Patient(ByVal FirstName As String, ByVal LastName As String, ByVal DOB As Date, ByVal Age As String, ByVal Address As String, ByVal village As String, _
ByVal PostalCode As String, ByVal Phone As String, ByVal Cell As String, ByVal MaritalStatus As String, ByVal Dependants As String, _
ByVal Gender As String, ByVal Employment As String, ByVal AppointmentDate As Date, ByVal Position As String, ByVal Supervisor As String)
Dim conn As OleDb.OleDbConnection = GetConnection()
Dim InsertStatement As String = "INSERT INTO Patient( FirstName, LastName, DOB, Age, Address, Village, PostalCode, Phone, Cell, MaritalStatus, Dependants, Gender, Employment, " & _
" AppointmentDate, Position, Supervisor) " & _
"VALUES( @FirstName, @LastName, @DOB, @Age, @Address, @Village, @PostalCode, @Phone, @Cell, @MaritalStatus, @Gender, @Dependants, @Employment, @AppointmentDate, @Position, @Supervisor )"
Dim selectSQL As String = "SELECT FirstName, LastName, DOB, Age, Address, Village, PostalCode, Phone, Cell, Maritalstatus, Dependants, Gender, Employment, Appointmentdate, Position, Supervisor FROM Patients"
'Dim SelectCommand As New OleDbCommand(selectSQL, conn)
Dim InsertCmd1 As New OleDbCommand(InsertStatement, conn)
InsertCmd1.Parameters.Add("@FirstName", OleDbType.VarChar, 25, "FirstName")
InsertCmd1.Parameters(0).Value = FirstName
InsertCmd1.Parameters.Add("@LastName", OleDbType.VarChar, 25, "LastName")
InsertCmd1.Parameters(1).Value = LastName
InsertCmd1.Parameters.Add("@DOB", OleDbType.Date, 8, "DOB")
InsertCmd1.Parameters(2).Value = DOB
InsertCmd1.Parameters.Add("@Age", OleDbType.VarChar, 5, "Age")
InsertCmd1.Parameters(3).Value = Age
InsertCmd1.Parameters.Add("@Address", OleDbType.VarChar, 50, "Address")
InsertCmd1.Parameters(4).Value = Address
InsertCmd1.Parameters.Add("@Village", OleDbType.VarChar, 50, "Village")
InsertCmd1.Parameters(5).Value = village
InsertCmd1.Parameters.Add("@PostalCode", OleDbType.VarChar, 12, "PostalCode")
InsertCmd1.Parameters(6).Value = PostalCode
InsertCmd1.Parameters.Add("@Phone", OleDbType.VarChar, 10, "Phone")
InsertCmd1.Parameters(7).Value = Phone
InsertCmd1.Parameters.Add("@Cell", OleDbType.VarChar, 10, "Cell")
InsertCmd1.Parameters(8).Value = Cell
InsertCmd1.Parameters.Add("@MaritalStatus", OleDbType.VarChar, 15, "MaritalStatus")
InsertCmd1.Parameters(9).Value = MaritalStatus
InsertCmd1.Parameters.Add("@Dependants", OleDbType.VarChar, 25, "Dependants")
InsertCmd1.Parameters(10).Value = Dependants
InsertCmd1.Parameters.Add("@Gender", OleDbType.VarChar, 15, "Gender")
InsertCmd1.Parameters(11).Value = Gender
InsertCmd1.Parameters.Add("@Employment", OleDbType.VarChar, 25, "Employment")
InsertCmd1.Parameters(12).Value = Employment
InsertCmd1.Parameters.Add("@Appointmentdate", OleDbType.DBDate, 8, "AppointmentDate")
InsertCmd1.Parameters(13).Value = AppointmentDate
InsertCmd1.Parameters.Add("@Position", OleDbType.VarChar, 25, "Position")
InsertCmd1.Parameters(14).Value = (Position)
InsertCmd1.Parameters.Add("@Supervisor", OleDbType.VarChar, 25, "Supervisor")
InsertCmd1.Parameters(15).Value = Supervisor
Try
conn.Open()
InsertCmd1.ExecuteNonQuery()
Dim StrStatement As String _
= "SELECT IDENT_CURRENT('Patient')FROM Patient"
Dim Selectcommand As New OleDbCommand(StrStatement, conn)
Dim PatientID As Integer = CInt(Selectcommand.ExecuteScalar)
Return
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.Close()
End Try
End Sub

 

[jmcilhinney]

When you get a syntax error in what appears to be valid SQL, the culprit is often a table or column name that is a reserved word. You should be able to find out which one fairly easily as MSDN would have a list of all Jet SQL reserved words. That said, you'll just then need to wrap that identifier in brackets. You could simply wrap them all in brackets and then it doesn't matter which one is a reserved word.

By the way, you can make your code much more succinct by replacing this pattern:

InsertCmd1.Parameters.AddWithValue("@FirstName", FirstName)

You should also not be using expressions like InsertCmd1.Parameters over and over:

With InsertCmd1.Parameters
    .AddWithValue("@FirstName", FirstName)
    '...
End With