Loop through two databases and populate listbox

Question

I would like to populate listbox with only items from database B (1st column) by comparing those items split values (.Split(":")(0)) with values from database A (column 2). If split value from database A exist in database B then ignore that item, else add that item in listbox.

I know that I have to connect to both databases but I don't know how to create a loop that would check if split value from one database exists in another and put missing (undivided values) in listbox?

These are database connections

'DATABASE B
            Dim cn As OleDb.OleDbConnection
        Dim strSQL As String
        cn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=w:\Sifarnik.MDB")
        strSQL = "SELECT A1A FROM TILSIF"
        cn.Open()

'DATABASE A 
        Dim cn2 As OleDb.OleDbConnection
        Dim strSQL2 As String
        cn2 = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\;Extended Properties=dBASE IV;")
        strSQL2 = "SELECT BUSS From [DD Pa]"
        cn2.Open()

Thanks.

EDIT : I populated listbox with only split values now all I need is to check every item in listbox if that item exist in another database and if does remove it. Once again I need halp with loop in code below.

Dim cn As OleDb.OleDbConnection
    Dim cmd As OleDb.OleDbCommand
    Dim odr As OleDb.OleDbDataReader
    Dim strSQL As String
    cn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\;Extended Properties=dBASE IV;")

    strSQL = "SELECT POSLOVNIPA From [Ap 1a]"


    cn.Open()
    cmd = New OleDb.OleDbCommand(strSQL, cn)
    odr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

    Do While odr.Read
        ListBox2.Items.Add(odr.GetValue(0).Split(":")(0))
    Loop

EDIT 2 : Now I have two listboxes, one for each database. My question would be how to edit loop to include removal pf items that exist in listbox3 and repopulate listbox 2 with complete value without split?

        Do While odr.Read
        ListBox2.Items.Add(odr.GetValue(0).Split(":")(0))
    Loop

whats the reason for `String.Split`? is the key for the related value embedded as part of another column? — Ňɏssa Pøngjǣrdenlarp, May 24 '14 at 13:46
values are like these : "4055:TEST1", "4546:TEST2", etc in database A and database B has values like : "4055", "4555"... since value "4055:TEST1" (split = 4055) exist in database B, that items should be ignored when listbox is being populated. `String.Split`could be changed with function LEFT (4) — Jovica, May 24 '14 at 13:56
To be more clear : check every item (split to ":" or left-first four char) from database A with every item in database B, if item (split, or left) exist don't add it to listbox — Jovica, May 24 '14 at 14:01
first, get your data into a DataSet or iList (yes, you can bind it to iList as well), then: http://stackoverflow.com/questions/114851/how-do-i-bind-the-result-of-datatable-select-to-a-listbox-control — porkchop, May 24 '14 at 14:16
I think you have a few options depending on whether you are most comfortable with a VB code vs SQL/DBMS solution (or a little of both). How much data you are dealing with may impact or even dictate the solution. Not enough info to go on. — Ňɏssa Pøngjǣrdenlarp, May 24 '14 at 16:12
The main problem for me is how to set a loop to check every item from one DB to another. But even if I decide to use 2 (1 hidden) listboxes for each database and after comparing items in those listboxes and removing duplicates, result would be the same. Still I wouldn't know how to set a loop for that operation. — Jovica, May 24 '14 at 16:42
I've edited original post once again. I was hoping for more elegant solution but beggars can't be choosers. :) — Jovica, May 24 '14 at 16:53

Loop through two databases and populate listbox

0 Answers0