[2008] ms.access two tables...

i'm having a ms access database having two tables.. i tried to connect, it connected...i tried to insert new row in the first table, it insert... but when i insert a new row in second table, it says the "Syntax error in INSERT INTO statement." in the Update command in the data adapter... here is the full code:

Code:

---

Imports System.Data.OleDb
Public Class MainForm

    Private accessConnection As New OleDbConnection
    Private accessDataSet As DataSet

    Private accessPDataAdapter As OleDbDataAdapter
    Private accessPCommandBuilder As OleDbCommandBuilder

    Private accessTDataAdapter As OleDbDataAdapter
    Private accessTCommandBuilder As OleDbCommandBuilder

    Private accessSQL As String

#Region "Functions"
    Private Function ParticularName(ByVal id As Integer) As String
        For Each tempRow As DataRow In accessDataSet.Tables("particulars").Rows
            If tempRow.Item("ID") = id Then
                Return tempRow.Item("name")
            End If
        Next
        Return Nothing
    End Function
#End Region

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TransDiscount_How.Items.Add("$")
        OpenFileDialog1.ShowDialog()
    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        accessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + OpenFileDialog1.FileName

        Try
            accessDataSet = New DataSet

            ' Fill Particulars
            accessSQL = "SELECT * FROM `particulars`"
            accessPDataAdapter = New OleDbDataAdapter(accessSQL, accessConnection)
            accessPDataAdapter.TableMappings.Add("Table", "particulars")
            accessPDataAdapter.Fill(accessDataSet, "particulars")

            ' Fill Particulars
            accessSQL = "SELECT * FROM `transactions`"
            accessTDataAdapter = New OleDbDataAdapter(accessSQL, accessConnection)
            accessTDataAdapter.TableMappings.Add("Table", "transactions")
            accessTDataAdapter.Fill(accessDataSet, "transactions")

            accessConnection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Exit Sub
        End Try

        For Each tempRow As DataRow In accessDataSet.Tables("particulars").Rows
            ' put them in debit/credit combobox (for adding transaction)
            Dim tempItem1 As New ParticularItem(tempRow.Item("ID"), tempRow.Item("name"), tempRow.Item("description"), tempRow.Item("type"))
            TransDebit.Items.Add(tempItem1)
            TransCredit.Items.Add(tempItem1)

            ' and put this in the main particular list
            Dim tempStr() As String = {tempRow.Item("ID"), tempRow.Item("name"), tempRow.Item("description"), tempRow.Item("type")}
            Dim tempItem2 As New ListViewItem(tempStr)
            ParticularsList.Items.Add(tempItem2)
        Next

        ' Add transactions
        For Each tempRow As DataRow In accessDataSet.Tables("transactions").Rows
            Dim discount As String = Nothing

            If (Val(tempRow.Item("discount")) > 0) And tempRow.Item("discount_how") <> "" Then
                If tempRow.Item("discount_how") = "%" Then
                    discount = Val(tempRow.Item("discount")).ToString + "%"
                Else
                    discount = tempRow.Item("discount_how") + Val(tempRow.Item("discount")).ToString
                End If
            Else
                discount = "-"
            End If

            Dim tempDate As New Date(tempRow.Item("date"))
            Dim tempStr() As String = {tempRow.Item("ID"), tempDate.ToString("dd/MM/yyyy"), ParticularName(tempRow.Item("debit")), ParticularName(tempRow.Item("credit")), tempRow.Item("amount"), discount}
            Dim tempItem As New ListViewItem(tempStr)
            TransactionsList.Items.Add(tempItem)
        Next
    End Sub

    Private Sub AddPart_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddPart_btn.Click
        If PartName.Text.Trim = "" Then
            MessageBox.Show("Please fill out the name for the particular.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information)
            PartName.Focus()
            Exit Sub
        End If

        If PartType.SelectedItem = "" Then
            MessageBox.Show("Please select the type of the particular.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Information)
            PartType.Focus()
            Exit Sub
        End If

        accessPCommandBuilder = New OleDbCommandBuilder(accessPDataAdapter)

        Dim tempRow As DataRow = accessDataSet.Tables("particulars").NewRow
        tempRow.Item("name") = PartName.Text
        tempRow.Item("description") = PartDesc.Text
        tempRow.Item("type") = PartType.SelectedItem

        accessDataSet.Tables("particulars").Rows.Add(tempRow)

        If MessageBox.Show("Do you want to save the database right now?", "Save", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            accessPDataAdapter.Update(accessDataSet, "particulars")
        End If
    End Sub

    Private Sub AddTrans_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddTrans_btn.Click
        accessTCommandBuilder = New OleDbCommandBuilder(accessTDataAdapter)

        Dim tempRow As DataRow = accessDataSet.Tables("transactions").NewRow
        tempRow.Item("debit") = CType(TransDebit.SelectedItem, ParticularItem).ID
        tempRow.Item("credit") = CType(TransCredit.SelectedItem, ParticularItem).ID
        tempRow.Item("description") = TransDesc.Text
        tempRow.Item("amount") = Val(TransAmount.Text)
        If Val(TransDiscount.Text) > 0 Then
            tempRow.Item("discount") = Val(TransDiscount.Text)
            tempRow.Item("discount_how") = TransDiscount_How.Text
        End If
        tempRow.Item("date") = TransDate.Value.Ticks

        accessDataSet.Tables("transactions").Rows.Add(tempRow)

        If MessageBox.Show("Do you want to save the database right now?", "Save", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            accessTDataAdapter.Update(accessDataSet, "transactions")
        End If
    End Sub
End Class

---

table1 name: particulars
table2 name: transactions

Where is the Insert statement?

look in the end, you will find this: accessDataSet.Tables("transactions").Rows.Add(tempRow)

after using this i've added: accessTDataAdapter.Update(accessDataSet, "transactions")
which updates the data...

i'm usuing the same process with the first table and it works...

can i have a better way to add a row???

But that's not the SQL statement. It is telling you that there is a syntax error in your Insert statement. As in:

sql Code:

INSERT INTO tblName (Column1, Column2) VALUES ('blah1', 'blah2')

but i'm not using this type of code in sql commands... i'm having a OleDbCommandBuilder, which automatically makes the command...

well leave it... can you tell me a better way to add, delete, edit rows in a database having TWO tables with DIFFERENT COLUMNS

Table1: particulars
its columns: ID, name, description and type

Table2: transactions
its columns: ID, deb, cre, amt, discount, discount_how, date

both IDs are autonumber and deb, cre, amt, discount, date are integer...

i would really appreaciate it...i'm trying to search for making a database with two tables but could not find a good one...