title: vb2008 insert and update record using one button
body: <p>I am trying to make a glossary. I have a form with a listbox, 2 textboxes, and a save button.</p>
  
  <p>The listbox is now populated with words from the database, and when a word is selected, its definition will display in textbox2. </p>
  
  <p>The user can add a record by filling the textbox1 with a new word and textbox2 with its definition,and clicking the save button. If the new word already existed it will not allow to save a new record, also if there's a null value between the 2 textboxes. If it doesn't exist it will be inserted on the table and the new word will be added to the listbox.</p>
  
  <p>The user can also update the record by selecting first a word on the list then edit the word and/or definition and clicking the save button.</p>
  
  <p>I already got the updating part to work but I have problem in inserting a new record. I can't do it properly. The glossary table has only 2 fields: word, definition. Here's my code:</p>
  
  <pre><code>Dim myCmd As New MySqlCommand
  Dim myReader As MySqlDataReader
  Dim myAdptr As New MySqlDataAdapter
  Dim myDataTable As New DataTable
  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
      Call Connect()
      With Me
          If Blank() = False Then
              If Duplicate() = False Then
                  STRSQL = "insert into glossary values (@word, @def)"
                  myCmd.Connection = myConn
                  myCmd.CommandText = STRSQL
                  myCmd.Parameters.AddWithValue("word", txtNew.Text)
                  myCmd.Parameters.AddWithValue("def", txtdefine.Text)
                  myCmd.ExecuteNonQuery()
                  myCmd.Dispose()
                  MsgBox("Record Added")
                  Dim word As String
                  word = txtNew.Text
                  lstword.Items.Add(word)
                  'myConn.Close()
                  'Me.FillListbox()
              Else
                  myConn.Open()
                  STRSQL = "Update glossary set word = @term, definition = @mean where word = @term"
                  myCmd.Connection = myConn
                  myCmd.CommandText = STRSQL
                  myCmd.Parameters.AddWithValue("term", txtNew.Text)
                  myCmd.Parameters.AddWithValue("mean", txtdefine.Text)
                  myCmd.ExecuteNonQuery()
                  myCmd.Dispose()
                  MsgBox("Record Updated", MsgBoxStyle.Information, "New word added")
              End If
          End If
      End With
  
  
  End Sub
  
  Public Function Blank() As Boolean
      Call Connect()
      With Me
          If .txtNew.Text = "" Or .txtdefine.Text = "" Then
              Blank = True
              MsgBox("Cannot save! Term and definition should not contain null value", MsgBoxStyle.Critical, "Unable to save")
          Else
              Blank = False
          End If
      End With
  End Function
  
  Public Function Duplicate() As Boolean
      Call Connect()
      With Me
          STRSQL = "Select * from glossary where word = '" &amp; txtNew.Text &amp; "'"
          myCmd.Connection = myConn
          myCmd.CommandText = STRSQL
          If myDataTable.Rows.Count &lt;&gt; 0 Then
              Duplicate = True
              'MsgBox("Word already exist. Please check the word.", MsgBoxStyle.Critical, "Duplicate.")
  
          Else
              Duplicate = False
          End If
          myConn.Close()
      End With
  End Function
  </code></pre>
  
  <p>this is my connection module:</p>
  
  <pre><code>Public myConnectionString As String
  Public STRSQL As String
  Public myConn As New MySqlConnection
  Public Sub Connect()
      With myConn
          Try
              If .State = ConnectionState.Open Then
                  .Close()
              End If
              myConnectionString = "Database=firstaidcqs;Server=localhost;Uid=root;Password="
              .ConnectionString = myConnectionString
              .Open()
              'MsgBox("Successful Connection")
          Catch ex As Exception
              MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Error")
              .Close()
          End Try
      End With
  End Sub
  Public Sub Disconnect()
      With myConn
          .Close()
          .Dispose()
      End With
  End Sub
  </code></pre>
  
  <p>How can I make this work properly?</p>