3

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.

Improve this question
7
  • 1
    What is that question mark instead of valid parameters in the Insert statement ? Commented Nov 18, 2012 at 7:54
  • markers representing three textbox fields... i've tried even with @ , but to no avail.. Commented Nov 18, 2012 at 7:57
  • Check out this link mikesdotnetting.com/Article/26/… Commented Nov 18, 2012 at 7:58
  • I suggest using the ODBC driver instead of OLE-DB. OLE-DB is dead: sadev.co.za/content/ole-db-dead-long-live-odbc it might not fix the problem, but it might prevent problems in the future. Commented Nov 18, 2012 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. Commented Nov 18, 2012 at 11:14

4 Answers 4

Reset to default
2

Shouldn't

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

Be:

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

And so on for the other parameters?

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

2 Comments

tried, not working... all the while data is being entered but when i refresh table no data is visible...
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.
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.

Improve this answer

4 Comments

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??
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.
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??
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!).
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");
   }
}
Improve this answer

9 Comments

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.
@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
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.
Thanks @ZevSpitz. I have made some search. used parametrized query and identified the problem of OP
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.. 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?
|
-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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.