Updating Data if find a duplicate

[ImZick]

I have a code here which will add the data in the system and also if it find a data that exist in the system a message box will appear if the user want to overwrite the data.

here is the code

            Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & ConAccessDatabase & "';")
            Dim CountResult As Integer
            con.Open()
            Dim cmd As New OleDbCommand("", con)

            With Form_Month_End_Report

                cmd.CommandText = "SELECT COUNT(*) AS numRows FROM [" & EBU2DB_LOB & "] WHERE Months = '" & .Combo_LOB_Month.Text & "' AND Brand = '" & .Combo_LOB_Brand.Text & "' And LOB = '" & .Combo_LOB_LOB.Text & "'"
                CountResult = cmd.ExecuteScalar()
                con.Close()

                If CountResult > 0 Then
                    'If their are duplicates then perform this code
                    Dim result = MessageBox.Show _
                    ("The Brand ' " & .Combo_LOB_Brand.Text & _
                    " ' with an LOB of ' " & .Combo_LOB_LOB.Text & _
                    " ' in Month of " & .Combo_LOB_Month.Text & " ' is already exist." & vbCrLf & _
                    "Do you want to Replace it?", "Month End System", MessageBoxButtons.YesNo)

                    'code if the user want to replace the data that exist
                    If result = DialogResult.Yes Then
                        cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
                        & " SET Months = ?," _
                        & " Brand = ?," _
                        & " LOB = ?,"
                        cmd.Parameters.AddWithValue("@Months ", .Combo_LOB_Month.Text)
                        cmd.Parameters.AddWithValue("@Brand ", .Combo_LOB_Brand.Text)
                        cmd.Parameters.AddWithValue("@LOB ", .Combo_LOB_LOB.Text)
                        cmd.Parameters.AddWithValue("@Revenue ", .Text_LOB_Revenue.Text)
                        cmd.Parameters.AddWithValue("@GP ", .Text_LOB_GP.Text)
                    End If
                    'end of replace

                Else
                    'If their are no duplicates then add it to the database
                    cmd.CommandText = "INSERT INTO [" & EBU2DB_LOB & "] (Months,Brand,LOB,Revenue,GP) VALUES(?,?,?,?,?)"
                    cmd.Parameters.AddWithValue("@Months ", .Combo_LOB_Month.Text)
                    cmd.Parameters.AddWithValue("@Brand", .Combo_LOB_Brand.Text)
                    cmd.Parameters.AddWithValue("@LOB ", .Combo_LOB_LOB.Text)
                    cmd.Parameters.AddWithValue("@Revenue ", .Text_LOB_Revenue.Text)
                    cmd.Parameters.AddWithValue("@GP ", .Text_LOB_GP.Text)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                    'Add database End
                End If

            End With

however it if find an item and asking if i want to overwrite it and if i check yes nothing happen.
it still the same.

[pritaeas]

The comma after LOB is messing it up I think. Are you sure you want to update without a where clause btw (affecting all records)?

[ImZick]

opps i forgot the where but also if i put that

If result = DialogResult.Yes Then
cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
& " SET Months = ?," _
& " Brand = ?," _
& " Where LOB = ?,"

the data i want to replace is just the same nothing happened

[Reverend Jim]

Still too many commas. Try

If result = DialogResult.Yes Then
    cmd.CommandText = "UPDATE [" & EBU2DB_LOB & "] " _
                    & "   SET Months = ?," _
                    & "       Brand  = ? " _
                    & " WHERE LOB    = ? "

[ImZick]

Still it doesnt work. :(

[ImZick]

the data is still the same..

    cmd.CommandText = "SELECT COUNT(*) AS numRows FROM [" & EBU2DB_LOB & "] WHERE Months = '" & .Combo_LOB_Month.Text & "' AND Brand = '" & .Combo_LOB_Brand.Text & "' And LOB = '" & .Combo_LOB_LOB.Text & "'"
CountResult = cmd.ExecuteScalar()
con.Close()

as you can see i want to find if the data is already in the database (Months, Brand and LOB) after it find

a messagebox will show if i want to overwrite it.
the if the user click yes
the data will update the Revenue and GP

but my problem is that the GP and Revenue is still the same.

[Reverend Jim]

You are only updating Months and Brand. Why would you expect GP and Revenue to be different? And because you are only doing an update, and you are only selecting COUNT(*), why would you expect the count to change?