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.