0

Trying to insert a new record into an MS Access .accdb file. When I run the code below, it appears to work fine. No errors are presented, but nothing updates in the database file either. I have verified that the database is in an accessible location.

selectedNote is an object with the three listed parameters. The only field I'm not including in the query string is the ID field which is autonumber.

string scon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = |DataDirectory|AMS.accdb";
string str = "INSERT INTO Notes ([ItemID], [Note], [Note Date]) VALUES (?, ?, ?)";

try
{
    using (OleDbConnection con = new OleDbConnection(scon))
    {
        using (OleDbCommand cmd = new OleDbCommand(str, con))
        {
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("ItemID", selectedNote.ItemID);
            cmd.Parameters.AddWithValue("Note", selectedNote.Note);
            cmd.Parameters.AddWithValue("Note Date", selectedNote.NoteDate.ToString("dd-MM-yy"));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show("Failed due to" + ex.Message);
}

Thanks all, hopefully I can get this hammered out.

EDIT: Curiously, I just found that if I hard-code a path to the .accdb file with a line like the following, it actually does write to that file. So I guess the question becomes why is it not working on the build path where the database is in the same path as the exe.

AppDomain.CurrentDomain.SetData("DataDirectory","C:\temp");

I have tried setting DataDirectory to something like AppDomain.CurrentDomain.BaseDirectory, but this doesn't seem to work either.

Adder
  • 1
  • 3
  • 2
    Set a breakpoint in your code, examine `scon` and ensure its `Data Source` is “the database file” you think it is. And [stop using `AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Dour High Arch Oct 03 '20 at 00:57
  • 1
    Is the Access database referenced by your project? If it's part of the build, and you've set it to "Copy Always", your changes would be wiped out each time you debugged it. – Tieson T. Oct 03 '20 at 06:36
  • I do have it referenced, but it is set to "Copy if Newer". I originally did have it as "Copy Always", and hoped that would be the simple fix, but alas. – Adder Oct 03 '20 at 13:36
  • The database file you're looking at and the database file you're making changes to are almost certainly two different files. You haven't given us enough information to guess what is going on, but [changing `DataDirectory` will wreck your data](https://stackoverflow.com/questions/12266924/) and you must stop making random changes before you permanently destroy your database. – Dour High Arch Oct 03 '20 at 16:46
  • I think this is to do with your date if anything. You should always pass the date format as `Format("yyyy-MM-dd")` as a SQL friendly parameter. – RazorKillBen Oct 05 '20 at 20:22

1 Answers1

2

A date should not be inserted as text, and you do have the DateTime value, thus:

cmd.Parameters.AddWithValue("Note Date", selectedNote.NoteDate);
Gustav
  • 53,498
  • 7
  • 29
  • 55