Keep getting the error "Syntax Error in UPDATE Statement"

Keep getting the error "Syntax Error in UPDATE Statement"

• Ikbal

  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

  
  

• David Hatherly

  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

  
  
  

• Vinayak Kamat

  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

  
  
  

• Steve_King

  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

  
  
  

• Arix

  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.

  
  

• S.Guhananth

  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.
  
  

• DeltaWind

  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.

  
  

• ArtNJ

  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

  
  

• krisb

  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.

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