Error In Update Procedure

[Guest]

I am able to get this section of code to properly open an Access database and fill the dataset from a table within it. Then after editing certain values in the dataset attempt to update I get an unhandled exception. After trapping the code in a try it returns "Syntax Error in UPDATE Statement" however I have tried all documents and syntax appears correct. I've verified that I am not using any reserved words for column names in the Access database. This problem is quite frustrating. Any help is appreciated. Here is my code

-----------------------------
Dim strConnect1 As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& "E:\Microsoft Visual Studio .NET\Projects\GrammarMachine\mpqgrammar.mdb
Dim cn1 As OleDbConnection = New OleDbConnection(strConnect1
cn1.Open(

Dim strSelectOut1 As String =
"SELECT * FROM MPQFIN WHERE CASENO = " & selI
Dim dsOutCmd As New OleDbDataAdapter(strSelectOut1, cn1
Dim dsOutCmdBuild As OleDbCommandBuilder = New OleDbCommandBuilder(dsOutCmd
Dim dsOut As New DataSet(
dsOutCmd.Fill(dsOut, "MPQFIN"

dsOut.Tables(0).Rows(0).Item("Done") =
dsOut.Tables(0).Rows(0).Item("Coder") = userNam

dsOut.Tables(0).Rows(0).Item("ACTOR") = RTBSpon.Tex
dsOut.Tables(0).Rows(0).Item("ACTION") = RTBAct.Tex
dsOut.Tables(0).Rows(0).Item("CONSEQ") = RTBCon.Tex
dsOut.Tables(0).Rows(0).Item("BACK") = RTBBack.Tex
dsOut.Tables(0).Rows(0).Item("DEFINE") = RTBDef.Tex

Tr
dsOutCmd.Update(dsOut, "MPQFIN"
Catch Ex As Exceptio
Console.WriteLine("Error: " & Ex.Message
MsgBox("Error: " & Ex.Message
End Try

 

[Guest]

I'd like to ask you for some more information.
Does the error occur every time or only with certain values?
Do any of the field values contain single or double quotes?
Have you tried your update after first setting SelectCommand.CommandText on the Data Adapter?

This posting is provided "AS IS" with no warranties, and confers no rights.

----- Valdacil wrote: -----

I am able to get this section of code to properly open an Access database and fill the dataset from a table within it. Then after editing certain values in the dataset attempt to update I get an unhandled exception. After trapping the code in a try it returns "Syntax Error in UPDATE Statement" however I have tried all documents and syntax appears correct. I've verified that I am not using any reserved words for column names in the Access database. This problem is quite frustrating. Any help is appreciated. Here is my code:

------------------------------
Dim strConnect1 As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "E:\Microsoft Visual Studio .NET\Projects\GrammarMachine\mpqgrammar.mdb"
Dim cn1 As OleDbConnection = New OleDbConnection(strConnect1)
cn1.Open()

Dim strSelectOut1 As String = _
"SELECT * FROM MPQFIN WHERE CASENO = " & selID
Dim dsOutCmd As New OleDbDataAdapter(strSelectOut1, cn1)
Dim dsOutCmdBuild As OleDbCommandBuilder = New OleDbCommandBuilder(dsOutCmd)
Dim dsOut As New DataSet()
dsOutCmd.Fill(dsOut, "MPQFIN")

dsOut.Tables(0).Rows(0).Item("Done") = 1
dsOut.Tables(0).Rows(0).Item("Coder") = userName


dsOut.Tables(0).Rows(0).Item("ACTOR") = RTBSpon.Text
dsOut.Tables(0).Rows(0).Item("ACTION") = RTBAct.Text
dsOut.Tables(0).Rows(0).Item("CONSEQ") = RTBCon.Text
dsOut.Tables(0).Rows(0).Item("BACK") = RTBBack.Text
dsOut.Tables(0).Rows(0).Item("DEFINE") = RTBDef.Text

Try
dsOutCmd.Update(dsOut, "MPQFIN")
Catch Ex As Exception
Console.WriteLine("Error: " & Ex.Message)
MsgBox("Error: " & Ex.Message)
End Try

 

[David Sceppa]

Good questions, John.

This error generally occurs when using column names that are reserved
words for the Jet provider, or that contain a space, punctuation mark, etc.
In such cases, the solution is to have the CommandBuilder delimit the
table and column names by setting the QuotePrefix and QuoteSuffix
properties. Your code would look like:

Dim dsOutCmdBuild As New OleDbCommandBuilder(dsOutCmd)
dsOutCmdBuild.QuotePrefix = "["
dsOutCmdBuild.QuoteSuffix = "]"

You might want to give this a try and see if it resolves your problem.
If not, please let us know.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.