Exception when try to INSERT into Access-DB

[Jazper]

Hi i got a big problem and don't know how to solve it...

i have an access DB and try to insert a new record!
Fild1: ID - Numeric - PrimaryKey
Fild2: User - Text

//---My ConnectionStrin g----------------------------------
Application["OleConStr"]
= "Provider=Micro soft.Jet.OLEDB. 4.0;Mode=Share Deny None;"
+ @"Data Source=C:\db\ja z.mdb";

//---My Code for Select----------------------------------
//---(works great)---------------------------------------

OleDbCommand cmdS = new OleDbCommand("S elect *
from jaz_test", this.con);
OleDbDataReader rd = cmdS.ExecuteRea der();
while( rd.Read() )
Response.Write( rd["ID"].ToString() + "-" +
rd["User"].ToString());
rd.Close();
cmdS.Dispose();

//RESULT = 1-Webmaster
//RESULT = 2-Webmaster II

//---My Code for Insert (Exception raises)--------------
OleDbCommand cmdI = new OleDbCommand("I NSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);
cmdI.ExecuteNon Query();
cmdI.Dispose();
//---My Problem!----------------------------------
cmdI.ExecuteNon Query() throws Exception "Syntax error in
INSERT INTO statement."

WHERE is the Error?. SQL-Insert statement must be OK! when
i execute it in Access itself, it works great! When i
execute it by VB6 it works great! When i execute it in C++
it works great! BUT NOT IN DOTNET with OleProvider!

What is the problem...?

Thanx for every hint!
Jazper

[Tobin Harris]

"Jazper" <j.***@bluewin. ch> wrote in message
news:03******** *************** *****@phx.gbl.. .

Hi i got a big problem and don't know how to solve it...
//---My Code for Insert (Exception raises)--------------
OleDbCommand cmdI = new OleDbCommand("I NSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);

I'm guessing a little here, but are you sure you can insert into an
Autonumber column? Does this work?

INSERT INTO jaz_test(User) VALUES('UserX')

then try a

SELECT @@IDENTITY

to get the newly created autonum?

HTH

Tobin Harris

[Jazper]

I think i found the error
User is a Access Reserved Word. When i do the sql like
this:
INSERT INTO jaz_test([ID], [User]) VALUES(3, 'UserX')"
then it works...

but why? why can execute the query-editor of access the
query and Dotnet not???

is there a list of reserved words like "User"?

Regards, Jazper

[Jazper]

Hi Tobin

No it did not work. Also with Autocolumn. But i think i
found the error..

User is a Access Reserved Word. When i do the sql like
this:
INSERT INTO jaz_test([ID], [User]) VALUES(3, 'UserX')"
then it works...

but why? why can execute the query-editor of access the
query and Dotnet not???

is there a list of reserved words like "User"?

Regards, Jazper

-----Original Message-----
"Jazper" <j.***@bluewin. ch> wrote in message
news:03******* *************** ******@phx.gbl. ..

Hi i got a big problem and don't know how to solve it...
//---My Code for Insert (Exception raises)--------------
OleDbCommand cmdI = new OleDbCommand("I NSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);
I'm guessing a little here, but are you sure you can

insert into anAutonumber column? Does this work?

INSERT INTO jaz_test(User) VALUES('UserX')

then try a

SELECT @@IDENTITY

to get the newly created autonum?

HTH

Tobin Harris
.

[Nicholas Paldino [.NET/C# MVP]]

Jazper,

Apparently one of the words "ID" or "User" is reserved, and you need to
use the bracket notation to escape it so that it can be interpreted as a
column name and not a reserved word.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- ni************* *@exisconsultin g.com

"Jazper" <j.***@bluewin. ch> wrote in message
news:0c******** *************** *****@phx.gbl.. .

Hi Tobin

No it did not work. Also with Autocolumn. But i think i
found the error..

User is a Access Reserved Word. When i do the sql like
this:
INSERT INTO jaz_test([ID], [User]) VALUES(3, 'UserX')"
then it works...

but why? why can execute the query-editor of access the
query and Dotnet not???

is there a list of reserved words like "User"?

Regards, Jazper

-----Original Message-----
"Jazper" <j.***@bluewin. ch> wrote in message
news:03******* *************** ******@phx.gbl. ..

Hi i got a big problem and don't know how to solve it...
//---My Code for Insert (Exception raises)--------------
OleDbCommand cmdI = new OleDbCommand("I NSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);

I'm guessing a little here, but are you sure you can

insert into an

Autonumber column? Does this work?

INSERT INTO jaz_test(User) VALUES('UserX')

then try a

SELECT @@IDENTITY

to get the newly created autonum?

HTH

Tobin Harris
.

[Dr.Bob]

Once I had this problem. I finally determined that the reserved word was
not in Access, but in the SQL. "User" is reserved in SQL - So, the Access
SQL editor maybe is not fully compliant, or the OLEDB pass through chokes.
One place to find the SQL reserved words is:
http://www.jsoftware.com/books/help/...rved_words.htm

UnDo my email.
"Jazper" <j.***@bluewin. ch> wrote in message
news:0c******** *************** *****@phx.gbl.. .

I think i found the error
User is a Access Reserved Word. When i do the sql like
this:
INSERT INTO jaz_test([ID], [User]) VALUES(3, 'UserX')"
then it works...

but why? why can execute the query-editor of access the
query and Dotnet not???

is there a list of reserved words like "User"?

Regards, Jazper