Dataadapter.update Syntax Error
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why does this C# code generate a Syntax Error when I call DataAdapter.Update()? up vote 0 down vote favorite I am trying to add a new row into an existing MS Access database table in C#. However, when I make the call to the DataAdapter’s Update method I get a “Syntax error in INSERT INTO statement”. My code is: try { // Open the database OleDbCommand mySelectCommand = new OleDbCommand(strAccessSelect, myAccessConn); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(mySelectCommand); OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myDataAdapter); myAccessConn.Open(); // Get Analogue table int rowCount = myDataAdapter.Fill(myDataSet, "Analogue"); Console.WriteLine("Row Count: " + rowCount); DataTable analogues = myDataSet.Tables["Analogue"]; analogues.PrimaryKey = new DataColumn[] { analogues.Columns["Name"] }; // Create a new row. System.Data.DataRow newRow; newRow = analogues.NewRow(); newRow["Name"] = "My new row"; newRow["Area"] = "System"; newRow["Description"] = "My new row, created by me!"; analogues.Rows.Add(newRow); myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(); myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(); Console.WriteLine("Insert Command: " + myDataAdapter.InsertCommand.ToString()); Console.WriteLine("Update Command: " + myDataAdapter.UpdateCommand.ToString()); myDataAdapter.Update(myDataSet, "Analogue"); } catch (Exception ex) { Console.WriteLine("Error: Failed to manipulate the DataBase.\n{0}", ex.ToString()); return; } finally { myAccessConn.Close(); } I have removed most of the columns from the table for brevity, but all of the column headers are in quotes, none of them contain any spaces and none are reserved words. None of the ones I have removed are Access keywords, but I note that “NAME” and “Description” are. I don’t believe this is causing my problem because I have been able to modify the text in the “Description” column in an existing row. The Command Builder is generating the following for me which doesn’t seem quite right: System.Data.OleDb.OleDbCommand Do I need
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the http://stackoverflow.com/questions/21575541/why-does-this-c-sharp-code-generate-a-syntax-error-when-i-call-dataadapter-updat Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up C# - “Syntax error in update statement” when trying to update the access database using datagridview (also using OleDbCommandBuilder) up vote 1 http://stackoverflow.com/questions/28302944/c-sharp-syntax-error-in-update-statement-when-trying-to-update-the-access-da down vote favorite I am displaying a data in datagridview and I am trying to update the database from datagridview. So I am using OleDbCommandBuilder to generate the update command. I get "Syntax error in update statement" when clicking on update button. Here is my code: private void listBox9_SelectedValueChanged(object sender, EventArgs e) { AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)); connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\\Trip Sheet Management System\\WABCO.mdb"); sql = "SELECT ID,[TRIP COST] FROM TMSDETAILS"; dataAdapter = new OleDbDataAdapter(sql, connection); dataTable = new DataTable(); bindingSource = new BindingSource(); connection.Open(); dataAdapter.Fill(dataTable); bindingSource.DataSource = dataTable; dataGridView1.DataSource = bindingSource; connection.Close(); } private void button8_Click(object sender, EventArgs e) { commandBuilder = new OleDbCommandBuilder(dataAdapter); try { dataAdapter.Update(dataTable); } catch (Exception ex) { MessageBox.Show(ex.Message); } } When I click on update button, I get error in MessageBox saying "Syntax error in update statement" Field 1: ID - Autonumber, primary key Field 2: TRIP COST - Number c
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine https://social.msdn.microsoft.com/Forums/en-US/317a4bbf-d47d-4778-adf4-30f20a55fe9d/keep-getting-the-error-syntax-error-in-update-statement?forum=Vsexpressvb Microsoft Student Partners ISV Startups TechRewards Events Community Magazine http://www.vbforums.com/showthread.php?463351-RESOLVED-Getting-Error-quot-Syntax-error-in-INSERT-INTO-Statement-quot Forums Blogs Channel 9 Documentation APIs and reference Dev centers Retired content Samples We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Ask a question Quick access Forums home Browse dataadapter.update syntax forums users FAQ Search related threads Remove From My Forums Answered by: Keep getting the error "Syntax Error in UPDATE Statement" Archived Forums V > Visual Basic Express Edition Question 0 Sign in to vote I've worked with the older versions of Visual Basic and since dataadapter.update syntax error moving to 2005 express I think I've gone a bit more crazy. I keep getting this error when the dataapdater.update statement is called. Here is the code that I am using and I've also thrown in the information from the syntax error. Private Sub EditMaterial(ByVal codepos As Integer) Dim con As New OleDb.OleDbConnection Dim updateCMD As OleDbCommand Dim Sql As String, updateSQL As String, idtxt As String con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data" & _ Source = C:\tmp\chemical.mdb" con.Open() Sql = "SELECT * FROM tblmasterpricesheet" da = New OleDb.OleDbDataAdapter(Sql, con) da.Fill(ds, "Code") idtxt = ds.Tables("code").Rows(codepos).Item(0) updateSQL = "UPDATE tblMasterPriceSheet" & _ "SET [ID] = ?, [Code] = ?, [Material] = ?, [Density] = ?," & _ "[CAS #] = ?, [Cost per pound] =
in INSERT INTO Statement" If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 8 of 8 Thread: [RESOLVED] Getting Error - "Syntax error in INSERT INTO Statement" Tweet Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Apr 14th, 2007,09:57 PM #1 KShum View Profile View Forum Posts Thread Starter Member Join Date Dec 2006 Location Upstate NY Posts 37 [RESOLVED] Getting Error - "Syntax error in INSERT INTO Statement" I am trying to add a new row to an Access Database table called tblSafeDep. Below is my code: Code: ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Apps\SafeDeposit\SafeDepMaint.mdb Connection1 = New OleDb.OleDbConnection(ConnectionString) Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM tblSafeDep", Connection1) Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter() da.SelectCommand = cmd Dim cb As New OleDb.OleDbCommandBuilder(da) Dim ds As DataSet = New DataSet("tblSafeDep") Try Connection1.Open() da.Fill(ds, "tblSafeDep") Connection1.Close() Catch Ex As Exception MsgBox(Ex.Message) Console.WriteLine(Ex.Message) End Try .... do some processing ..... Try Dim NewRow As DataRow = ds.Tables("tblSafeDep").NewRow Dim countr As Integer = ds.Tables("tblSafeDep").Rows.Count NewRow.Item("ID") = countr + 1 NewRow.Item("RptDt") = sRptDate NewRow.Item("BoxNo") = sBoxNo NewRow.Item("OpenBr") = sOpBr NewRow.Item("ActBr") = sActBr NewRow.Item("size") = sSize NewRow.Item("teller") = sTeller NewRow.Item("CustName") = sCustName NewRow.Item("dlfm") = sDLFM ds.Tables("tblSafeDep").Rows.Add(NewRow) da.Update(ds, "tblsafedep") Catch ex As Exception MessageBox.Show(ex.ToString, "Error Encountered") End Try When it tries to do the update, I get an error message "Syntax error in INSERT INTO Statement." Can anyone see what I am doing wrong? Reply With Quote Apr 15th, 2007,03:53 AM #2 Shardox View Profile View Forum Posts Lively Member