How do I update a table using oledb parameters?
I have a table which has three fields, namely LM_code, M_Name, Desc. LC_code is an autogenerated Id string that stores this update to M_Name and Desc. I used the normal update command, the value is passed at runtime but the fields are not updated. I hope that 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 the DataManager class, I am executing a 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");
}
a source to share
This annoyed me, sluggish little OleDB, so I'll post my solution here for posterity. This is an old post, but seems like a good place.
OleDB doesn't recognize named parameters, but apparently it does recognize that you are trying to pass a named parameter, so you can use it to your advantage and make your SQL semantic and understandable. As long as they are passed in the same order, it takes a variable as a named parameter.
I used this to update a simple Access database on 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 might look like a lot of code, and yes, you are technically repeating yourself, but it makes the world easier when you later play cell dots.
a source to share
You are close to the rest of your connection and the like, but as you noticed, execution with parameterized queries is safer from within 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();
a source to share
Just to add to RJB's answer, it's a little known fact that OleDb actually accepts named parameters. You just need to declare the parameters in SQL.
See: low-bandwidth.blogspot.com.au/2013/12/positional-msaccess-oledb-parameters.html
If you DO NOT declare parameters in SQL, OleDb uses pure positional parameter insertion, and it doesn't matter if the parameter names match SQL or if the parameters are used twice in SQL - it will just go through and blindly replace any parameters found in SQL in order from start to end, with transferred.
However, if you define the parameters correctly, you take advantage of named parameters and parameters that can be repeated multiple times in the SQL statement.
a source to share