INSERT query OleDB C#

Question

I know it was 1000000000 times already, but none solution helped to me. I want to insert data in C# using OleDB. I tried mln solutions but here is the easiest one which should work but it doesn't:

 SQLCONNECTION = @"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\dbScenariusz.mdb";

 using (OleDbConnection connection = new OleDbConnection(SQLCONNECTION))
            {
                string sql = "INSERT INTO Table (content) VALUES('lala')";
                connection.Open();
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.ExecuteNonQuery();
                connection.Close();
            }

SQLCONNECTION is ok. It works fine for the SELECT query.

string sql - I tried this query in Access and it works fine.

I get no error. It just didn't insert anything to the database. When I run the query in Access (the same database) the row is inserted.

The strange thing is that command.ExecuteNonQuery(); returns 1! That means that 1 row was affected!

I really have no idea where the problem is, so I really appreciate any help. Sorry for my english.

UPDATE: Another strange thing. I change query to update and it works fine! really wtf? :)

just a guess, as your code seems fine. are you running it on a 64 bit system? — gsharp
– gsharp, Commented Feb 21, 2011 at 13:54
Well Zitun, 1) I insert the row through Microsoft Access (using the same query) 2) I open the table in Visual Studio and I see the row inserted through Access! But the same thing doesn't work in Visual. I really have no idea what's wrong. — arczi
– arczi, Commented Feb 21, 2011 at 14:40

Emond · Accepted Answer · 2011-02-21 14:06:36Z

3

You are connecting to the DataDirectory. Is the .mbd file being copied after the build? In other words are you re-deploying the database with each build and thereby losing the inserts?

A quick test could be using the same code and same connection to do a select after the insert.

edited Feb 21, 2011 at 14:06

answered Feb 21, 2011 at 13:55

Emond

50.8k11 gold badges86 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

arczi Over a year ago

I hoped this is the problem but no. 1) I run insert query and get return value=1 (1 row affected) 2) I run select query (from the same table) with success 3) I check the table and nothing was inserted 4) Unbelievable!

arczi Over a year ago

You were right Erno! Here is the problem: I've got the database in the main directory, the .mdb file is copied to bin/Debug/ and all changes are made on this file. So when I run the application again all changes in database are gone. How should I change it?

Emond Over a year ago

Simply by not redeploying the database every single time and placing the database in a directory that doesn't get updated.

Vivek Jain · Accepted Answer · 2014-10-30 08:41:05Z

In the solution Explorer, check app.config. Double click the app.config, then re-enter the connectionString. Give the path of your database. For example, in my project the default location of connectionString is:

connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App_Data\database.accdb;Persist Security Info=True"

Suppose the database is stored in this location:

C:\Documents and Settings\Amuk\My Documents\Visual Studio 2010\Projects\insertion\insertion\App_Data

therefore, replace the connectionString with

connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Amuk\My Documents\Visual Studio 2010\Projects\insertion\insertion\App_Data\database.accdb;Persist Security Info=True"

your problem will be solved, i hope this will definitely help you, i was also getting the same problem, and by replacing the connectionString with original path, the database is storing all records.

Sachin Shanbhag · Accepted Answer · 2011-02-21 13:44:04Z

0

You can try to use transaction to commit your changes.

command.Transaction = connection.BeginTransaction(); //after connection.open()

Then add

command.Transaction.Commit(); //Before connection.close()

answered Feb 21, 2011 at 13:44

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

3 Comments

arczi Over a year ago

I did exactly like you suggested but nothing has changed.

Sachin Shanbhag Over a year ago

@arczi - Have you checked properly? There are no errors at all? Apart from this, I dont think there is any issue with your code.

arczi Over a year ago

No errors and no warnings. 1) I insert the row through Microsoft Access (using the same query) 2) I open the table in Visual Studio and I see the row inserted through Access! I really have no idea what's wrong.

Collectives™ on Stack Overflow

INSERT query OleDB C#

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related