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,user_lastname,user_student_id,user_web_student_id,user_staff_id,user_faculty_id,user_email,user_type,edited_by,edited_date,is_active) VALUES ('ltest','letmein','first','last',0,9,0,0,'email@email.com',3,0,'4/5/2007 5:39:39 PM',Yes)"

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
lcftahoeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dreadyCommented:
I'm not sure what's wrong here. But my guess would be that either the value for edited_date is problematic (try it without time and pm to start with), and i'm also not sure, but did you try putting yes in quotes, so 'yes' instead of yes

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?

0
mbizupCommented:
Access uses # to delimit dates...

"INSERT INTO tblUser (username, password,user_firstname,user_lastname,user_student_id,user_web_student_id,user_staff_id,user_faculty_id,user_email,user_type,edited_by,edited_date,is_active) VALUES ('ltest','letmein','first','last',0,9,0,0,'email@email.com',3,0,#4/5/2007 5:39:39 PM#,Yes)"
0
mbizupCommented:
Access uses # to delimit dates...
Also try TRUE instead of Yes.

"INSERT INTO tblUser (username, password,user_firstname,user_lastname,user_student_id,user_web_student_id,user_staff_id,user_faculty_id,user_email,user_type,edited_by,edited_date,is_active) VALUES ('ltest','letmein','first','last',0,9,0,0,'email@email.com',3,0,#4/5/2007 5:39:39 PM#,TRUE)"
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

lcftahoeAuthor Commented:
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.
0
jefftwilleyCommented:
trim it down...so you're testing method, and not syntax. No reason to chase down two horses..

"INSERT INTO tblUser (username) VALUES ('ltest');"

should work ok since you removed the indexes.

if this still doesn't work, then it's the rdGet that's not working right.
J
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcftahoeAuthor Commented:
found the culprit stepping through each individual insert: "password" must be protected in Access. I changed the field name and it worked, thanks!
0
jefftwilleyCommented:
Excellent..happy to assist, as always
J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.