adding data to database manually?

[Hitman™]

 Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click


        Dim MyConnection As OleDb.OleDbConnection = Nothing
        Dim MyTransaction As OleDb.OleDbTransaction = Nothing
        Try
            'mulakan transaksi dan connection
            MyConnection = New OleDb.OleDbConnection(My.Settings.Sistem_Transaksi_Kedai_PasuConnectionString)
            MyConnection.Open()
            MyTransaction = MyConnection.BeginTransaction


            'ambik id dari recipt
            Dim sql As String
            sql = "select max (NoTransaction) as MAXNO from Transaction"
            Dim CMD2 As New OleDb.OleDbCommand
            CMD2.Connection = MyConnection
            CMD2.Transaction = MyTransaction
            CMD2.CommandText = Sql
            Dim NoTransaction As Long = CMD2.ExecuteScalar()
            CMD2.Dispose()


            'masukkan detail recipt
            Dim i As Integer
            For i = 0 To DGV2.Rows.Count - 1


                'get the value
                Dim NoProduct As String = DGV2.Rows(i).Cells(0).Value
                Dim Price As Decimal = DGV2.Rows(i).Cells(2).Value
                Dim Quantity As Integer = DGV2.Rows(i).Cells(3).Value


                'create command
                Dim CMD3 As New OleDb.OleDbCommand
                sql = "insert into Transaction" & _
                      "(NoTransaction,NoProduct,TotalPrice,Quantity,DateTime) " & _
                      "values " & _
                      "(:0           ,:1       ,:2        ,:3      ,:4) "
                CMD3.Connection = MyConnection
                CMD3.Transaction = MyTransaction
                CMD3.CommandText = Sql
                CMD3.Parameters.AddWithValue(":0", NoTransaction)
                CMD3.Parameters.AddWithValue(":1", NoProduct)
                CMD3.Parameters.AddWithValue(":2", Quantity)
                CMD3.Parameters.AddWithValue(":3", Price)
                CMD3.Parameters.AddWithValue(":4", Now.TimeOfDay)


                CMD3.ExecuteNonQuery()
                CMD3.Dispose()








            Next


            'save changes
            MyTransaction.Commit()


            'close connection
            MyTransaction.Dispose()
            MyConnection.Close()
            MyConnection.Dispose()


            DGV2.Rows.Clear()
            TextBox4.Text = ""


        Catch ex As Exception


            If MyTransaction IsNot Nothing Then
                MyTransaction.Rollback()
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "error")
            End If
            If MyConnection IsNot Nothing Then
                If MyConnection.State = ConnectionState.Open Then
                    MyConnection.Close()
                End If
            End If


        End Try

im using insert into..


but i got error saying "syntax error in from clause"

 

[jmcilhinney]

As far as I'm aware, Access doesn't recognise a colon ) as a parameter prefix. You should use @.

 

[IanRyder]

Hi,

Well, well, well, this one had me scratching my head for a while!

After quickly confirming the you can actually in fact use the colon character in access as a parameter prefix (in 2003 you can anyway, not sure about other versions) it got me wondering what the problem was.

It comes down to the fact that the OleDbCommand object is interpreting your table name called "Transaction" as a reserved word. Please do not ask me why since I do not know. So you need to change the name of your table to something else. i.e tblTransaction.

In addition to this should you not be increasing your NoTransaction variable by 1 each time since I am guessing that this is to be a unique transaction reference?

Hope that helps.

Cheers,

Ian
BTW, I would also take heed of jmcilhinney's comments and use the @ character as your parameter prefix since this is the known industry standard.