Run-time error 2147217900(80040e14) Syntax error in INSERT INTO Statement

Run-time erro '-2147217900(80040e14)
Syntax error in INSERT INTO Statement

What's this problem? I tried to search but no answer. Please help.

Here's my code: This code tries to insert new record to table in Microsoft Access 2000 from VB6 buy it keeps getting error above.

conn.Execute ("insert into StoneSetting([ID],[JobNumber],[DOB],[TapeNum],[RefProvider],[Tech],[RecordNum],[ProcedureDate],[Comment],[Job1],[Job2],[HDP]) values(" & Val(txtID.Text) & "," & "'" & txtJobNumber.Text & "'" & "," & "" & txtDOB.Text & "" & "," & "'" & txtTapeNum.Text & "'" & "," & "'" & txtRefProvider.Text & "'" & "," & "'" & txtTech.Text & "'" & "," & "'" & txtRecordNum.Text & "'" & "," & "" & txtProcedureDate.Text & "" & "," & Replace(txtComment.Text, "'", "''") & "," & "'" & chkJob1.Value & "'" & "," & "'" & chkJob2.Value & "'" & "," & "'" & chkHDP.Value & "'" & "")



willhebertAsked:
Who is Participating?
 
Ryan ChongCommented:
try check the fields data type with the values and make sure they are matched?

also, try copy out the sql statement generated and paste it into query builder and run it for debugging?

like:

...
SQLStr = "insert into StoneSetting([ID],[JobNumber],[DOB],[TapeNum],[RefProvider],[Tech],[RecordNum],[ProcedureDate],[Comment],[Job1],[Job2],[HDP]) values(" & Val(txtID.Text) & "," & "'" & txtJobNumber.Text & "'" & "," & "" & txtDOB.Text & "" & "," & "'" & txtTapeNum.Text & "'" & "," & "'" & txtRefProvider.Text & "'" & "," & "'" & txtTech.Text & "'" & "," & "'" & txtRecordNum.Text & "'" & "," & "" & txtProcedureDate.Text & "" & "," & Replace(txtComment.Text, "'", "''") & "," & "'" & chkJob1.Value & "'" & "," & "'" & chkJob2.Value & "'" & "," & "'" & chkHDP.Value & "'" & ""

debug.print SQLstr

....
0
 
appariCommented:
try replacing the characters you used to enclose the date values with ' ( single quote), as follows

conn.Execute ("insert into StoneSetting([ID],[JobNumber],[DOB],[TapeNum],[RefProvider],[Tech],[RecordNum],[ProcedureDate],[Comment],[Job1],[Job2],[HDP]) values(" & Val(txtID.Text) & "," & "'" & txtJobNumber.Text & "'" & "," & "'" & txtDOB.Text & "'" & "," & "'" & txtTapeNum.Text & "'" & "," & "'" & txtRefProvider.Text & "'" & "," & "'" & txtTech.Text & "'" & "," & "'" & txtRecordNum.Text & "'" & "," & "'" & txtProcedureDate.Text & "'" & "," & Replace(txtComment.Text, "'", "''") & "," & "'" & chkJob1.Value & "'" & "," & "'" & chkJob2.Value & "'" & "," & "'" & chkHDP.Value & "'" & "")



0
 
Ryan ChongCommented:
also typically for date field, use # instead of ' in Access.

you may try like:

SQLStr = "insert into StoneSetting([ID],[JobNumber],[DOB],[TapeNum],[RefProvider],[Tech],[RecordNum],[ProcedureDate],[Comment],[Job1],[Job2],[HDP]) values(" & Val(txtID.Text) & "," & "'" & txtJobNumber.Text & "'" & "," & "#" & txtDOB.Text & "#" & "," & "'" & txtTapeNum.Text & "'" & "," & "'" & txtRefProvider.Text & "'" & "," & "'" & txtTech.Text & "'" & "," & "'" & txtRecordNum.Text & "'" & "," & "" & txtProcedureDate.Text & "" & "," & Replace(txtComment.Text, "'", "''") & "," & "'" & chkJob1.Value & "'" & "," & "'" & chkJob2.Value & "'" & "," & "'" & chkHDP.Value & "'" & ""
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
appariCommented:
ryancys:
but i think if you are executing ado to execute the sql the date separator is ' not #. if using DAO have to use #. excuse me if i am wrong.
0
 
Ryan ChongCommented:
I think it's depends on data type, I use # many times in Access and it works : )
0
 
willhebertAuthor Commented:
I just retyped all the fields data type and it worked. Thanks for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.