By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

Syntax Error in INSERT INTO Statement

P: 17
Hi friends,
I am facing this error in my vb 6.0 code.

The code is...

Expand|Select|Wrap|Line Numbers
  1. Dim conn As New ADODB.Connection
  2. Dim cmd As New ADODB.Command
  3.  
  4.  
  5. Private Sub cmdOK_Click()
  6.  
  7. conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB Progs\Cafe\Cafe.mdb;"
  8.  
  9. conn.Open
  10.  
  11. cmd.ActiveConnection = conn
  12. conn.CursorLocation = adUseClient
  13.  
  14. cmd.CommandText = "Insert into Session (UserId) values ('" & txtUserName.Text & "')"
  15.  
  16. MsgBox cmd.CommandText
  17.  
  18. cmd.Execute
  19.  
  20. conn.Close
  21.  
  22. End Sub
When executed, it gives an error message saying...

Run-time error '-2147217900(80040e14)':
Syntax Error in INSERT INTO Statement

Please help.
Sep 23 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 5K+
P: 8,434
Without knowing more about your table and the contents of the textbox it's hard to say for sure. But perhaps your name field had an apostrophe in it? You know, like "O'Reilly" or something. Since the apostrophe character is used to delimit the field, this might confuse the database engine.
Sep 23 '07 #2

P: 17
Without knowing more about your table and the contents of the textbox it's hard to say for sure. But perhaps your name field had an apostrophe in it? You know, like "O'Reilly" or something. Since the apostrophe character is used to delimit the field, this might confuse the database engine.
Hi
There is no such problem with my textbox "txtName".
When I display the command text in a msgbox, it shows

Insert into Session(UserId) values('test')

And about the table,
Table name is Session with fields,

SessionID Autonumber
UserID Text 50
UserName Text 50

and so on.
I don't think other fields are relevent here.
I don't know what's wrong.
Sep 23 '07 #3

Expert 5K+
P: 8,434
I'm going to refer this to the Access forum, as my SQL is somewhat "rusty". (Just for future reference, this originated in the VB forum.)

The only thing I can think of is that perhaps some other field (such as UserName) is mandatory and the "syntax error" actually means that you left out a required field. I'm sure the SQL experts in the Access forum will have a better idea of what's going on.
Sep 23 '07 #4

P: 17
Thank you very muchh for your concern.
By the way i have only a SessionID field that is Autonumber
that happens to be required (& Primery Key). All other fields have
no constraints.
Anywayz thank you once again.
hope to get some help from access community as well.
Sep 24 '07 #5

NeoPa
Expert Mod 15k+
P: 31,492
Hi sudhanshu.

Did you get :
Insert into Session(UserId) values('test')
or
Insert into Session (UserId) values ('test')?

Try formulating it as :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Session] ([UserId]) VALUES ('test')
and see what it says.
Please post your results precisely in the post as misquotes can waste a lot of time.
Good luck.
Sep 25 '07 #6

Expert 5K+
P: 8,434
Did you get :
Insert into Session(UserId) values('test')
or
Insert into Session (UserId) values ('test')?
So the space is significant, then? I wondered about it, but I'm not that familiar with the INSERT syntax yet.
Sep 25 '07 #7

NeoPa
Expert Mod 15k+
P: 31,492
I'd be surprised if it were, but the results as posted were not as expected and I want to make sure we're not dealing with misinterpretations etc before continuing. So much time is wasted chasing leads that turn out simply to be slapdash posts by the OP. The square brackets ([ & ]) ensure we're not falling over any reserved words.
Sep 25 '07 #8

P: 17
Hi NeoPa & Killer42
thank you very much both of you. It worked.
Perhaps I have field names which are keywords.
I had no idea abt this.
I dont know how I've been using such fieldnames
before without having a problem.
Anyways thank you once again for your concern.
Sep 26 '07 #9

NeoPa
Expert Mod 15k+
P: 31,492
It may not have been reserved words. Often simply going through the code carefully fixes bugs you don't even notice.
Sep 26 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.