none
Updating Access DB in C# RRS feed

  • Question

  • I'm using C# to write a windows form application that updates an Access DB using textboxes and a button to save. I can pull in the information from the DB. My problem is when I enter values into the textboxes and click the save button, it won't update the DB. I get no errors, so I'm not understanding why it won't update it. Any suggestions on my code? Thanks.

    private void btnUpdate_Click(object sender, EventArgs e)
        {
          using (OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Master_2.MDB"))
          {
            c.Open();
            OleDbCommand sComm = new OleDbCommand("Update Sheet1 set [Column1] = '" + this.txtblock1.Text + "', [Column2] = '" + this.txBlock2.Text + "', [Column3] = '" + this.txtBlock3.Text + "', [Column4] = '" + this.txtBlock4.Text + "', [Column5] = '" + this.txtBlock5.Text + "' + "' WHERE [PK_Column] = " + this.txtDefault1.Text, c);
            sComm.ExecuteNonQuery();
            c.Close();
          }
        }

     

    Sunday, May 30, 2010 10:22 PM

Answers

  • Hi Zee Dee,

    is your database file included in the project ?

    Since the code is not raising an exception I have to assume that it gets executed. So I would think that you're looking at the wrong database - wrong mdb file.

    If the file is included in the project, it probably has the "Copy To Output Directory" property set to "Copy Always". In this case the mdb file is copied to your bin\debug folder every time you compile the project. So when you run the query go to bin\debug folder, and check the Master2.mdb file there. Do not check the file that resides in your project root folder. Also do not recompile the project in the meantime because the changes you made would be overwritten.

    Hope this helps ...

    best regards,
    Vladimir

    • Marked as answer by Zee Dee Monday, May 31, 2010 12:06 AM
    Sunday, May 30, 2010 11:34 PM

All replies

  • Hello,

    You may need to change your CPU targeting to x86 instead of all CPU's.

    Also, your Data Source has no root directory. I'm surprised it doesn't give you an error that it can't find the db. You can't fully qualifying the path to the db.

    Other than that, you code looks fine. However, those field names look like you've imported from an Excel file. Make sure the fieldnames are correct.

    Additionally, put a TRY CATCH block around the code.

    Adam


    Dibble and dabble but please don't babble.
    Sunday, May 30, 2010 11:25 PM
  • Hi Zee Dee,

    is your database file included in the project ?

    Since the code is not raising an exception I have to assume that it gets executed. So I would think that you're looking at the wrong database - wrong mdb file.

    If the file is included in the project, it probably has the "Copy To Output Directory" property set to "Copy Always". In this case the mdb file is copied to your bin\debug folder every time you compile the project. So when you run the query go to bin\debug folder, and check the Master2.mdb file there. Do not check the file that resides in your project root folder. Also do not recompile the project in the meantime because the changes you made would be overwritten.

    Hope this helps ...

    best regards,
    Vladimir

    • Marked as answer by Zee Dee Monday, May 31, 2010 12:06 AM
    Sunday, May 30, 2010 11:34 PM
  • That was it Vladimir. Now how do I make it save to the proper DB of my choice? Thanks for the help!
    Monday, May 31, 2010 12:06 AM
  • You can specify a relative path to your database, two folders up from bin\debug to get to the root project folder

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\Master_2.MDB"

    and in that case you should switch from Copy Always to Do Not Copy since you would target the original database.
    You would of course have to change the connection string when deploying application. Common scenario would be storing the connection string in the app config file, either directly or using project settings.

    The other approach would be to change "Copy Always" to "Copy if newer", so that the mdb file in the bin\debug folder is only overwritten when you actually change the original database file, e.g. add table, add/remove column ...

    BTW, targeting a copy of the original database has its' advantages ... for instance you can run the same commands over and over again for debugging purposes without primary key constraints being violated. You just need to know in which database to look for the changes.

    Best regards,
    Vladimir

    Monday, May 31, 2010 12:52 AM
  • Ok, I understand that. Now when I complete the project and need to distribute it, I would add to the provider string where the database resides. Something like this?

    using (OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Projects\NewProject\Database\Database1.MDB"))
    Sorry for the dumb questions. This is the first time tackling C# and trying to learn as I go. Thanks!

    Monday, May 31, 2010 1:20 AM
  • You probably want something like Application.StartupPath & "/Database1.MDB"

    Adam


    Dibble and dabble but please don't babble.
    Monday, May 31, 2010 2:27 AM
  • There are no dumb questions,

    at the end you need to specify path to your database after deploy and you can do that in more than way. If you plan to deploy database in the application folder then relative path would do just fine and you can use the connection string from your first post.

    using (OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Master_2.MDB"))

    This will look for the database in the application folder. So if you want to use original database, that's included in the project and resides in the root project folder when developing and debugging, and deploy database in the application folder, you will have to switch the connection string each time you are deploying your application between:

    using (OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\Master_2.MDB"))
    and 
    using (OleDbConnection c = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Master_2.MDB"))

    I don't know if there is some more elegant solution, and I hope I was clear enough ... maybe we'll get someone else's view on this too.

    Best regards,
    Vladimir

    Monday, May 31, 2010 9:48 AM
  • Vladimir -- "Copy To Output DirectoryThat was exactly my issue. Thanks!!

    • Edited by TH-AW-1 Saturday, August 9, 2014 8:43 PM
    Saturday, August 9, 2014 8:42 PM