Update a record in a database via a datagrid
-
I'm trying to make an update to a record in a MS Access database via a datagrid edit column. When i press the update button, an error occurs:
System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at RuamSiam.editsalerate.dgSaleRate_Update(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\ruamsiam\editsalerate.aspx.cs:line 187Here's the sql used for this update: (generated by my app)update salerate set cashprice = '29000',financeprice = '25000',downpayment = '1500',cost = '28900' where model = 'AN112A'The columns in the datagrid consist of the following:[0]: model: Button Column [1]: cashprice: Bound Column [2]: financeprice: Bound Column [3]: downpayment: Bound Column [4]: cost: Bound Column [5]: Edit,Update,Cancel: Button Column [6]: Delete: Button ColumnThe code i use for the update process is:TextBox cashprice,financeprice,downpayment,cost; string model = this.dgSaleRate.DataKeys[this.dgSaleRate.SelectedIndex].ToString(); cashprice = (TextBox)e.Item.Cells[1].Controls[0]; financeprice = (TextBox)e.Item.Cells[2].Controls[0]; downpayment = (TextBox)e.Item.Cells[3].Controls[0]; cost = (TextBox)e.Item.Cells[4].Controls[0]; string sqlUpdate = "update salerate set "; sqlUpdate += "cashprice = '" + cashprice.Text + "',"; sqlUpdate += "financeprice = '" + financeprice.Text + "',"; sqlUpdate += "downpayment = '" + downpayment.Text + "',"; sqlUpdate += "cost = '" + cost.Text + "'"; sqlUpdate += " where model = '" + model + "'"; try { if(conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; //the connection is connected correctly. cmd.CommandType = CommandType.Text; cmd.CommandText = sqlUpdate; cmd.ExecuteNonQuery(); } catch(Exception ex) { this.lblErrMsg.Text = ex.ToString(); this.lblErrMsg.Text += "<br><br>" + sqlUpdate; } finally { this. -
I'm trying to make an update to a record in a MS Access database via a datagrid edit column. When i press the update button, an error occurs:
System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at RuamSiam.editsalerate.dgSaleRate_Update(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\ruamsiam\editsalerate.aspx.cs:line 187Here's the sql used for this update: (generated by my app)update salerate set cashprice = '29000',financeprice = '25000',downpayment = '1500',cost = '28900' where model = 'AN112A'The columns in the datagrid consist of the following:[0]: model: Button Column [1]: cashprice: Bound Column [2]: financeprice: Bound Column [3]: downpayment: Bound Column [4]: cost: Bound Column [5]: Edit,Update,Cancel: Button Column [6]: Delete: Button ColumnThe code i use for the update process is:TextBox cashprice,financeprice,downpayment,cost; string model = this.dgSaleRate.DataKeys[this.dgSaleRate.SelectedIndex].ToString(); cashprice = (TextBox)e.Item.Cells[1].Controls[0]; financeprice = (TextBox)e.Item.Cells[2].Controls[0]; downpayment = (TextBox)e.Item.Cells[3].Controls[0]; cost = (TextBox)e.Item.Cells[4].Controls[0]; string sqlUpdate = "update salerate set "; sqlUpdate += "cashprice = '" + cashprice.Text + "',"; sqlUpdate += "financeprice = '" + financeprice.Text + "',"; sqlUpdate += "downpayment = '" + downpayment.Text + "',"; sqlUpdate += "cost = '" + cost.Text + "'"; sqlUpdate += " where model = '" + model + "'"; try { if(conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; //the connection is connected correctly. cmd.CommandType = CommandType.Text; cmd.CommandText = sqlUpdate; cmd.ExecuteNonQuery(); } catch(Exception ex) { this.lblErrMsg.Text = ex.ToString(); this.lblErrMsg.Text += "<br><br>" + sqlUpdate; } finally { this. -
At first glance I feel there datatype mismatch happening, Closely watch the values passed and datatypes may be you will be able to trace out Padvit
I've tried changing the datatype but it still doesn't work and yields the same error. Thank you so much anyway. KiT
-
I'm trying to make an update to a record in a MS Access database via a datagrid edit column. When i press the update button, an error occurs:
System.Data.OleDb.OleDbException: Operation must use an updateable query. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at RuamSiam.editsalerate.dgSaleRate_Update(Object sender, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\ruamsiam\editsalerate.aspx.cs:line 187Here's the sql used for this update: (generated by my app)update salerate set cashprice = '29000',financeprice = '25000',downpayment = '1500',cost = '28900' where model = 'AN112A'The columns in the datagrid consist of the following:[0]: model: Button Column [1]: cashprice: Bound Column [2]: financeprice: Bound Column [3]: downpayment: Bound Column [4]: cost: Bound Column [5]: Edit,Update,Cancel: Button Column [6]: Delete: Button ColumnThe code i use for the update process is:TextBox cashprice,financeprice,downpayment,cost; string model = this.dgSaleRate.DataKeys[this.dgSaleRate.SelectedIndex].ToString(); cashprice = (TextBox)e.Item.Cells[1].Controls[0]; financeprice = (TextBox)e.Item.Cells[2].Controls[0]; downpayment = (TextBox)e.Item.Cells[3].Controls[0]; cost = (TextBox)e.Item.Cells[4].Controls[0]; string sqlUpdate = "update salerate set "; sqlUpdate += "cashprice = '" + cashprice.Text + "',"; sqlUpdate += "financeprice = '" + financeprice.Text + "',"; sqlUpdate += "downpayment = '" + downpayment.Text + "',"; sqlUpdate += "cost = '" + cost.Text + "'"; sqlUpdate += " where model = '" + model + "'"; try { if(conn.State == ConnectionState.Open) { conn.Close(); } conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; //the connection is connected correctly. cmd.CommandType = CommandType.Text; cmd.CommandText = sqlUpdate; cmd.ExecuteNonQuery(); } catch(Exception ex) { this.lblErrMsg.Text = ex.ToString(); this.lblErrMsg.Text += "<br><br>" + sqlUpdate; } finally { this. -
Hi there, Make sure the ASP.NET process identity has the modify permission to the folder containing the database file .mdb
Oops... How could i forget such a simple thing...:-D silly me. It's working now. Thank you so much. KiT
-
Oops... How could i forget such a simple thing...:-D silly me. It's working now. Thank you so much. KiT