Access 2007 database is not updating

Question

I'm working on a .Net Desktop app that uses Access database. I'm using the Contacts form and trying to change the Category field, which has multiple choices in a combo box for value. The value I'm trying to set is in the list of choices, but it doesn't do anything. Here is my code. Please shed some light on what's going on. That code seems to be working for a DELETE command.

        string list = string.Join(", ", f);

        string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtDB.Text + "";

        string ComStr = "UPDATE Contacts SET Category = ? where [E-mail Address] in (?)";
        using (OleDbConnection con = new OleDbConnection(ConnStr))
        {
            con.Open();
            using (OleDbCommand com = new OleDbCommand(ComStr, con))
            {
                com.Parameters.AddWithValue("List", list);
                com.Parameters.AddWithValue("Category", "Не получава мейли");
                com.ExecuteNonQuery();
            }
            con.Close();
        }

You must add the parameters in the same order as they occur in the query. — Fionnuala, Nov 19 '12 at 16:05

score 0 · Answer 1 · answered Nov 19 '12 at 16:23

I think this should work:-

string ComStr = "UPDATE Contacts SET Category = @Category where [E-mail Address] in @List";
            using (OleDbConnection con = new OleDbConnection(ConnStr))
            {
                con.Open();
                using (OleDbCommand com = new OleDbCommand(ComStr, con))
                {

                    com.Parameters.AddWithValue("@Category", "Не получава мейли");
                    com.Parameters.AddWithValue("@List", list);
                    com.ExecuteNonQuery();
                }
                con.Close();
            }

It still doesn't do anything to the database. – Mariela Nikolova Nov 20 '12 at 17:53 — Mariela Nikolova, Nov 20 '12 at 17:53

score 0 · Answer 2 · answered Nov 22 '12 at 09:51

I found the answer. Every item in the list must be in... ok i don't know the word, but here's my working code:

    string list = string.Join("', '", f);
    string l = "'" + list + "'";

    string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtDB.Text + "";

    string ComStr = "UPDATE Contacts SET Category = @Category where [E-mail Address] in (" + l + ")";
    using (OleDbConnection con = new OleDbConnection(ConnStr))
    {
        con.Open();
        using (OleDbCommand com = new OleDbCommand(ComStr, con))
        {

            com.Parameters.AddWithValue("Category", "Не получава мейли");
            com.ExecuteNonQuery();
        }
        con.Close();
    }

Thanks for the tips and the time guys.

Access 2007 database is not updating

2 Answers2