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();

Answer 2

Just to add to RJB's answer, it's a little-known fact that OleDb actually DOES accept named parameters. You've just got to declare the parameters in SQL as well.

See: low-bandwidth.blogspot.com.au/2013/12/positional-msaccess-oledb-parameters.html

If you DON'T declare the parameters in SQL, OleDb uses purely positional parameter insertion, and it doesn't matter if the names of the parameters match the SQL, or if parameters are used twice in the SQL - it will just go through and blindly replace any found parameters in the SQL in order from start to end, with those passed.

However if you DO declare the parameters correctly, you get the benefit of named parameters and parameters allowed to be repeated multiple times within the SQL statement.

Answer 3

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.....