Datagrid-Syntax error in UPDATE statement
-
Getting a syntax error when I try to run an update from my datagrid to update an access database. I can't figure out where it is. Im also getting the same error in my add row function. Some of the code is shown below: private void ExecuteNonQuery(string sql) { OleDbConnection conn = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (conn != null) conn.Close(); } } private void DiagnosisListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int DiagnosisNo = (int) DiagnosisListDataGrid.DataKeys[(int) e.Item.ItemIndex]; string nino = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string datetime = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string details = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string currentstatus = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string sql = "UPDATE DIAGNOSIS SET NINo=\"" + nino + "\", DateTime=\"" + datetime + "\", Details=\"" + details + "\", CurrentStatus=\"" + currentstatus + "\"" + " WHERE DiagnosisNo=" + DiagnosisNo; ExecuteNonQuery(sql); DiagnosisListDataGrid.EditItemIndex = -1; ReadRecords(); } private void btnAddDiagnosis_Click(object sender, System.EventArgs e) { string sql = "INSERT INTO DIAGNOSIS (NINo, DateTime, Details, CurrentStatus)" + " VALUES (\"new\", \"new\", \"new\", \"new\")"; ExecuteNonQuery(sql); ReadRecords(); } Can anyone spot a problem, I've been racking my brains but can't fgiure it out. Cheers for any help! Rory
-
Getting a syntax error when I try to run an update from my datagrid to update an access database. I can't figure out where it is. Im also getting the same error in my add row function. Some of the code is shown below: private void ExecuteNonQuery(string sql) { OleDbConnection conn = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (conn != null) conn.Close(); } } private void DiagnosisListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int DiagnosisNo = (int) DiagnosisListDataGrid.DataKeys[(int) e.Item.ItemIndex]; string nino = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string datetime = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string details = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string currentstatus = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string sql = "UPDATE DIAGNOSIS SET NINo=\"" + nino + "\", DateTime=\"" + datetime + "\", Details=\"" + details + "\", CurrentStatus=\"" + currentstatus + "\"" + " WHERE DiagnosisNo=" + DiagnosisNo; ExecuteNonQuery(sql); DiagnosisListDataGrid.EditItemIndex = -1; ReadRecords(); } private void btnAddDiagnosis_Click(object sender, System.EventArgs e) { string sql = "INSERT INTO DIAGNOSIS (NINo, DateTime, Details, CurrentStatus)" + " VALUES (\"new\", \"new\", \"new\", \"new\")"; ExecuteNonQuery(sql); ReadRecords(); } Can anyone spot a problem, I've been racking my brains but can't fgiure it out. Cheers for any help! Rory
I got a lot of errors like yours when I first started out. Someone tipped me off with "Paramterized Queries". It really helped and solved a lot of problems. Trust me! The keyword here is "Parameters". Here's a snipet from MS's OleDbDataAdapter.InsertCommand Property // Create the InsertCommand. cmd = new OleDbCommand("INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", conn); cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID"); cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName"); cmd.ExecuteNonQuery(); Note: You need to set the right Data Type for your parameter fields, specially with your DateTime field. Hope it works for you, Khang
-
I got a lot of errors like yours when I first started out. Someone tipped me off with "Paramterized Queries". It really helped and solved a lot of problems. Trust me! The keyword here is "Parameters". Here's a snipet from MS's OleDbDataAdapter.InsertCommand Property // Create the InsertCommand. cmd = new OleDbCommand("INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", conn); cmd.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID"); cmd.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName"); cmd.ExecuteNonQuery(); Note: You need to set the right Data Type for your parameter fields, specially with your DateTime field. Hope it works for you, Khang
When using this with my code i'm getting the folowing errors: C:\Inetpub\wwwroot\GP\AppointmentsList.aspx.cs(121): The name 'cmd' does not exist in the class or namespace 'GP.AppointmentsList' Where cmd is used. Would someone be able to tell me how to sort this out using the code I provided earlier? Appreciate the help - cheers. Rory
-
When using this with my code i'm getting the folowing errors: C:\Inetpub\wwwroot\GP\AppointmentsList.aspx.cs(121): The name 'cmd' does not exist in the class or namespace 'GP.AppointmentsList' Where cmd is used. Would someone be able to tell me how to sort this out using the code I provided earlier? Appreciate the help - cheers. Rory
-
I changed all cmd to OleDbCommand but they are still getting errors: 'System.Data.OleDb.OleDbCommand' denotes a 'class' where a 'variable' was expected An object reference is required for the nonstatic field, method, or property 'System.Data.OleDb.OleDbCommand.Parameters' Cheers for the suggestion, but no joy. Any other ideas??? Rory