locked
Keep getting the error "Syntax Error in UPDATE Statement"

    Question

  • I've worked with the older versions of Visual Basic and since 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] = ?," & _

                 "[Cost per gallon] = ?, [Pounds per drum] = ? " & _

                 "WHERE ID = ?"

     

            updateCMD = New OleDbCommand(updateSQL, con)

     

            updateCMD.Parameters.Add("@[ID]", OleDbType.Double, 3,

                                     "[ID]")

            updateCMD.Parameters.Add("@[Code]", OleDbType.VarChar, 5,

                                     "[Code]")

            updateCMD.Parameters.Add("@[Material]", OleDbType.VarChar, 50,

                                     "[Material]")

            updateCMD.Parameters.Add("@[Density]", OleDbType.Double, 10,

                                     "[Density]")

            updateCMD.Parameters.Add("@[CAS #]", OleDbType.VarChar, 50,

                                     "[CAS #]")

            updateCMD.Parameters.Add("@[Cost per pound]", OleDbType.Double,

                                      10, "[Cost per pound]")

            updateCMD.Parameters.Add("@[Cost per gallon]", OleDbType.Double,

                                      10, "[Cost per gallon]")

            updateCMD.Parameters.Add("@[Pounds per drum]", OleDbType.Double,

                                      10, "[Pounds per drum]")

     

            da.UpdateCommand = updateCMD

     

            ds.Tables("code").Rows(codepos).BeginEdit()

            With ds.Tables("code").Rows(codepos)

                .Item(0) = Convert.ToDouble(idtxt)

                .Item(1) = Convert.ToString(cmbCode.Text)

                .Item(2) = Convert.ToString(cmbMat.Text)

                .Item(3) = Convert.ToDouble(txtDen.Text)

                .Item(4) = Convert.ToString(txtCAS.Text)

                .Item(5) = Convert.ToDouble(txtCostLB.Text)

                .Item(6) = Convert.ToDouble(txtCostGal.Text)

                .Item(7) = Convert.ToDouble(txtLBGal.Text)

            End With

            ds.Tables("code").Rows(codepos).EndEdit()

     

            Try

                da.Update(ds, "code")

     

            Catch ex As System.InvalidOperationException

     

                MessageBox.Show(ex.Message)

     

            End Try

     

            con.Close()

     

        End Sub

     

     

    System.Data.OleDb.OleDbException was unhandled

      ErrorCode=-2147217900

      Message="Syntax error in UPDATE statement."

      Source="Microsoft JET Database Engine"

      StackTrace:

           at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

           at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

           at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

           at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)

           at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

           at dbtest.form1.EditMaterial(Int32 codepos) in C:\DanlinFormulation\may25\testdb\Form1.vb:line 194

           at dbtest.form1.btnupdate_click(Object sender, EventArgs e) in C:\DanlinFormulation\may25\testdb\Form1.vb:line 114

           at System.Windows.Forms.Control.OnClick(EventArgs e)

           at System.Windows.Forms.Button.OnClick(EventArgs e)

           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

           at System.Windows.Forms.Control.WndProc(Message& m)

           at System.Windows.Forms.ButtonBase.WndProc(Message& m)

           at System.Windows.Forms.Button.WndProc(Message& m)

           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

           at System.Windows.Forms.Application.Run(ApplicationContext context)

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

           at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

           at dbtest.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

    Sorry for the length but I wanted to make sure there was enought information to hopefully resolve my problem. I have yet to ever get the update command to work successfully to update my database so I hope someone can help me find a solution.

    Thanks in advance.

    Sunday, May 28, 2006 1:05 PM

Answers

  • well, between last night and this morning, I found the solution.

    First I had to read this article over in MSDN: http://msdn2.microsoft.com/en-us/z92w563z(VS.80).aspx

    Then I actually applied it, here is the code I ended up with:

    Private Sub EditMaterial(ByVal codepos As Integer)

    Dim myDataRow As DataRow

    OleDbDataAdapter1.Fill(myDataSet1, "code")

    myDataRow = MyDataSet1.Tables("code").Rows(codepos)

    myDataRow("Density") = Convert.ToDouble(txtDen.Text)

    MyDataSet1.GetChanges()

    Try

    OleDbDataAdapter1.Update(MyDataSet1, "code")

    Catch ex As System.InvalidOperationException

    MessageBox.Show(ex.Message)

    End Try

    End Sub

    I was very surprised that my origanl code was so dang long. All I did was add the Oledbdataadapter, Oledbdataconnection and a dataset to my tools. I pulled the Oledbdataadapter over onto my form and it walked me through the setup for the connection and the select command. It did generate the correct Updatecommand. All I had to do was use the Oledbdataadapter to configure the dataset, set a few properties in the database (mainly setting it to save on change) and set the dataadpater to bring over the key as it fills the dataset.

    It worked, and I'm absolutly amazed by this. I guess my mistake was trying to hard code all of it through the data sources. Believe me, I'll never do that again.

    Thanks guys for all the help, hope your here again when or if I run into a problem.

    Thursday, June 01, 2006 12:10 AM

All replies

  • hi,

    i'm not sure but have you tried to write the  paramter in the update instead of ? like for example

    updateSQL = "UPDATE    tblMasterPriceSheet" & _

                 "SET [ID] = @ID,

    also did you try to remove the brackets from the sourcecolumn like

    updateCMD.Parameters.Add("@ID", OleDbType.Double, 3,

                                     "ID")

    hope this helps

     

    Sunday, May 28, 2006 1:26 PM
  • I can try it with the @ instead of the ? but while reading on data accessing I found a piece that mentioned since I'm accessing a Access database you use the ? in the SQL command unlike the @ which is used for SQL Databases.

    Also I used the query builder in VB2005 to build the query and then just adjusted it for my use. When it was built the builder had used ? as well.

    As far as using brackets in the parameter statements, I could be wrong on this but I was using them to try and avoid possably using a VB keyword that I have also used in my database table. Supposedly useing the brackets will keep errors like this from happening.

    I will try your suggestion and let you know as soon as I can get back to my code. Thanks for the suggestions.

    Monday, May 29, 2006 12:52 AM
  • ok,

    plz if this worked let me know, here its other thread was discussing something close to this

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=414456&SiteID=1

    hope this helps

    Monday, May 29, 2006 1:16 AM
  • Nah, it didn't quite work though instead of syntax error I was getting the dreaded (though any error message is dreaded) error about not have a updatecommand.

    I may be on the right track but I have this feeling that I am missing something either in the way I pass information from my form to the dataset and then to the dataadapter, or my SQL is not quite correct.

    I have tried to reading throught the tutorials and the MSDN articles on doing database through Visual Basic but I just get this feeling that there is something I'm missing.

    If anyone has any suggestion or can point me to a good tutorial or article that explains database access through visual basic with regards to OLEDB I would great appreciate it.

     

    Thanks.

    Tuesday, May 30, 2006 2:23 AM
  • hi,

    you can try this

    featching data into your app >> http://msdn2.microsoft.com/en-us/library/ms171918(VS.80).aspx

    ADO.net Quick start >>> http://www.asp.net/QuickStart/howto/doc/adoplus/overviewcontents.aspx

    hope this helps

    Tuesday, May 30, 2006 2:57 AM
  • If your table has a primary key, you could try using a CommandBuilder on your DataAdapter. If that works, you can examine the generated UpdateText to get the proper syntax.

    --Mable

    Tuesday, May 30, 2006 3:01 AM
  • Okay time for an update. This morning has been very busy and I was finally able to sit down and look at the code once more.

    Shakalama the ADO.net quick start has probably been the most helpful guide I have seen so far and in it I have discovered a few things I didn't know.

    The two lines below I had added to my code:

    Dim daCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da)
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey

    the interesting part is where they have to be located. they are before the da.fill(ds,"code) line but after the assinging the datadapter the select string and connection.

    I played around some with the commandbuilder line and discovered that where it's located at is very important. place it in the wrong postion (like at the very top of the procedure) and the updatecommand as well as the other commands are not generated and the infomous error line "Update requires a valid UpdateCommand when passed DataRow collection with modified rows" is generated.

    So Mable, to answer your questions, yes I do have a primary key set and that is why the missingschemaaction line was inserted. After discovering that the primary key does not get sent over with the database like it should, I was missing it.

    Now, I would think that this has solved my problem, but in fact it just made a whole new one. The commandbuilder is generating a bad updatecommand query and I can't seem to figure a way to view the whole updatecommand. The portion I can get reads from the syntax error and the line is given as:

     "Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Code IS NULL) OR (Code = ?)) AND ((? = 1 AND Material IS NULL) OR (Material = ?)) AND ((? = 1 AND Density IS NULL) OR (Density = ?)) AND ((? = 1 AND CAS # IS NULL) OR (CAS # = ?)) AND ((? = 1 AND Cost per pound IS NULL) OR (Cost '."

    I need to find a way to see the whole query that the updatecommand has that is set by the commandbuilder. I have tried using a line such as:

            Sql = daCommandBuilder.DataAdapter.UpdateCommand.CommandText

    but this line is not correct. I need to find a way to display the whole updatecommand text.

    So once again, I'm at a stand still in trying to find a solution.

    Thanks again.
    Tuesday, May 30, 2006 8:20 PM
  • Sorry this took so long, but I finally found the code where I made this work.

    Instead of examining the DataAdapter.UpdateCommand, you must look at

    Commandbuilder.GetUpdateCommand().CommandText

    Apparently, the builder does not set the data adapter's UpdateCommand, but keeps track of its own.

    Hope this helps,

    --Mable

    Wednesday, May 31, 2006 8:30 PM
  • well, between last night and this morning, I found the solution.

    First I had to read this article over in MSDN: http://msdn2.microsoft.com/en-us/z92w563z(VS.80).aspx

    Then I actually applied it, here is the code I ended up with:

    Private Sub EditMaterial(ByVal codepos As Integer)

    Dim myDataRow As DataRow

    OleDbDataAdapter1.Fill(myDataSet1, "code")

    myDataRow = MyDataSet1.Tables("code").Rows(codepos)

    myDataRow("Density") = Convert.ToDouble(txtDen.Text)

    MyDataSet1.GetChanges()

    Try

    OleDbDataAdapter1.Update(MyDataSet1, "code")

    Catch ex As System.InvalidOperationException

    MessageBox.Show(ex.Message)

    End Try

    End Sub

    I was very surprised that my origanl code was so dang long. All I did was add the Oledbdataadapter, Oledbdataconnection and a dataset to my tools. I pulled the Oledbdataadapter over onto my form and it walked me through the setup for the connection and the select command. It did generate the correct Updatecommand. All I had to do was use the Oledbdataadapter to configure the dataset, set a few properties in the database (mainly setting it to save on change) and set the dataadpater to bring over the key as it fills the dataset.

    It worked, and I'm absolutly amazed by this. I guess my mistake was trying to hard code all of it through the data sources. Believe me, I'll never do that again.

    Thanks guys for all the help, hope your here again when or if I run into a problem.

    Thursday, June 01, 2006 12:10 AM