Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-05
7
Medium Priority
?
1,106 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

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 2000 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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