Syntax Error in INSERT INTO Statement

[sudhanshu]

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

The code is...

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
 
 
Private Sub cmdOK_Click()
 
conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VB Progs\Cafe\Cafe.mdb;"
 
conn.Open
 
cmd.ActiveConnection = conn
conn.CursorLocation = adUseClient
 
cmd.CommandText = "Insert into Session (UserId) values ('" & txtUserName.Text & "')"
 
MsgBox cmd.CommandText
 
cmd.Execute
 
conn.Close
 
End Sub

When executed, it gives an error message saying...

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

Please help.

[Killer42]

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.

[sudhanshu]

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.

[Killer42]

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.

[sudhanshu]

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.

[NeoPa]

Hi sudhanshu.

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

Try formulating it as :

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.

[Killer42]

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.

[NeoPa]

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.

[sudhanshu]

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.

[NeoPa]

It may not have been reserved words. Often simply going through the code carefully fixes bugs you don't even notice.