Dynamically generated OLDEB procedures are throwing Syntax errors

Question

Hi Everyone:

I am using the OleDBCommandBuilder to dynamically generate the necessary SQL statements needed to update a table DataSet back to the MS-Access database table. Here is the Exception being thrown:

Title: Error
Message: Syntax error in UPDATE statement.
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(DataTable dataTable)
   	at Dyn_DE_Tool_v3.FrmMain.BN_OleDB_SaveItem_Click(Object sender, EventArgs e) in 
	C:\Users\Quecu\source\repos\Dyn_DE_Tool_v3\Dyn_DE_Tool_v3\frmMain.vb:line 84
Date/Time: 6/26/2023 11:34:12 AM

Here is the code I created to save changes back to the database:

Private Sub BN_OleDB_SaveItem_Click(ByVal sender As System.Object,
                                    ByVal e As System.EventArgs)
        'Purpose:   Saves changes made to a Data Table
        '           Back to the Access Database
        Dim strTab As String,
            strSQL As String,
            da As OleDbDataAdapter,
            cb As OleDbCommandBuilder,
            Msg As String
        strTab = tbctrlTblPages.SelectedTab.Name

        'Search the current TabPage for a BindingNavigator control
        For Each c In Me.tbctrlTblPages.Controls.Item(strTab).Controls
            Dim oNav As BindingNavigator = TryCast(c, BindingNavigator)
            'If the control is a BindingNavigator
            If oNav IsNot Nothing Then
                oNav.BindingSource.EndEdit()
                Exit For
            End If
        Next
        'Connect to the database
        Using objConn As New OleDbConnection(cnnOleDB)
            Try
                If objConn.State = ConnectionState.Open Then
                    objConn.Close()
                Else
                    objConn.Open()
                    strSQL = "SELECT * FROM " & strTab & ";"
                    da = New OleDbDataAdapter(strSQL, objConn)
                    'Build Commands for add, delete, and update
                    cb = New OleDbCommandBuilder(da)
                    'Update the selected table
                    da.Update(ds_OleDBTbls.Tables(strTab)) ‘Exception is thrown
                    'da.Update(strTab)
                    'Debug.Print("Rows = " & CStr(dt_Records.Rows.Count))
                    Msg = "The " & strTab & " table has been " &
                          "successfully updated."
                    MessageBox.Show(Msg)
                End If
            Catch ex As Exception
                'Log error 
                Dim el As New Log.ErrorLogger
                el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
            Finally
                objConn.Close()
            End Try
        End Using
    End Sub

All add, delete, and update procedures are dynamically created using the OleDBDataAdapter and OleDBCommandBuilder functions contained in the NameSpace System.Data.OleDb.

Two questions arise:

1.       How can there be a Syntax error in a command generated dynamically in the System.Data.OleDB namespace.

2.       How do I fix a dynamically generated procedures when I cannot see the procedures?

Thank you,

MRM256

Answer 1

Hi @Mark McCumber ,

If the table name or any of the field names happen to be reserved words in Access SQL, you need to use the following code to generate the UPDATE statement like this:

UPDATE [TableName] SET [ColumnName]= ...

Dim cb As New OleDb.OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.