lcftahoe
asked on
syntax error on INSERT statement in VB.net to MS Access database
I'm banging my head against the wall on this, getting a syntax error on this insert statement from VB.net:
"INSERT INTO tblUser (username, password,user_firstname,us
MS Access fields (I removed all indexes):
user_id -- autonumber
username -- text
password -- text
user_firstname -- text
user_lastname -- text
user_student_id -- number
user_web_student_id -- number
user_staff_id -- number
user_faculty_id -- number
user_email -- text
user_type -- number
edited_by -- number
edited_date -- date/time
is_active -- Yes/No
"INSERT INTO tblUser (username, password,user_firstname,us
MS Access fields (I removed all indexes):
user_id -- autonumber
username -- text
password -- text
user_firstname -- text
user_lastname -- text
user_student_id -- number
user_web_student_id -- number
user_staff_id -- number
user_faculty_id -- number
user_email -- text
user_type -- number
edited_by -- number
edited_date -- date/time
is_active -- Yes/No
Access uses # to delimit dates...
"INSERT INTO tblUser (username, password,user_firstname,us
"INSERT INTO tblUser (username, password,user_firstname,us
Access uses # to delimit dates...
Also try TRUE instead of Yes.
"INSERT INTO tblUser (username, password,user_firstname,us
Also try TRUE instead of Yes.
"INSERT INTO tblUser (username, password,user_firstname,us
ASKER
From above recommendations I tried:
- getting rid of the timestamp
- putting # instead of ' around the date
(regarding above two am successfully inserting dates elsewhere on the page, with and without timestamp)
- 'Yes' and True instead of Yes (elsewhere on the page, insertion of Yes sans quotes is the only thing that works)
The code calling this is failing on the ExecuteReader with the Syntax error, this code is within a function called from all Get statements on the page, the rest of which are not failing:
cmdGet=new OleDbCommand
cmdGet.Connection=dbConn
cmdGet.CommandText=strGet
rdGet=cmdGet.ExecuteReader
So... still stumped.
- getting rid of the timestamp
- putting # instead of ' around the date
(regarding above two am successfully inserting dates elsewhere on the page, with and without timestamp)
- 'Yes' and True instead of Yes (elsewhere on the page, insertion of Yes sans quotes is the only thing that works)
The code calling this is failing on the ExecuteReader with the Syntax error, this code is within a function called from all Get statements on the page, the rest of which are not failing:
cmdGet=new OleDbCommand
cmdGet.Connection=dbConn
cmdGet.CommandText=strGet
rdGet=cmdGet.ExecuteReader
So... still stumped.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
found the culprit stepping through each individual insert: "password" must be protected in Access. I changed the field name and it worked, thanks!
Excellent..happy to assist, as always
J
J
How do you call the insert statement? I asume this is a sql string that you execute with executeCommand or something like that? Maybe you should post the whole function?