Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1885
  • Last Modified:

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 & "'" & "")



0
willhebert
Asked:
willhebert
  • 3
  • 2
1 Solution
 
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:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now