Improve company productivity with a Business Account.Sign Up

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

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
0
lcftahoe
Asked:
lcftahoe
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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