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

You are close with the rest of your connection and such, but as you note, doing it with parameterized queries is safer from SQL-Injection...

    // Some engines used named parameters, others may not... The "?"
    // are "place-holders" for the ordinal position of parameters being added...
    String MyQuery = "Update MyTable set SomeField = ?, AnotherField = ? "
        + " where YourKeyField = ?";

    OleDbCommand MyUpdate = new OleDbCommand( MyQuery, YourConnection );

   // Now, add the parameters in the same order as the "place-holders" are in above command
   OleDbParameter NewParm = new OleDbParameter( "ParmForSomeField", NewValueForSomeField );
   NewParm.DbType = DbType.Int32;   
   // (or other data type, such as DbType.String, DbType.DateTime, etc)
   MyUpdate.Parameters.Add( NewParm );

   // Now, on to the next set of parameters...
   NewParm = new OleDbParameter( "ParmForAnotherField", NewValueForAnotherField );
   NewParm.DbType = DbType.String;   
   MyUpdate.Parameters.Add( NewParm );

   // finally the last one...
   NewParm = new OleDbParameter( "ParmForYourKeyField", CurrentKeyValue );
   NewParm.DbType = DbType.Int32;   
   MyUpdate.Parameters.Add( NewParm );



  // Now, you can do you 
  MyUpdate.ExecuteNonQuery();