An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in sy

Discussion in 'ASP .Net' started by NicK chlam via DotNetMonster.com, Mar 21, 2005.

  1. this is the error i get

    System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
    at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
    DataTableMapping tableMapping)
    at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
    srcTable)
    at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
    at TryThis.Form1.save() in C:\Documents and Settings\Nick\My Documents\
    Vb.net_Practice\TryThis\TryThis\Form1.vb:line 310


    heres my source code


    Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial Bulk
    Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
    "ocking Mode=1;Data Source=""C:\Documents and Settings\Nick\My
    Documents\TryThis.m" & _
    "db"";Mode=Share Deny None;Jet OLEDB:Engine
    Type=5;Provider=""Microsoft.Jet.OLEDB.4" & _
    ".0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist
    security info=False;E" & _
    "xtended Properties=;Jet OLEDB:Compact Without Replica
    Repair=False;Jet OLEDB:Enc" & _
    "rypt Database=False;Jet OLEDB:Create System Database=False;Jet
    OLEDB:Don't Copy " & _
    "Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk
    Transactions=1"
    '
    'OleDbDataAdapter1
    '
    Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
    Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
    Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
    Me.OleDbDataAdapter1.TableMappings.AddRange(New
    System.Data.Common.DataTableMapping() {New
    System.Data.Common.DataTableMapping("Table", "tblCredit", New
    System.Data.Common.DataColumnMapping() {New
    System.Data.Common.DataColumnMapping("Amount", "Amount"), New
    System.Data.Common.DataColumnMapping("Date", "Date"), New
    System.Data.Common.DataColumnMapping("ID", "ID"), New
    System.Data.Common.DataColumnMapping("Memo", "Memo"), New
    System.Data.Common.DataColumnMapping("Name", "Name"), New
    System.Data.Common.DataColumnMapping("Transaction", "Transaction")})})
    Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
    '
    'OleDbSelectCommand1
    '
    Me.OleDbSelectCommand1.CommandText = "SELECT Amount, [Date], ID,
    [Memo], Name, Transaction FROM tblCredit"
    Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
    '
    'OleDbInsertCommand1
    '
    Me.OleDbInsertCommand1.CommandText = "INSERT INTO tblCredit(Amount,
    [Date], [Memo], Name, Transaction) VALUES (?, ?, ?," & _
    " ?, ?)"
    Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
    Me.OleDbInsertCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Amount",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Amount"))
    Me.OleDbInsertCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Date",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Date"))
    Me.OleDbInsertCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Memo",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Memo"))
    Me.OleDbInsertCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Name",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))
    Me.OleDbInsertCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Transaction",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Transaction"))
    '
    'OleDbUpdateCommand1
    '
    Me.OleDbUpdateCommand1.CommandText = "UPDATE tblCredit SET Amount =
    ?, [Date] = ?, [Memo] = ?, Name = ?, Transaction = " & _
    "? WHERE (ID = ?) AND (Amount = ? OR ? IS NULL AND Amount IS NULL)
    AND ([Date] = " & _
    "? OR ? IS NULL AND [Date] IS NULL) AND ([Memo] = ? OR ? IS NULL
    AND [Memo] IS NU" & _
    "LL) AND (Name = ? OR ? IS NULL AND Name IS NULL) AND (Transaction
    = ? OR ? IS NU" & _
    "LL AND Transaction IS NULL)"
    Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Amount",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Amount"))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Date",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Date"))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Memo",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Memo"))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Name",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Name"))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Transaction",
    System.Data.OleDb.OleDbType.VarWChar, 50, "Transaction"))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_ID",
    System.Data.OleDb.OleDbType.Integer, 0,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "ID", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Amount",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Amount", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Amount1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Amount", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Date",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Date1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Memo",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Memo", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Memo1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Memo", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Name",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Name", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Name1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Name", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Transaction",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Transaction", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbUpdateCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Transaction1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Transaction", System.Data.DataRowVersion.Original, Nothing))
    '
    'OleDbDeleteCommand1
    '
    Me.OleDbDeleteCommand1.CommandText = "DELETE FROM tblCredit WHERE
    (ID = ?) AND (Amount = ? OR ? IS NULL AND Amount IS N" & _
    "ULL) AND ([Date] = ? OR ? IS NULL AND [Date] IS NULL) AND ([Memo]
    = ? OR ? IS NU" & _
    "LL AND [Memo] IS NULL) AND (Name = ? OR ? IS NULL AND Name IS NULL)
    AND (Transac" & _
    "tion = ? OR ? IS NULL AND Transaction IS NULL)"
    Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_ID",
    System.Data.OleDb.OleDbType.Integer, 0,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "ID", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Amount",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Amount", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Amount1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Amount", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Date",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Date1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Date", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Memo",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Memo", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Memo1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Memo", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Name",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Name", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Name1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Name", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Transaction",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Transaction", System.Data.DataRowVersion.Original, Nothing))
    Me.OleDbDeleteCommand1.Parameters.Add(New
    System.Data.OleDb.OleDbParameter("Original_Transaction1",
    System.Data.OleDb.OleDbType.VarWChar, 50,
    System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte)
    , "Transaction", System.Data.DataRowVersion.Original, Nothing))
    '
    'DataSet11
    '
    Me.DataSet11.DataSetName = "DataSet1"
    Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
    '

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    Timer1.Enabled = False


    Bind()
    OleDbDataAdapter1.Fill(DataSet11)


    End Sub
    Private Sub Bind()
    Dim bind As Binding
    bind = New Binding("text", DataSet11, "tblCredit.Date")
    TextBox1.DataBindings.Add(bind)
    bind = New Binding("text", DataSet11, "tblCredit.Transaction")
    TextBox2.DataBindings.Add(bind)
    bind = New Binding("text", DataSet11, "tblCredit.Name")
    TextBox3.DataBindings.Add(bind)
    bind = New Binding("text", DataSet11, "tblCredit.Amount")
    TextBox4.DataBindings.Add(bind)
    bind = New Binding("text", DataSet11, "tblCredit.ID")
    TextBox6.DataBindings.Add(bind)
    bind = New Binding("text", DataSet11, "tblCredit.Memo")
    TextBox7.DataBindings.Add(bind)



    End Sub
    Private Sub save()

    Dim InsertRow, ModRow, delRow As DataSet

    Me.BindingContext(DataSet11, "tblCredit").EndCurrentEdit()
    InsertRow = DataSet11.GetChanges(DataRowState.Added)
    ModRow = DataSet11.GetChanges(DataRowState.Modified)
    delRow = DataSet11.GetChanges(DataRowState.Deleted)
    Try
    If Not InsertRow Is Nothing Then
    OleDbDataAdapter1.Update(InsertRow)
    End If
    Catch ex As Exception
    TextBox5.Text = Convert.ToString(ex)
    End Try

    If Not ModRow Is Nothing Then
    OleDbDataAdapter1.Update(ModRow)

    End If
    If Not delRow Is Nothing Then
    OleDbDataAdapter1.Update(delRow)
    End If


    DataSet11.AcceptChanges()
    'An unhandled exception of type 'System.Data.OleDb.OleDbException'
    occurred in system.data.dll

    End Sub

    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnBack.Click
    Me.BindingContext(DataSet11, "tblCredit").Position -= 1

    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnNext.Click
    Me.BindingContext(DataSet11, "tblCredit").Position += 1
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnSave.Click
    save()
    End Sub

    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles btnNew.Click
    Me.BindingContext(DataSet11, "tblCredit").AddNew()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Timer1.Tick
    TextBox5.Text = Me.BindingContext(DataSet11, "tblCredit").Position
    End Sub
    End Class

    --
    Message posted via http://www.dotnetmonster.com
    NicK chlam via DotNetMonster.com, Mar 21, 2005
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lee Simpson
    Replies:
    0
    Views:
    1,555
    Lee Simpson
    Jul 18, 2003
  2. silesius
    Replies:
    0
    Views:
    1,165
    silesius
    Nov 16, 2004
  3. Ankit Aneja
    Replies:
    5
    Views:
    11,597
    Steven Cheng[MSFT]
    Apr 6, 2006
  4. psychomad
    Replies:
    2
    Views:
    5,168
    Alexey Smirnov
    Apr 10, 2007
  5. Warren Tang
    Replies:
    1
    Views:
    861
    Warren Tang
    Sep 23, 2008
Loading...

Share This Page