Question Help with insert into

[blacksteel1988]

Hi, i need your help adding or inserting data into a table, you see, i have this code (im working with access database btw), this goes in the "add" button

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        ' Get items details
        Dim R As Sistema_Gestion_Contable.SIGESCONDS.ItemsRow = Button1.Tag


        ' search for the item code in the data grid
        Dim I As Integer
        Dim ItemLoc As Integer = -1
        For I = 0 To DGVreceipt.RowCount - 1
            If R.Barcode = DGVreceipt.Rows(I).Cells(0).Value Then


                ' if i find the item then
                ItemLoc = I
                Exit For
            End If
        Next


        ' if not i add it
        If ItemLoc = -1 Then
            ' check if disccount applies
            If ComboBox1.Text = "Third Age" Then
                Dim desc As Decimal = 0
                desc = R.saleprice * 0.3
                DGVreceipt.Rows.Add(R.Barcode, R.ItemName, R.BuyPrice, R.SalePrice, 1, R.SalePrice - desc)
            Else
                DGVreceipt.Rows.Add(R.Barcode, R.ItemName, R.BuyPrice, R.SalePrice, 1, R.SalePrice - desc)
            End If


        Else
            ' check if disccount applies
            If ComboBox1.Text = "Third Age" Then
                Dim desc As Decimal = 0
                desc = R.saleprice * 0.3
                DGVreceipt.Rows.Add(R.Barcode, R.ItemName, R.BuyPrice, R.SalePrice, 1, R.SalePrice - desc)
            Else
                DGVreceipt.Rows.Add(R.Barcode, R.ItemName, R.BuyPrice, R.SalePrice, 1, R.SalePrice - desc)
            End If
        End If


        ' clear textbox1 and focus
        TextBox1.Text = ""
        TextBox1.Focus()




        ' Sum the total
        Dim sum As Decimal = 0
        For I = 0 To DGVreceipt.Rows.Count - 1
            sum += DGVreceipt.Rows(I).Cells(5).Value
        Next
        TextBox4.Text = sum  ' <--- my result goes in here
    End Sub

    ' Save Button


    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim MyTransaction As OleDb.OleDbTransaction = Nothing
        Dim MyConection As OleDb.OleDbConnection = Nothing
        Try


            ' Create connection and start it
            MyConection = New OleDb.OleDbConnection(My.Settings.BD_SIGESCONConnectionString) 
            MyConection.Open()
            MyTransaction = MyConection.BeginTransaction 


            ' Insert the txn into the table receipts
            Dim SQL As String = "insert into receipts (Date,total) values (:0,:1)"
            Dim CMD1 As New OleDb.OleDbCommand
            CMD1.Connection = MyConection
            CMD1.Transaction = MyTransaction
            CMD1.CommandText = SQL
            CMD1.Parameters.AddWithValue(":0", Now.Date)
            CMD1.Parameters.AddWithValue(":1", TextBox4.Text)
            CMD1.ExecuteNonQuery()
            CMD1.Dispose()


            ' Get receipt id
            SQL = "select max(idreceipt) as MAXID from receipts"
            Dim CMD2 As New OleDb.OleDbCommand
            CMD2.Connection = MyConection
            CMD2.Transaction = MyTransaction
            CMD2.CommandText = SQL
            Dim IdReceipt As Long = CMD2.ExecuteScalar()
            CMD2.Dispose()




            ' Insert receipt details into receiptsdetails table
            Dim I As Integer
            For I = 0 To DGVreceipt.Rows.Count - 1


                ' Get the values
                Dim Barcode As String = DGVreceipt.Rows(I).Cells(0).Value
                Dim BuyPrice As Decimal = DGVreceipt.Rows(I).Cells(2).Value
                Dim SalePrice As Decimal = DGVreceipt.Rows(I).Cells(3).Value
                Dim ItemCount As Integer = DGVreceipt.Rows(I).Cells(4).Value
                Dim ClientName As String = TextBox5.Text
                Dim SocialId As String = TextBox6.Text


                ' Create an insert statement
                Dim CMD3 As New OleDb.OleDbCommand
                SQL = "insert into receiptdetails (idreceipt,barcode,itemcount,ItemBuyPrice,ItemSalePrice,ClientName,SocialId) Values (:0,:1,:2,:3,:4,:5,:6)"
                CMD3.Connection = MyConection
                CMD3.Transaction = MyTransaction
                CMD3.CommandText = SQL
                CMD3.Parameters.AddWithValue(":0", IdReceipt)
                CMD3.Parameters.AddWithValue(":1", Barcode)
                CMD3.Parameters.AddWithValue(":2", ItemCount)
                CMD3.Parameters.AddWithValue(":3", BuyPrice)
                CMD3.Parameters.AddWithValue(":4", SalePrice)
                CMD3.Parameters.AddWithValue(":5", ClientName)
                CMD3.Parameters.AddWithValue(":6", SocialId)


                CMD3.ExecuteNonQuery()
                CMD3.Dispose()
            Next


            ' If everything goes well save the transaction
            MiTransaccion.Commit()  <---- Here is where my system breaks :angryfire::angryfire::angryfire:


            ' Close db conn
            MyTransaction.Dispose()
            MyConection.Close()
            MyConection.Dispose()


            DGVreceipt.Rows.Clear()
            TextBox4.Text = ""
            TextBox5.Text = ""
            TextBox6.Text = ""
            RadioButton2.Checked = True


        Catch ex As Exception


            If MyTransaction IsNot Nothing Then
                MyTransaction.Rollback()
            End If
            If MiConeccion IsNot Nothing Then
                If MyConection.State = ConnectionState.Open Then
                    MyConection.Close()
                End If
            End If
            MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
        End Try
        Me.DialogResult = Windows.Forms.DialogResult.OK
    End Sub

The error i get is:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"

Help!!! is this a problem with the database? or with my code? because just two weeks ago it worked fine.

thanks in advance

 

[jmcilhinney]

Does your database table have a primary key? If so, what column(s) is it and is the data type of that column(s)?