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],[JobNumb er],[DOB], [TapeNum], [RefProvid er],[Tech] ,[RecordNu m],[Proced ureDate],[ Comment],[ Job1],[Job 2],[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 & "'" & "")
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],[JobNumb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also typically for date field, use # instead of ' in Access.
you may try like:
SQLStr = "insert into StoneSetting([ID],[JobNumb er],[DOB], [TapeNum], [RefProvid er],[Tech] ,[RecordNu m],[Proced ureDate],[ Comment],[ Job1],[Job 2],[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 & "'" & ""
you may try like:
SQLStr = "insert into StoneSetting([ID],[JobNumb
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.
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 : )
ASKER
I just retyped all the fields data type and it worked. Thanks for all your help.
conn.Execute ("insert into StoneSetting([ID],[JobNumb