Solved

syntax error on INSERT statement in VB.net to MS Access database

Posted on 2007-04-05
7
1,100 Views
Last Modified: 2012-06-27
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
Comment
Question by:lcftahoe
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Expert Comment

by:dready
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:lcftahoe
Comment Utility
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
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
 

Author Comment

by:lcftahoe
Comment Utility
found the culprit stepping through each individual insert: "password" must be protected in Access. I changed the field name and it worked, thanks!
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Excellent..happy to assist, as always
J
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now