[2.0] C# with Access backend.

[JenniferBabe]

Is it just me or is Access wierd when connected to C#? For e.g. the following query works:

Insert into table values('1', '2');

However this does not:

Insert into table (username, password) values ('1', '2');


If I take the 2 queries and paste it into the query facility in the access database both of them works fine! But for some reason the first one is working and the second one is not (and the second one is correct).

I never met this problem with oracle or sql server, is this normal?

[ComputerJy]

The ODBC driver for access is old to maintain compatibility with version '97

[JenniferBabe]

ohhhhhhhhhh that explains it! this is wierd. For e.g. i was inserting into a table with a column named password. i tried everything and it still failed even though the insert statement was correct. I had to change the column name from password to password1 and then it worked.

This sucks!

[ComputerJy]

That is something else
The keywords ID & Password ... etc are reserved by T-SQL, so in order to refer to the Password cell you must use [Password]

[JenniferBabe]

Thank you very much, it worked fine!

[JenniferBabe]

I don't know if you could help me with this query:

sql = "Insert into Partner values (14, 'test', 'tset2', 'tset3')";

Then executed it. Now this is a correct SQL statement but something is wrong with that driver from C#. Here is my observation. I've done inserts with a few other tables without any troubles, but this one is different, the primary key (14) is a number while the other tables is autonumber. I don't know if this matters.

Now when the table is empty and I execute the above statement it works fine. If I execute it another time changed 14 to 15 (since its the PK), its generating this message: DATA TYPE MISMATCH IN CRITERIA EXPRESSION.

But the statement is valid, its correct! If i copy the statement to Access query facility its working fine! I cannot understand it. For the connection I used: OLEDBCONNECT, and command: OLEDBCOMMAND.

I dont know what else to do. Could anyone help me pls?

Jennifer

[ComputerJy]

I think you should be asking this question in the "Database Development Forum" (http://www.vbforums.com/forumdisplay.php?f=3).
I'm not very experienced when it comes to working with Access DB, but I tried and it seems that it has a different way of inserting data. I tried to design a visual query and the sql looked like INSERT INTO Faculty ( ID, [Last Name], [First Name] )
SELECT 1 AS Expr1, 'LastName' AS Expr2, [FirstName] AS Expr3;

[JenniferBabe]

hey thanks, i'll check that forum!