Can't insert new record in Access 2K table thru datagrid
I am facing a weird problem. With the following code, I can't insert a new record in an Access table. Though update & delete is working fine with the code. Moreover, same code performs excellently on an Oracle table!
While trying to insert a new record thru grid in Access table, I get following error.
Syntax error in INSERT INTO STATEMENT
Any idea what went wrong? When I create the form using Data Form Wizard then no problem!
Thanks!
-------------------------------------------
Imports System.Data.OleDb
Public Class frmPublisher
Inherits System.Windows.Forms.Form
<< windows form designer genereated code >>
Dim connstr As String
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim cm As CurrencyManager
Dim cnn As OleDbConnection
Dim cbld As OleDbCommandBuilder
Dim FilterCondn As String
Private Sub FormLoad()
Try
connstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Library.mdb;"
'connstr = ("Provider=MSDAORA.1;Password=MKM;User ID=MKM;Data Source=MDB")
cnn = New OleDbConnection(connstr)
da = New OleDbDataAdapter("select * from publisher " & FilterCondn, cnn)
cbld = New OleDbCommandBuilder(da)
ds = New DataSet()
da.Fill(ds, "Publisher")
cm = CType(Me.BindingContext(ds), CurrencyManager)
dg.SetDataBinding(ds, "Publisher")
Catch x As OleDbException
MessageBox.Show(x.Message, "DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch z As Exception
MessageBox.Show(z.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub mnuRecordUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRecordUpdate.Click
Try
'****************************************
'WHY INSERT IS GIVING ERROR IN ACCESS ONLY?
'Syntax error in INSERT INTO STATEMENT
'Couldn't solve the problem
'****************************************
da.Update(ds, "Publisher")
MessageBox.Show("Records updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Catch x As OleDbException
MessageBox.Show(x.Message, "DB Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch z As Exception
MessageBox.Show(z.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub mnuFileLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileLoad.Click
FormLoad()
End Sub
Private Sub mnuFileClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileClose.Click
Me.Close()
End Sub
Private Sub mnuRecordFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRecordFilter.Click
FilterCondn = InputBox("Enter filter criteria", "Input")
End Sub
end class
