C# & OleDB error with SQL parameterized query

Question

First of all sorry if the formatting is poor, I'm new to this.

Okay so I recently started learning C# in order to program a local database software. Now the book I'm using has a chapter on this but at the SQL injection prevention a problem has arisen. I'm using C# in Visual Studio and MS Access with the OleDB package.

Now when I use a method at first nothing happens (everything works fine). But the next query I use after that gives me the following error message:

COM object that has been separated from its underlying RCW cannot be used

This only happens when using parameterized SQL queries.

---

So this code is executed after clicking a button. It takes a string from a textbox and puts it into a SQL command parameter. The update table method executes the command and updates the table in the window. The error appears after I added a parameter to the command.

private void CmdSearchName_Click(object sender, EventArgs e)
{
    cmd.CommandText = "SELECT * FROM contacts WHERE name LIKE ?";

    cmd.Parameters.Add("", OleDbType.VarChar).Value = txtInput.Text;
    UpdateTable();
    cmd.Parameters.Clear();
}

Now as stated above the first time I would run this it works. Running it again throws the error. Running it a third time, it works again while the fourth doesn't. It goes on like this.

If additional information is needed just ask. Thanks for your help.

Answer 1

If you going to update the table, then you need which row id to update, and your code will look much like this:

       using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
       {
           string strSQL = "UPDATE contacts SET [name] = @Name WHERE ID = @id";

           using (OleDbCommand cmd = new OleDbCommand(strSQL, conn))
           {
               cmd.Parameters.Add("@Name", OleDbType.VarWChar).Value = textInput.Text;
               cmd.Parameters.Add("@id", OleDbType.Integer).Value = 124;

               conn.Open();
               cmd.ExecuteNonQuery();
           }
       }

So, to update the data, you need to use a update command. And you need to supply a "id", else the update command will update all rows in the database.

As a "also just in case", I put square brackets around the column "name", since it is a reserved word.

I used a "made up" value for the id of 1234, and you would replace that ID with your correct id value to update the "one" correct row.

I will also note that while I used named parameters like @Name and @id?

The order matters in which you add such parameters when using Access as a database.