Link to home
Start Free TrialLog in
Avatar of willhebert
willhebert

asked on

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



Avatar of appari
appari
Flag of India image

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



ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 & "'" & ""
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.
I think it's depends on data type, I use # many times in Access and it works : )
Avatar of willhebert
willhebert

ASKER

I just retyped all the fields data type and it worked. Thanks for all your help.