Exception when try to INSERT into Access-DB

  • Thread starter Thread starter Jazper
  • Start date Start date
J

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 ConnectionString----------------------------------
Application["OleConStr"]
= "Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;"
+ @"Data Source=C:\db\jaz.mdb";

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

OleDbCommand cmdS = new OleDbCommand("Select *
from jaz_test", this.con);
OleDbDataReader rd = cmdS.ExecuteReader();
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("INSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);
cmdI.ExecuteNonQuery();
cmdI.Dispose();


//---My Problem!----------------------------------
cmdI.ExecuteNonQuery() 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
 
Jazper said:
Hi i got a big problem and don't know how to solve it...
//---My Code for Insert (Exception raises)--------------
OleDbCommand cmdI = new OleDbCommand("INSERT INTO
jaz_test(ID, User) VALUES(3, 'UserX')", this.con);
Click to expand...

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
 
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
 
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
 
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]
- (e-mail address removed)

Jazper said:
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-----


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


.
Click to expand...
Click to expand...
 
Back
Top