uneable to update an Access database
-
I get an exception in the last line, ExecuteNonQuery. It tells me there is something wrong with the SQL command, although this same command works fine within Access. I have tried this code with insert instead and it works. myConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=alfapet.mdb"); myConnection.Open(); using(myConnection) { OleDbCommand com = new OleDbCommand( "UPDATE userinfo SET Password='"+newPass.Pass+"' WHERE Name='"+user.Name+"' AND Password='"+oldPass.Pass+"'", myConnection); int i=com.ExecuteNonQuery();
-
I get an exception in the last line, ExecuteNonQuery. It tells me there is something wrong with the SQL command, although this same command works fine within Access. I have tried this code with insert instead and it works. myConnection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=alfapet.mdb"); myConnection.Open(); using(myConnection) { OleDbCommand com = new OleDbCommand( "UPDATE userinfo SET Password='"+newPass.Pass+"' WHERE Name='"+user.Name+"' AND Password='"+oldPass.Pass+"'", myConnection); int i=com.ExecuteNonQuery();
The way you're doing it is not optimal and often leads to problems because certain characters - like quotes - are not escaped properly. See the documentation for the
OleDbParameterclass and use a paramterized query like so:OleDbCommand cmd = myConnection.CreateCommand();
cmd.CommandText = "UPDATE userinfo SET Password=? WHERE " +
"Name=? AND Password=?";
cmd.Parameters.Add("NewPassword", OleDbType.VarWChar, 40).Value = newPassword;
cmd.Parameters.Add("Name", OleDbType.VarWChar, 40).Value = user.Name;
cmd.Parameters.Add("OlePassword", OleDbType.VarWChar, 40).Value = oldPassword;
int i = cmd.ExecuteNonQuery();
Console.WriteLine("{0} record(s) modified", i);The OLE DB provider for ADO.NET does not use named parameters, and instead uses positional parameters using the question mark, so you must add your parameters in the same order. The example aboves assumes you declare your fields as Text using 40 characters as the length. This is just an example, though, so read the documentation for the
OleDbParameterfor more information.Microsoft MVP, Visual C# My Articles