G
Guest
I am getting "Syntax error in UPDATE statement" if one of the fields in a
table of Access database (2000, or 97) has the name "Upper" or "Lower".
As soon as I change the name of the field to something else, the code works
fine.
I looked through the list of reserved names, and I could not find the words
"Upper" and "Lower".
In order to reproduce this behavior, please create a database "Lookup.mdb"
with one table "Test" which contains 2 fields of type "Text".
The name of one of the fields should be "Upper", or "Lower". Populate the
table with any entries you want.
After that create a Windows Forms app and add a Windows Form. Use the code
of the Windows Forms class Update1.vb below. Change the constant
LOOKUP_DBPATH appropriately.
Run the app. Click the button "Get Lookup Test". The datagrid should be
populated with values from table "Test". Edit the content of a cell under the
filed "Upper" or "Lower". Click the button "Update Lookup Test". An exception
should be raised.
You may want to set breakpoints in the routines "OnRowUpdating" and
"OnRowUpdated".
The environment is: Visual Studio 2003; Windows XP Prof; Windows Forms App;
Microsoft Access 2000 database.
=================================
The code:
#Region " Imports "
Imports System
Imports System.Data
Imports System.Data.OleDb
#End Region
Public Class Update1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents grdLookup As System.Windows.Forms.DataGrid
Friend WithEvents btnGetLookup As System.Windows.Forms.Button
Friend WithEvents btnUpdateLookup As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.grdLookup = New System.Windows.Forms.DataGrid
Me.btnGetLookup = New System.Windows.Forms.Button
Me.btnUpdateLookup = New System.Windows.Forms.Button
CType(Me.grdLookup,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'grdLookup
'
Me.grdLookup.DataMember = ""
Me.grdLookup.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.grdLookup.Location = New System.Drawing.Point(6, 8)
Me.grdLookup.Name = "grdLookup"
Me.grdLookup.Size = New System.Drawing.Size(346, 166)
Me.grdLookup.TabIndex = 0
'
'btnGetLookup
'
Me.btnGetLookup.Location = New System.Drawing.Point(370, 10)
Me.btnGetLookup.Name = "btnGetLookup"
Me.btnGetLookup.Size = New System.Drawing.Size(114, 22)
Me.btnGetLookup.TabIndex = 1
Me.btnGetLookup.Text = "Get Lookup Test"
'
'btnUpdateLookup
'
Me.btnUpdateLookup.Location = New System.Drawing.Point(370, 40)
Me.btnUpdateLookup.Name = "btnUpdateLookup"
Me.btnUpdateLookup.Size = New System.Drawing.Size(114, 22)
Me.btnUpdateLookup.TabIndex = 2
Me.btnUpdateLookup.Text = "Update Lookup Test"
'
'Update1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(492, 181)
Me.Controls.Add(Me.btnUpdateLookup)
Me.Controls.Add(Me.btnGetLookup)
Me.Controls.Add(Me.grdLookup)
Me.Name = "Update1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "Update1"
CType(Me.grdLookup,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
<STAThread()> Public Shared Sub Main()
Application.Run(New Update1)
End Sub
Private m_Conn As OleDbConnection
Private m_openedConnection As Boolean
Private m_sql As String
Private m_adap As OleDbDataAdapter
Private m_build As OleDbCommandBuilder
Private m_dt As DataTable
Const conAccess As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;"
Const conAccess1 As String = "Data Source="
Const LOCALDB_PATH = "E:\SMW\TestUpdates\Lookup.mdb"
Private Sub Update1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
m_openedConnection = OpenConnection(m_Conn)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub Update1_Closing(ByVal sender As Object, ByVal e As
System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
Try
Call CloseConnection(m_Conn)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub btnGetLookup_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetLookup.Click
Dim gotLookup As Boolean
Try
m_sql = "SELECT * FROM Test"
m_dt = New DataTable
m_dt.TableName = "Test"
gotLookup = GetDataTableForEditAndUpdate(m_sql, m_Conn, m_adap,
m_dt)
grdLookup.Text = "Test"
grdLookup.DataSource = m_dt
AddHandler m_adap.RowUpdating, _
New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
AddHandler m_adap.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub btnUpdateLookup_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdateLookup.Click
Dim updated As Boolean
Try
updated = UpdateLookupTableBackToLocalDB()
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Public Function OpenConnection(ByRef conn As OleDbConnection) As Boolean
Try
If conn Is Nothing Then
conn = New OleDbConnection
conn.ConnectionString = conAccess & conAccess1 & LOCALDB_PATH
End If
Catch ex As Exception
OpenConnection = False
Debug.WriteLine(ex.Message)
Exit Function
End Try
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
OpenConnection = True
Catch ex As OleDbException
Debug.WriteLine(ex.Message)
OpenConnection = False
End Try
End Function
Public Sub CloseConnection(ByRef conn As OleDbConnection)
Try
If Not conn Is Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
conn.Dispose()
End If
End If
Catch ex As Exception
Debug.WriteLine(ex.Message)
Finally
conn = Nothing
End Try
End Sub
Public Function GetDataTableForEditAndUpdate(ByVal sql As String, _
ByVal conn As
OleDbConnection, _
ByRef adap As
OleDbDataAdapter, _
ByRef dt As DataTable) As
Boolean
Try
adap = New OleDbDataAdapter(sql, conn)
m_build = New OleDbCommandBuilder(adap)
adap.FillSchema(dt, SchemaType.Source)
adap.Fill(dt)
GetDataTableForEditAndUpdate = True
Catch ex As Exception
Debug.WriteLine(ex.Message)
GetDataTableForEditAndUpdate = False
End Try
End Function
Private Function UpdateLookupTableBackToLocalDB() As Boolean
Try
Me.Cursor = Cursors.WaitCursor
With m_adap
.Update(m_dt.Select(Nothing, Nothing,
DataViewRowState.Deleted))
.Update(m_dt.Select(Nothing, Nothing,
DataViewRowState.ModifiedCurrent))
.Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Added))
End With
UpdateLookupTableBackToLocalDB = True
Catch ex As Exception
Debug.WriteLine(ex.Message)
UpdateLookupTableBackToLocalDB = False
Finally
Me.Cursor = Cursors.Default
End Try
End Function
Protected Sub OnRowUpdating(ByVal sender As Object, _
ByVal args As OleDbRowUpdatingEventArgs)
Debug.WriteLine(args.Command.CommandText)
If args.Errors Is Nothing Then
'
Else
Debug.WriteLine(args.Errors.Message)
Debug.WriteLine(args.Errors.Source)
End If
Debug.WriteLine(args.Status.ToString)
End Sub
Protected Sub OnRowUpdated(ByVal sender As Object, _
ByVal args As OleDbRowUpdatedEventArgs)
Debug.WriteLine(args.Command.CommandText)
If args.Errors Is Nothing Then
'
Else
Debug.WriteLine(args.Errors.Message)
Debug.WriteLine(args.Errors.Source)
End If
Debug.WriteLine(args.Status.ToString)
End Sub
End Class
table of Access database (2000, or 97) has the name "Upper" or "Lower".
As soon as I change the name of the field to something else, the code works
fine.
I looked through the list of reserved names, and I could not find the words
"Upper" and "Lower".
In order to reproduce this behavior, please create a database "Lookup.mdb"
with one table "Test" which contains 2 fields of type "Text".
The name of one of the fields should be "Upper", or "Lower". Populate the
table with any entries you want.
After that create a Windows Forms app and add a Windows Form. Use the code
of the Windows Forms class Update1.vb below. Change the constant
LOOKUP_DBPATH appropriately.
Run the app. Click the button "Get Lookup Test". The datagrid should be
populated with values from table "Test". Edit the content of a cell under the
filed "Upper" or "Lower". Click the button "Update Lookup Test". An exception
should be raised.
You may want to set breakpoints in the routines "OnRowUpdating" and
"OnRowUpdated".
The environment is: Visual Studio 2003; Windows XP Prof; Windows Forms App;
Microsoft Access 2000 database.
=================================
The code:
#Region " Imports "
Imports System
Imports System.Data
Imports System.Data.OleDb
#End Region
Public Class Update1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents grdLookup As System.Windows.Forms.DataGrid
Friend WithEvents btnGetLookup As System.Windows.Forms.Button
Friend WithEvents btnUpdateLookup As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.grdLookup = New System.Windows.Forms.DataGrid
Me.btnGetLookup = New System.Windows.Forms.Button
Me.btnUpdateLookup = New System.Windows.Forms.Button
CType(Me.grdLookup,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'grdLookup
'
Me.grdLookup.DataMember = ""
Me.grdLookup.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.grdLookup.Location = New System.Drawing.Point(6, 8)
Me.grdLookup.Name = "grdLookup"
Me.grdLookup.Size = New System.Drawing.Size(346, 166)
Me.grdLookup.TabIndex = 0
'
'btnGetLookup
'
Me.btnGetLookup.Location = New System.Drawing.Point(370, 10)
Me.btnGetLookup.Name = "btnGetLookup"
Me.btnGetLookup.Size = New System.Drawing.Size(114, 22)
Me.btnGetLookup.TabIndex = 1
Me.btnGetLookup.Text = "Get Lookup Test"
'
'btnUpdateLookup
'
Me.btnUpdateLookup.Location = New System.Drawing.Point(370, 40)
Me.btnUpdateLookup.Name = "btnUpdateLookup"
Me.btnUpdateLookup.Size = New System.Drawing.Size(114, 22)
Me.btnUpdateLookup.TabIndex = 2
Me.btnUpdateLookup.Text = "Update Lookup Test"
'
'Update1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(492, 181)
Me.Controls.Add(Me.btnUpdateLookup)
Me.Controls.Add(Me.btnGetLookup)
Me.Controls.Add(Me.grdLookup)
Me.Name = "Update1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "Update1"
CType(Me.grdLookup,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
<STAThread()> Public Shared Sub Main()
Application.Run(New Update1)
End Sub
Private m_Conn As OleDbConnection
Private m_openedConnection As Boolean
Private m_sql As String
Private m_adap As OleDbDataAdapter
Private m_build As OleDbCommandBuilder
Private m_dt As DataTable
Const conAccess As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;"
Const conAccess1 As String = "Data Source="
Const LOCALDB_PATH = "E:\SMW\TestUpdates\Lookup.mdb"
Private Sub Update1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
m_openedConnection = OpenConnection(m_Conn)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub Update1_Closing(ByVal sender As Object, ByVal e As
System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
Try
Call CloseConnection(m_Conn)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub btnGetLookup_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetLookup.Click
Dim gotLookup As Boolean
Try
m_sql = "SELECT * FROM Test"
m_dt = New DataTable
m_dt.TableName = "Test"
gotLookup = GetDataTableForEditAndUpdate(m_sql, m_Conn, m_adap,
m_dt)
grdLookup.Text = "Test"
grdLookup.DataSource = m_dt
AddHandler m_adap.RowUpdating, _
New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
AddHandler m_adap.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Private Sub btnUpdateLookup_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdateLookup.Click
Dim updated As Boolean
Try
updated = UpdateLookupTableBackToLocalDB()
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
End Sub
Public Function OpenConnection(ByRef conn As OleDbConnection) As Boolean
Try
If conn Is Nothing Then
conn = New OleDbConnection
conn.ConnectionString = conAccess & conAccess1 & LOCALDB_PATH
End If
Catch ex As Exception
OpenConnection = False
Debug.WriteLine(ex.Message)
Exit Function
End Try
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
OpenConnection = True
Catch ex As OleDbException
Debug.WriteLine(ex.Message)
OpenConnection = False
End Try
End Function
Public Sub CloseConnection(ByRef conn As OleDbConnection)
Try
If Not conn Is Nothing Then
If conn.State = ConnectionState.Open Then
conn.Close()
conn.Dispose()
End If
End If
Catch ex As Exception
Debug.WriteLine(ex.Message)
Finally
conn = Nothing
End Try
End Sub
Public Function GetDataTableForEditAndUpdate(ByVal sql As String, _
ByVal conn As
OleDbConnection, _
ByRef adap As
OleDbDataAdapter, _
ByRef dt As DataTable) As
Boolean
Try
adap = New OleDbDataAdapter(sql, conn)
m_build = New OleDbCommandBuilder(adap)
adap.FillSchema(dt, SchemaType.Source)
adap.Fill(dt)
GetDataTableForEditAndUpdate = True
Catch ex As Exception
Debug.WriteLine(ex.Message)
GetDataTableForEditAndUpdate = False
End Try
End Function
Private Function UpdateLookupTableBackToLocalDB() As Boolean
Try
Me.Cursor = Cursors.WaitCursor
With m_adap
.Update(m_dt.Select(Nothing, Nothing,
DataViewRowState.Deleted))
.Update(m_dt.Select(Nothing, Nothing,
DataViewRowState.ModifiedCurrent))
.Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Added))
End With
UpdateLookupTableBackToLocalDB = True
Catch ex As Exception
Debug.WriteLine(ex.Message)
UpdateLookupTableBackToLocalDB = False
Finally
Me.Cursor = Cursors.Default
End Try
End Function
Protected Sub OnRowUpdating(ByVal sender As Object, _
ByVal args As OleDbRowUpdatingEventArgs)
Debug.WriteLine(args.Command.CommandText)
If args.Errors Is Nothing Then
'
Else
Debug.WriteLine(args.Errors.Message)
Debug.WriteLine(args.Errors.Source)
End If
Debug.WriteLine(args.Status.ToString)
End Sub
Protected Sub OnRowUpdated(ByVal sender As Object, _
ByVal args As OleDbRowUpdatedEventArgs)
Debug.WriteLine(args.Command.CommandText)
If args.Errors Is Nothing Then
'
Else
Debug.WriteLine(args.Errors.Message)
Debug.WriteLine(args.Errors.Source)
End If
Debug.WriteLine(args.Status.ToString)
End Sub
End Class