c# - how to update a table using oledb parameters?

Question

I am having a table which has three fields, namely LM_code,M_Name,Desc. LC_code is a autogenerated string Id, keeping this i am updating M_Name and Desc. I used normal update command, the value is passing in runtime but the fields are not getting updated. I hope using oledb parameters the fields can be updated.

Here is my code.

public void Modify()
{
    String query = "Update Master_Accounts set (M_Name='" + M_Name + "',Desc='" + Desc + "') where LM_code='" + LM_code + "'";
    DataManager.RunExecuteNonQuery(ConnectionString.Constr, query);
}

In DataManager Class i am executing the query string.

public static void RunExecuteNonQuery(string Constr, string query)
{

    OleDbConnection myConnection = new OleDbConnection(Constr);
    try
    {
        myConnection.Open();
        OleDbCommand myCommand = new OleDbCommand(query, myConnection);
        myCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        string Message = ex.Message;
        throw ex;
    }

    finally
    {
        if (myConnection.State == ConnectionState.Open)
            myConnection.Close();
    }

}

private void toolstModify_Click_1(object sender, EventArgs e)
{
    txtamcode.Enabled = true;
    jewellery.LM_code = txtamcode.Text;
    jewellery.M_Name = txtaccname.Text;
    jewellery.Desc = txtdesc.Text;
    jewellery.Modify();
    MessageBox.Show("Data Updated Succesfully");

}

Answer 1

This annoyed me, screwy little OleDB, so I'll post my solution here for posterity. It's an old post but seems like a good place.

OleDB doesn't recognize named parameters, but it apparently does recognize that you're trying to convey a named parameter, so you can use that to your advantage and make your SQL semantic and easier to understand. So long as they're passed in the same order, it'll accept a variable as a named parameter.

I used this to update a simple Access database in a network folder.

 using (OleDbConnection conn = new OleDbConnection(connString))
 {
       conn.Open();
       OleDbCommand cmd = conn.CreateCommand();

       for (int i = 0; i < Customers.Count; i++)
       {
            cmd.Parameters.Add(new OleDbParameter("@var1", Customer[i].Name))
            cmd.Parameters.Add(new OleDbParameter("@var2", Customer[i].PhoneNum))
            cmd.Parameters.Add(new OleDbParameter("@var3", Customer[i].ID))
            cmd.Parameters.Add(new OleDbParameter("@var4", Customer[i].Name))
            cmd.Parameters.Add(new OleDbParameter("@var5", Customer[i].PhoneNum))

            cmd.CommandText = "UPDATE Customers SET Name=@var1, Phone=@var2" + 
                              "WHERE ID=@var3 AND (Name<>@var4 OR Phone<>@var5)";
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
       }
 }

It may look like an excess of code, and yes you're technically repeating yourself, but this makes it worlds easier when you're playing connect-the-dots later on.....