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? :)

Answer 1

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.

Answer 2

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.