Update Query Cant seem to spot the problem
-
Hi there, Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update. I'd appreciate if someone could point out the mistake I have overlooked. Thanksprivate string PARAM_ID = "@ID"; private string PARAM_NAME = "@Name"; private string PARAM_AGE = "@Age"; private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb"; private OleDbConnection connection = new OleDbConnection(connectionstring); public void UpdateCustomer(CustomerInfo customer) { OleDbParameter[] parameters = new OleDbParameter[]{ new OleDbParameter(PARAM_ID,OleDbType.Integer), new OleDbParameter(PARAM_NAME,OleDbType.VarChar), new OleDbParameter(PARAM_AGE,OleDbType.Integer)}; parameters[0].Value = customer.ID; parameters[1].Value = customer.Name; parameters[2].Value = customer.Age; OleDbCommand command = new OleDbCommand(); command.Connection = this.connection; command.Connection.Open(); command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID"; for (int i = 0; i < parameters.Length; i++) { OleDbParameter parm = (OleDbParameter)parameters[i]; command.Parameters.Add(parm); } try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } finally { command.Connection.Close(); command.Dispose(); } }edit: Added the connection string. -
Hi there, Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update. I'd appreciate if someone could point out the mistake I have overlooked. Thanksprivate string PARAM_ID = "@ID"; private string PARAM_NAME = "@Name"; private string PARAM_AGE = "@Age"; private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb"; private OleDbConnection connection = new OleDbConnection(connectionstring); public void UpdateCustomer(CustomerInfo customer) { OleDbParameter[] parameters = new OleDbParameter[]{ new OleDbParameter(PARAM_ID,OleDbType.Integer), new OleDbParameter(PARAM_NAME,OleDbType.VarChar), new OleDbParameter(PARAM_AGE,OleDbType.Integer)}; parameters[0].Value = customer.ID; parameters[1].Value = customer.Name; parameters[2].Value = customer.Age; OleDbCommand command = new OleDbCommand(); command.Connection = this.connection; command.Connection.Open(); command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID"; for (int i = 0; i < parameters.Length; i++) { OleDbParameter parm = (OleDbParameter)parameters[i]; command.Parameters.Add(parm); } try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } finally { command.Connection.Close(); command.Dispose(); } }edit: Added the connection string.Can you also write the error which you are getting?
Arun Singh Noida.
-
Hi there, Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update. I'd appreciate if someone could point out the mistake I have overlooked. Thanksprivate string PARAM_ID = "@ID"; private string PARAM_NAME = "@Name"; private string PARAM_AGE = "@Age"; private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb"; private OleDbConnection connection = new OleDbConnection(connectionstring); public void UpdateCustomer(CustomerInfo customer) { OleDbParameter[] parameters = new OleDbParameter[]{ new OleDbParameter(PARAM_ID,OleDbType.Integer), new OleDbParameter(PARAM_NAME,OleDbType.VarChar), new OleDbParameter(PARAM_AGE,OleDbType.Integer)}; parameters[0].Value = customer.ID; parameters[1].Value = customer.Name; parameters[2].Value = customer.Age; OleDbCommand command = new OleDbCommand(); command.Connection = this.connection; command.Connection.Open(); command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID"; for (int i = 0; i < parameters.Length; i++) { OleDbParameter parm = (OleDbParameter)parameters[i]; command.Parameters.Add(parm); } try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } finally { command.Connection.Close(); command.Dispose(); } }edit: Added the connection string.I think that you're going to find that the order of the parameters going into your oledbcommand for access is important...
-
I think that you're going to find that the order of the parameters going into your oledbcommand for access is important...
I am getting no error. The output of ExecuteNonQuery is an "int" type. The value of this int variable indicates how many rows have been modified. In my case its zero when infact it should be one. I have one row of data in the table that needs to be updated so it should return 1 not 0. I can verify that order of parameters matches what is in the table. Any other suggestions I can try out ? Edit: Running the code like this works fine:
public void UpdateCustomer(CustomerInfo customer) { string query = "UPDATE CUSTOMER SET Name = " + "'" + customer.Name + "'," + "Age = " + customer.Age + " WHERE ID = " + customer.ID; OleDbCommand command = new OleDbCommand(); command.CommandText = query; command.Connection = this.connection; command.Connection.Open(); try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception ex) { throw new Exception(ex.Message, ex.InnerException); } finally { command.Connection.Close(); command.Dispose(); } } -
I am getting no error. The output of ExecuteNonQuery is an "int" type. The value of this int variable indicates how many rows have been modified. In my case its zero when infact it should be one. I have one row of data in the table that needs to be updated so it should return 1 not 0. I can verify that order of parameters matches what is in the table. Any other suggestions I can try out ? Edit: Running the code like this works fine:
public void UpdateCustomer(CustomerInfo customer) { string query = "UPDATE CUSTOMER SET Name = " + "'" + customer.Name + "'," + "Age = " + customer.Age + " WHERE ID = " + customer.ID; OleDbCommand command = new OleDbCommand(); command.CommandText = query; command.Connection = this.connection; command.Connection.Open(); try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception ex) { throw new Exception(ex.Message, ex.InnerException); } finally { command.Connection.Close(); command.Dispose(); } }UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID
private string PARAM_ID = "@ID";
private string PARAM_NAME = "@Name";
private string PARAM_AGE = "@Age";What i think you'll find is happening here is that the sql statement is being executed and is trying to set name = id, age = name where id = age. Awkward problem.
-
Hi there, Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update. I'd appreciate if someone could point out the mistake I have overlooked. Thanksprivate string PARAM_ID = "@ID"; private string PARAM_NAME = "@Name"; private string PARAM_AGE = "@Age"; private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb"; private OleDbConnection connection = new OleDbConnection(connectionstring); public void UpdateCustomer(CustomerInfo customer) { OleDbParameter[] parameters = new OleDbParameter[]{ new OleDbParameter(PARAM_ID,OleDbType.Integer), new OleDbParameter(PARAM_NAME,OleDbType.VarChar), new OleDbParameter(PARAM_AGE,OleDbType.Integer)}; parameters[0].Value = customer.ID; parameters[1].Value = customer.Name; parameters[2].Value = customer.Age; OleDbCommand command = new OleDbCommand(); command.Connection = this.connection; command.Connection.Open(); command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID"; for (int i = 0; i < parameters.Length; i++) { OleDbParameter parm = (OleDbParameter)parameters[i]; command.Parameters.Add(parm); } try { int x = command.ExecuteNonQuery(); Debug.WriteLine(x.ToString()); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } finally { command.Connection.Close(); command.Dispose(); } }edit: Added the connection string.Your problem is with the order of your parameters. System.Data.Oledb does not work like sqlclient (or Access internally) The parameter names are irrelevant, only order matters. change your parameters to: parameters[0].Value = customer.Name; parameters[1].Value = customer.Age; parameters[2].Value = customer.ID; So that they match the order of occurrence in the query.
-
Your problem is with the order of your parameters. System.Data.Oledb does not work like sqlclient (or Access internally) The parameter names are irrelevant, only order matters. change your parameters to: parameters[0].Value = customer.Name; parameters[1].Value = customer.Age; parameters[2].Value = customer.ID; So that they match the order of occurrence in the query.