0

I'm trying to use an insert query (access is the database program) but I am getting an error as follows:

System.Data.OledDb.OleDbException (0x800004005): Operation must use an updateable query.

This is after I have created an .msi file and installed it (it works fine when debugging/running inside Visual Studio). Delete queries also do not work, nor update (just the select query). When I try to delete, I get an error saying "Could not delete from specified tables") . (here is the complete exception message: http://imgur.com/BSuh0nS)

Here is the code for both methods that handle this

public void InsertData()
{
        dbc.Open();

        using (dbCmd = new OleDbCommand("INSERT INTO members (household_head, birthday, phone, email, address, status, spouse, spouse_birthday, spouse_phone, spouse_email, " +
          "anniversary, spouse_status, child1, child1_birthday, child1_email, " +
     "child2, child2_birthday, child2_email, child3, child3_birthday, child3_email, child4, child4_birthday, child4_email, child5, child5_birthday, child5_email," +
     "child6, child6_birthday, child6_email, child7, child7_birthday, child7_email) " +
     "VALUES (@txtBox_householdHead, @txtBox_householdHeadBirthday, @txtBox_householdHeadPhone, @txtBox_householdHeadEmail, @txtBox_householdHeadAddress, @txtBox_householdHeadStatus, " +
     "@txtBox_spouse, @txtBox_spouseBirthday, @txtBox_spousePhone, @txtBox_spouseEmail, @txtBox_Anniversary, @txtBox_spouseStatus, " +
     "@txtBox_child1, @txtBox_child1Birthday, @txtBox_child1Email, " +
     "@txtBox_child2, @txtBox_child2Birthday, @txtBox_child2Email, @txtBox_child3, @txtBox_child3Birthday, @txtBox_child3Email, @txtBox_child4, @txtBox_child4Birthday, @txtBox_child4Email, " +
     "@txtBox_child5, @txtBox_child5Birthday, @txtBox_child5Email, @txtBox_child6, @txtBox_child6Birthday, @txtBox_child6Email, @txtBox_child7, @txtBox_child7Birthday, @txtBox_child7Email)", dbc))
        {
            try
            {
                InsertDBParameters(ref dbCmd);

                dbCmd.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
        }


        MessageBox.Show("Record inserted.");

        ClearAll(this);

        dbc.Close();

}

and the InsertDBParameters method:

private void InsertDBParameters(ref OleDbCommand cmd)
{

        foreach (Control c in Controls)
        {
            if (c is TextBox)
            {
                listOfTextboxes.Add(new KeyValuePair<string, string>(((TextBox)c).Name, ((TextBox)c).Text));
            }
        }

        for (int i = 0; i < listOfTextboxes.Count; i++)
        {
            cmd.Parameters.AddWithValue(String.Format("@{0}", listOfTextboxes[i].Key.ToString()), listOfTextboxes[i].Value);
        }

}

Any help would be appreciated,

Thanks!

I'm imagining it must be a permission thing from what I've researched so far, but am not sure. Here is my connection string if it helps as well:

OleDbConnection db = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\qbcdb.mdb");
user2101411
  • 1,204
  • 2
  • 14
  • 33
  • Possible duplicate. Check this answer: https://stackoverflow.com/a/20420815/1300049 – JleruOHeP Aug 31 '17 at 22:42
  • Possible duplicate of ["Operation must use an updateable query" error in MS Access](https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access) – JleruOHeP Aug 31 '17 at 22:43
  • how would I go about changing the permissions inside of an installed program? That doesn't seem right. – user2101411 Aug 31 '17 at 22:43
  • the select query works fine, it is the modifying queries (update, delete) that do not work right. – user2101411 Aug 31 '17 at 22:44
  • i'm not using a join or anything. I don't see how this question is a possible duplicate. – user2101411 Aug 31 '17 at 22:46
  • I'd test the insert statement directly in MS-Access. Add a print statement of dbCmd before the ExecuteNonQuery line. Copy and paste the statement into MS-Access to make sure the query itself work outside of c#. Then figure out which user is executing the statement in c# and make sure that user has permissions to update the MS-Access DB. – LAS Aug 31 '17 at 23:03
  • @LAS the insert query works fine when executed in access. I didn't make any permissions, I just used advanced installer to build a msi installer of the solution. What would I do in this case? – user2101411 Aug 31 '17 at 23:15
  • Have you tried adding `User Id` and `Password` to the [connection string](https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/datadirectory-functionality/)? If a copy of the database is created when the application is installed, is the database file perhaps getting set to Read-Only? – Poosh Sep 02 '17 at 05:00

0 Answers0