title: Insert Statement Not Inserting With Correct Auto Incremental
body: <p>For example, I have the 3 rows currently in the table with a primary key of 0, 1 and 3. I want it to fill the gap, so the next insert statement should take the primary key of 2. However, it is not doing that.</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
      Dim msg As String = ""
  
      If Validate.CheckAddress(txtAddress.Text) And _
         Validate.CheckEmailAddress(txtEmailAddress.Text) And _
         Validate.CheckPhoneNumber(txtPhoneNumber.Text) And _
         Validate.CheckPostcode(txtPostcode.Text) Then
  
          MsgBox("correct validation")
  
          Dim Admin As Integer = 0
          Dim Query As String
  
          If chkAdmin.Checked Then
              Admin = 1
          Else
              Admin = 0
          End If
  
          If Not frmLogin.User.DoesUserExist(CInt(lblUserIDValue.Text)) Then  'if user doesnt exist
              Query = "INSERT INTO tbluser (Admin, UserForename, UserSurname, Username, Password, UserPhoneNumber, UserAddress, UserPostcode, UserEmailAddress) VALUES (@admin, @forename, @surname, @username, @password, @phonenumber, @address, @postcode, @emailaddress)"
              MsgBox("we are inserting")
          Else
              Query = "UPDATE tbluser SET Admin=@admin, UserForename=@forename, UserSurname=@surname, Username=@username, UserAddress=@address, UserPostcode=@postcode, UserEmailAddress=@emailaddress WHERE UserID=@userID"
              MsgBox("we are updating")
          End If
  
          Using Conn As New MySqlConnection(MySQL.ConnectionDetails)
              Using Comm As New MySqlCommand()
                  With Comm
                      .Connection = Conn
                      .CommandText = Query
                      .CommandType = CommandType.Text
                      .Parameters.AddWithValue("@admin", Admin)
                      .Parameters.AddWithValue("@forename", txtForename.Text)
                      .Parameters.AddWithValue("@surname", txtSurname.Text)
                      .Parameters.AddWithValue("@username", (txtForename.Text + txtSurname.Text.First + txtPhoneNumber.Text.Last.ToString)) 'firstname + first letter of surname + last number of phone number
                      .Parameters.AddWithValue("@password", "password")
                      .Parameters.AddWithValue("@phonenumber", txtPhoneNumber.Text)
                      .Parameters.AddWithValue("@address", txtAddress.Text)
                      .Parameters.AddWithValue("@postcode", txtPostcode.Text)
                      .Parameters.AddWithValue("@emailaddress", txtEmailAddress.Text)
                      .Parameters.AddWithValue("@userID", CInt(lblUserIDValue.Text))
                  End With
                  Try
                      Conn.Open()
                      If Comm.ExecuteNonQuery() Then
                          MsgBox("Successfull ")
                          Me.Dispose()
                      End If
                  Catch ex As MySqlException
                      msg += "MySQL Exception " &amp; ex.GetBaseException.ToString
                  End Try
              End Using
         End Using
  
     Else
  
         If (Validate.CheckAddress(txtAddress.Text) = False) Then
             msg += "Not a valid address " &amp; Environment.NewLine
         End If
         If (Validate.CheckEmailAddress(txtEmailAddress.Text) = False) Then
             msg += "Not a valid email address " &amp; Environment.NewLine
         End If
         If (Validate.CheckPhoneNumber(txtPhoneNumber.Text) = False) Then
             msg += "Not a valid phone number " &amp; Environment.NewLine
         End If
         If (Validate.CheckPostcode(txtPostcode.Text) = False) Then
             msg += "Not a valid postcode "
         End If
  
      End If
  
      If (msg &lt;&gt; "") Then
          MessageBox.Show("The following problems were found: " &amp; Environment.NewLine &amp; msg, "User - Error(s)", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
      End If
  
  End Sub
  </code></pre>