Checking if row value is exist

Getting Started
1

Hi,

I have 50.000 rows

I want to block duplicating data entry by comparing the data from listbox.

dim promoEnded As Boolean = False 
dim o as Integer
for o=ListBox1.Listcount downto 0
  dim gg as integer
  for gg=0 to Listbox5.Listcount -1
    if ListBox1.selected(o) then
      If Listbox1.cell(o,1)=Listbox5.cell(gg,0) then
        MessageBox "Barcode exist"
        exit for
      end if
    end if
  next
next

the code is working but way to slow.
is there any simple code that can do the process faster?

thanks
arief

2

Do the check BEFORE you load the data into the listbox.

1 Like
3

Create a dictionary where you buffer the contents of Listbox5 column 0 when filling it and compare your entry against this. Comparison should be much faster as a dictionary uses hash values to find its key values.

1 Like
4

As @Ulrich_Bogun wrote, use a Dictionary to remove Duplicates before you add them to the List.

  • Create a Dictionary
  • Use Lookup to check if a Value is already within the List (and thus in the Dictionary)
  • If Lookup does not find the Value, add it to the Dictionary and the List

Pseudo Code:

If Dictionary.Lookup(NewValue, True) Then
   Add NewVualue to the Dictionary and the List
End If
5

if you store the rows into sqlite database as example you could query
“select count(*) as c from mytable where barcode=?”,barcode

7

If you fill the list boxes one after the other, create the first list and write each entry also into the dictionary. For the second list, you only add the entries that are not yet in the dictionary.

8

You might get a bit of an improvement if you make the listbox invisible before filling it, and make it visible after.

1 Like
9

No no, just disable/enable the vertical scrollbars. :slight_smile:

10

Hi,

thanks for the solution.
will do the testing with these recommendation.

thanks
arief