Unable to insert data in MS Access using parameterized query in c#

Question

I have the following code which tries to store e values form 3 textboxes into a MS Access 2007 database.

string ConnString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\dxs.accdb");
string SqlString = "Insert Into tests( [Nam], [add], [phone]) Values (?,?,?)";

using (OleDbConnection conn = new OleDbConnection(ConnString))
{
   using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
   {
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.AddWithValue(@"Nam", textBox1.Text);
      cmd.Parameters.AddWithValue(@"add", textBox2.Text);
      cmd.Parameters.AddWithValue(@"phone",textBox3.Text);

      conn.Open();
      cmd.ExecuteNonQuery();
      MessageBox.Show("entered");
   }
}

But even though the code is correct after entering values nothing is being stored in table.

What is that question mark instead of valid parameters in the Insert statement ? — Amin Sayed, Nov 18 '12 at 07:54
markers representing three textbox fields... i've tried even with @ , but to no avail.. — Amardeep Mandrekar, Nov 18 '12 at 07:57
Check out this link http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access — Amin Sayed, Nov 18 '12 at 07:58
I suggest using the ODBC driver instead of OLE-DB. OLE-DB is dead: http://www.sadev.co.za/content/ole-db-dead-long-live-odbc it might not fix the problem, but it might prevent problems in the future. — Dai, Nov 18 '12 at 11:13
Also, I recommend storing the return value from `ExecuteNonQuery`, it returns the number of rows changed in the database. If it's zero then you know something is up. — Dai, Nov 18 '12 at 11:14
[Parametrized Insertion](http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database). If you would still unable to insert, Then I would suggest you test with my tested connection string your insertion will be successful at last :) — Sami, Nov 18 '12 at 18:17

score 2 · Answer 1 · answered Nov 18 '12 at 08:06

2

Shouldn't

cmd.Parameters.AddWithValue(@"Nam", textBox1.Text);

Be:

cmd.Parameters.AddWithValue("@Nam", textBox1.Text);

And so on for the other parameters?

answered Nov 18 '12 at 08:06

nemmy

753
1
6
19

tried, not working... all the while data is being entered but when i refresh table no data is visible... – Amardeep Mandrekar Nov 18 '12 at 08:10
No, `@"foo"` denotes a "Verbatim string" whereas `"@foo"` denotes a regular string containing a T-SQL parameter. The `@` can be omitted completely from the OP's code because there's no need for a verbatim string (because there's no escape sequences) and also because he isn't using T-SQL. – Dai Nov 18 '12 at 11:12

score 1 · Accepted Answer · answered Nov 18 '12 at 08:04

1

When i had the similar problems, solution was:

If database is part of application it can be copied in a bin folder - and then application work with it. That is why you can`t find your changes in datatables with MS Access client.

answered Nov 18 '12 at 08:04

Dzmitry Martavoi

6,867
6
38
59

my project has one dx database in main project folder as well as the BIN folder. if i delete the latter one, will my problem will be solved?? – Amardeep Mandrekar Nov 18 '12 at 08:32
If you delete the database in the `bin` folder, it will simply be copied over again at your next build, without the changes. You need to select the database in the Solution Explorer, and look in the properties for `Copy to Output directory` which should be set to `Do not copy`. Then, you need to connect to the database in the project folder, which means you cannot use `|DataDirectory|`, but the absolute path to the database. – Zev Spitz Nov 18 '12 at 12:43
this kinda solves my problem but a new one arises, (i placed the same database in d drive and made the connection). if, say, i want to make this code work on multiple systems. will it work? now that the database is not in output directory.. will i have to manually add the database to the other machines d drive?? or is there a work around?? – Amardeep Mandrekar Nov 19 '12 at 14:49
You should keep relative path to your DB - its a good practice (for such case). If your application needs for a database, you should keep it as a part of project. You should be careful, becouse in this case your DB will be copied(replaced) after every build. But when you will work with your app. without rebuilding(recompiling) you will use the same database all the time(until you rebuild your project!). – Dzmitry Martavoi Nov 19 '12 at 17:05

Sami · Answer 3 · 2012-11-20T10:52:30.943

1

Make sure your database exists in output(bin) folder where exists your exe file of project. If not then copy it there. After your have your database file at right place, You will be to see the changes.

Additionally, you also need few changes in your code, you have problem with your parameter. Change Values (?,?,?) to Values (@Nam,@add,@phone)"; and @"Nam" to "@Nam". See the comments Correction1 and Correction2.

Also no need to use double slash \\ when you are using @ at beginning of string

string ConnString=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dxs.accdb");

string sql="Insert Into tests([Nam],[add],[phone]) Values (@Nam,@add,@phone)";
// Correction 1: Above line is changed ?,?,? to parameter names (names used by your command)

using (OleDbConnection conn = new OleDbConnection(ConnString))
{
   using (OleDbCommand cmd = new OleDbCommand(sql, conn))
   {
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.AddWithValue("@Nam", textBox1.Text);
      cmd.Parameters.AddWithValue("@add", textBox2.Text);
      cmd.Parameters.AddWithValue("@phone",textBox3.Text);
      // Correction 2: your parameter names are changed @"xyz" to "@xyz"

      conn.Open();
      cmd.ExecuteNonQuery();
      MessageBox.Show("entered");
   }
}

edited Nov 20 '12 at 10:52

answered Nov 18 '12 at 08:46

Sami

8,168
9
66
99

The downvote is uncalled for; this would appear to be the solution. `|DataDirectory|` refers to the database in the output folder, not the database in the project folder. – Zev Spitz Nov 18 '12 at 12:45
@ZevSpitz. I appreciate your down vote. But I can not understand (have not info about) output folder. I did not think so as you told, but I am ready to think, if I get some idea – Sami Nov 18 '12 at 16:33
I said **the downvote is not called for** - there shouldn't be a downvote, because this appears to be the answer. (The downvote wasn't mine.) However, you shpuld edit this to use parameters, like in the original question, to avoid SQL injection. – Zev Spitz Nov 18 '12 at 16:41
Thanks @ZevSpitz. I have made some search. used parametrized query and identified the problem of OP – Sami Nov 18 '12 at 18:13
Your most recent edit is **not** needed to resolve the OP's problem. [The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement or a stored procedure.](http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx). I think **you had it right the first time** when you told the OP to remove `|DataDirectory|` from the connection string. Does this newly edited code even run? – Zev Spitz Nov 18 '12 at 18:53
All I meant in my previous comment about parameters was that your example used literals in building the SQL statement, and not parameters. This might solve the OP's problem, but it is bad practice because of [SQL injection](http://en.wikipedia.org/wiki/Sql_injection) - someone might put an SQL statement in the textbox and it will run. – Zev Spitz Nov 18 '12 at 18:55
i no longer have the database in the output forlder. but in the d drive, i'm able to view data being entered through the app. now one more question. will the project work on diff. systems?? or i have to manually add the databse to the other machines d drive?? – Amardeep Mandrekar Nov 19 '12 at 14:52
@AmardeepMandrekar. Should Open another question `ConnectionString for Database on network PC`. It will be helpful for the others as well – Sami Nov 19 '12 at 15:41
@AmardeepMandrekar. Follow [This Question Just For You !](http://stackoverflow.com/questions/13457608/oledb-connection-string-for-network-database-ms-access#comment18404567_13457608) – Sami Nov 19 '12 at 16:12

score -1 · Answer 4 · answered Nov 18 '12 at 16:46

-1

your insert statement should be like dis

    string SqlString = "Insert Into tests( [Nam], [add], [phone]) Values (@Nam, @add, @phone)";


   cmd.CommandType = CommandType.Text;
  cmd.Parameters.AddWithValue("@Nam", textBox1.Text);
  cmd.Parameters.AddWithValue("@add", textBox2.Text);
  cmd.Parameters.AddWithValue("@phone",textBox3.Text);

try this

answered Nov 18 '12 at 16:46

Sohail

780
3
14
27

downvote - [The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider for ODBC do not support named parameters for passing parameters to an SQL statement or a stored procedure.](http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx). – Zev Spitz Nov 18 '12 at 18:57

Unable to insert data in MS Access using parameterized query in c#

4 Answers4