title: How to avoid duplicate record on listview update in VB.NET
body: <p>i have no problem when inserting records if it has a duplicate, but not on updating a record, and i really have no idea what to do here.</p>
  
  <p>here's my code:</p>
  
  <pre><code>Imports MySql.Data.MySqlClient
  Public Class Form3
  Public id As Integer
  Public sConnection As New MySqlConnection
  Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      If sConnection.State = ConnectionState.Closed Then
          sConnection.ConnectionString = "server=localhost;user id=root;database=db;password=root"
          sConnection.Open()
      End If
      LoadPeople()
  
  End Sub
  Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
      If id = Nothing Then
          MsgBox("Please choose a record to edit.", MsgBoxStyle.Exclamation)
      Else
          Dim sqlQuery As String = "SELECT * FROM candidate WHERE cid = '" &amp; lvPeople.SelectedItems(0).Text &amp; "'"
          Dim sqlAdapter As New MySqlDataAdapter
          Dim sqlCommand As New MySqlCommand
          Dim sqlTabble As New DataTable
  
          With sqlCommand
              .CommandText = sqlQuery
              .Connection = sConnection
              .ExecuteNonQuery()
          End With
  
          With sqlAdapter
              .SelectCommand = sqlCommand
              .Fill(sqlTabble)
          End With
          Form5.id = lvPeople.SelectedItems(0).Text
          Form5.cfname = sqlTabble.Rows(0)("cfname")
          Form5.cmname = sqlTabble.Rows(0)("cmname")
          Form5.clname = sqlTabble.Rows(0)("clname")
          Form5.cpos = sqlTabble.Rows(0)("cpos")
          Form5.cyr = sqlTabble.Rows(0)("cyr")
          Form5.cparty = sqlTabble.Rows(0)("cparty")
          Form5.vyear = sqlTabble.Rows(0)("vyear")
          Form5.ShowDialog()
          id = Nothing
      End If
  
  
  
  End Sub
  
  Private Sub lvPeople_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lvPeople.MouseClick
      id = lvPeople.SelectedItems(0).Selected
  End Sub
  </code></pre>
  
  <p>my <code>Listview</code> code:</p>
  
  <pre><code>Public Sub LoadPeople()
      Dim sqlQuery As String = "select * from candidate where cfname&lt;&gt;'Select a Candidate'"
      Dim sqlAdapter As New MySqlDataAdapter
      Dim sqlCommand As New MySqlCommand
      Dim TABLE As New DataTable
      Dim i As Integer
  
      With sqlCommand
          .CommandText = sqlQuery
          .Connection = sConnection
      End With
  
      With sqlAdapter
          .SelectCommand = sqlCommand
          .Fill(TABLE)
      End With
  
      lvPeople.Items.Clear()
  
      For i = 0 To TABLE.Rows.Count - 1
          With lvPeople
              .Items.Add(TABLE.Rows(i)("cid"))
              With .Items(.Items.Count - 1).SubItems
                  .Add(AddFieldValue(TABLE.Rows(i), ("cpos")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("cfname")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("cmname")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("clname")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("cyr")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("cparty")))
                  .Add(AddFieldValue(TABLE.Rows(i), ("vyear")))
              End With
          End With
      Next
  
  End Sub
  
  Private Function AddFieldValue(ByVal row As DataRow, ByVal fieldName As String) As String
      If Not DBNull.Value.Equals(row.Item(fieldName)) Then
          Return CStr(row.Item(fieldName))
      Else
          Return Nothing
      End If
  End Function
  </code></pre>
  
  <p>this <code>update</code> form will popup if the <code>edit</code> button is pressed</p>
  
  <pre><code>Imports MySql.Data.MySqlClient
  Public Class Form5
  Friend id As Integer
  Friend cfname As String
  Friend clname As String
  Friend cmname As String
  Friend cpos As String
  Friend cyr As String
  Friend cparty As String
  Friend vyear As Integer
  
  Public sConnection As New MySqlConnection
  
  Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      If sConnection.State = ConnectionState.Closed Then
          sConnection.ConnectionString = "server=localhost;user id=root;database=db;password=root"
          sConnection.Open()
      End If
  
      txtfname.Text = cfname
      txtlname.Text = clname
      txtmname.Text = cmname
      cmbpos.Text = cpos
      cmbyr.Text = cyr
      txtparty.Text = cparty
      TextBox1.Text = vyear
  
  End Sub
  
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  
      Dim conn As New MySqlConnection
      Dim cmd As New MySqlCommand
      Dim dr As MySqlDataReader
  
      conn.ConnectionString = "server = localhost; user id = root; database = db; password = root"
      cmd.Connection = conn
      conn.Open()
      cmd.CommandText = " SELECT * FROM candidate WHERE cfname LIKE '" &amp; txtfname.Text &amp; "' AND cmname='" &amp; txtmname.Text &amp; "' AND clname='" &amp; txtlname.Text &amp; "'"
      dr = cmd.ExecuteReader
  
      If dr.HasRows Then
          MessageBox.Show("Entry already exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
      ElseIf txtfname.Text = "" Or txtlname.Text = "" Or txtmname.Text = "" Or txtparty.Text = "" Or cmbyr.Text = "" Or cmbpos.Text = "" Then
          MessageBox.Show("Please complete the required fields..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
      Else
          Dim sqlQuery As String = "UPDATE candidate SET cfname='" &amp; txtfname.Text &amp; "', cmname='" &amp; txtmname.Text &amp; "', clname='" &amp; txtlname.Text &amp; "' , cpos='" &amp; cmbpos.Text &amp; "', cyr='" &amp; cmbyr.Text &amp; "', cparty='" &amp; txtparty.Text &amp; "' WHERE cid='" &amp; id &amp; "'"
          Dim sqlCommand As New MySqlCommand
  
          With sqlCommand
              .CommandText = sqlQuery
              .Connection = sConnection
              .ExecuteNonQuery()
          End With
          MsgBox("Record Updated")
          Dispose()
      End If
  
  
      Form3.LoadPeople()
      Me.Close()
  End Sub
  
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
      Me.Hide()
  End Sub
  End Class
  </code></pre>
  
  <p>i tried to delete the record when i press the <code>edit</code> button so it will not find the entry then insert the edited entry when i press <code>update</code> button and insert the same entry when i press the <code>cancel</code> button, and yeah it didn't work.</p>