none
Strange problem when deleting from an Access DB RRS feed

  • Question

  • I have a data-bound listbox on a form. On pressing a Delete command button I on the form, the following code is executed:-
    private
     void
     btnDelete_Click(object
     sender, EventArgs e)
            {
                string
     strSQL = "DELETE FROM Categories WHERE Category = '"
    ;
                strSQL = String.Concat(strSQL, listBox1.Text);
                strSQL = String.Concat(strSQL, "'"
    );
    
                dataAdapter.DeleteCommand = new
     OleDbCommand(strSQL, AccessConnection);
                dataAdapter.DeleteCommand.ExecuteNonQuery();
                dTable.Clear();
                dataAdapter.Fill(dTable);
            }
    
    So whatever value is selected in the listbox, is deleted. And the list in the listbox is refreshed to show the absence of the deleted item.

    The Access DB originally had 4 records entered manually - lets say A,B,C and D. Now if I delete record B, the listbox correctly shows only A,C and D. If I exit the application and run it again immediately, the list is correct again - showing only A,C and D. However, if I close the program, make any changes in the code (lets say something simple as delete a semi-colon and then typing it in again) and then run the program again, all 4 records - A,B,C and D - are on the listbox again.

    So I opened the Access file and repeated the process described above. The row actually gets deleted from the file, but when I change some bit of the code and re-run the app, the rows magically come back??????!!!

    What's going on?
    Sunday, April 12, 2009 1:21 PM

Answers

  • There is no magic. I would see two possible scenarions:

    1. Make sure your code work with the single copy of the database. You could check it scanning hard drive for the database file name and check modify dates on found files

    2. It is possible in a case if application inserts all 4 records somewhere in a code, for example on start-up of your application
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by tushar_t Tuesday, April 14, 2009 12:56 AM
    Monday, April 13, 2009 10:01 AM
    Moderator

All replies

  • There is no magic. I would see two possible scenarions:

    1. Make sure your code work with the single copy of the database. You could check it scanning hard drive for the database file name and check modify dates on found files

    2. It is possible in a case if application inserts all 4 records somewhere in a code, for example on start-up of your application
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by tushar_t Tuesday, April 14, 2009 12:56 AM
    Monday, April 13, 2009 10:01 AM
    Moderator
  • Thank you for your reply. The problem was the first point you mentioned. I asked this question the C# Express Edition forum as well and received the answer there:

    This sounds like the database is being copied into the bin\debug directory when you compile the program.
    Have a look at the directory where the DB is and check the bin\debug directory (and sub-dirs) for the .mdb file.
    I've seen this with SQL Server Express - don't know if the same applies to Access.


    I had enabled this option when I used the wizard for data binding for the first time.

    Tuesday, April 14, 2009 12:55 AM