ODBC Insert not working on Access Database

Question

Right now I'm using an Access Database as a dummy ODBC database (so I don't damage any valuable information during development) and I'm having trouble inserting records into it.

I can read from this DB just fine and the Insert command goes through, but when I try to read the record back it isn't there

    using (OdbcConnection conn = new OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=" + Directory.GetCurrentDirectory() + @"\Setup DB.accdb"))
    {
        conn.Open();
        int ID;

        //Autoincrement doesn't work, so new ID is LASTID++
        using (OdbcCommand query = new OdbcCommand("SELECT MAX(ID) AS LASTID FROM Setups", conn))
        {
            using (var reader = query.ExecuteReader())
            {
                reader.Read();
                ID = int.Parse(reader["LASTID"].ToString()) + 1;
            }
        }

        transaction = conn.BeginTransaction();
        using (OdbcCommand insert = new OdbcCommand("INSERT INTO Setups (ID, PartNumber,LeafSide,Width) VALUES (?, ?, ?, ?)", conn))
        {
            //If ID is left out ^ then an error is thrown about the ID being null
            insert.Parameters.Add("@ID", OdbcType.Int).Value = ID;
            insert.Parameters.Add("@PartNumber", OdbcType.VarChar).Value = PartNo;
            insert.Parameters.Add("@LeafSide", OdbcType.VarChar).Value = AorB;
            insert.Parameters.Add("@Width", OdbcType.Double).Value = Width;

            insert.Transaction = transaction;

            //This temp shows that 1 record is modified
            int temp = insert.ExecuteNonQuery();

            transaction.Commit();
        }
    }
    //try catch and rollback removed for display

I have a feeling I'm missing something really simple, but for the life of me I can't figure out what it is.

score 0 · Accepted Answer · answered Aug 03 '17 at 13:38

It turns out that I was missing something simple. Since I was using Directory.GetCurrentDirectory() + @"\Setup DB.accdb" as my location, I was modifying the DB in the /bin/Debug folder instead of the one that I was checking.

Thanks to this answer for making me realize this:

if your c# code executes without any exceptions, it updates the database too, Probably you are used AttachDbFilename=|DataDirectory|\yourDB.mdf in your ConnectionString means the database that is updated is located in the subfolder BIN\DEBUG folder of your project.

ODBC Insert not working on Access Database

1 Answers1