Solved

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

Posted on 2007-04-05
7
1,101 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
ID: 18862134
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
ID: 18862219
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
ID: 18862222
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:lcftahoe
ID: 18862401
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
ID: 18862457
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
ID: 18866218
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
ID: 18866239
Excellent..happy to assist, as always
J
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

930 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

11 Experts available now in Live!

Get 1:1 Help Now