We help IT Professionals succeed at work.

how to compare 2 recordsets and find a particualar record?

priya_pbk
priya_pbk asked
on
hi

I have 2 recordsets, say one is "Adodc1" and the other is the normal recordset say "rs".

At a particular point of time the Adodc1 will have say 10 records. The columns in Adodc1 are name and comments.

eg:
  Name     Comments
  Apple    500 pounds
  Orange   200 pounds
  Pear     600 pounds
etc...


In the other recordset, I have simlar names in addition to other names

eg:
  Name     Comments
  Apple    585 pounds
  Mango    100 pounds
etc...

These are dummy figures and records, actually it's a huge recordset consisting of company name and its related comments.

The Adodc1 is bound to a Datagrid and the other recordset is made at runtime reading from an other table.

What I wish to do:-
If the rs recordset consists of names matching the adodc recordset the adodc should be overwritten by the value of comment in rs recordset, and if the adodc does not have the value in rs , then that value will be inserted in Adodc recordset.

What I am doing right now :-(which i feel is not the best option)
I am using the For loop to move thru the adodc recorset and using an If..end if Loop within that..and then again a for..next loop...
(I find this very cumbersome, for the fact that i am quering a huge table)

i tried using Adodc1.recordset.Find "name='Apple'" and simlar things.

Hope somebody can tell me an easy and not so cumbersome method!

Rgds,
Priya

Comment
Watch Question

Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
The main key for doing this faster is that both rs and adodc1 are sorted by the name field. Then, you could do a merge loop, basing yourself on the name field...

i assume that both records are sorted by name field ascending:

rs.movefirst
adodc1.movefirst
while not rs.eof and not adodc1.eof
  if rs.eof then
    'there are only records in adodc1, i guess you don't need to do anything...
    adodc1.movelast
  else
    if adodc1.eof
      'there are only records in rs, add them all to adodc1
      while not rs.eof
         adodc1.addnew array("name", "comments"), array(rs.collect("name"), rs.Collect("comments"))
         adodc1.update
         rs.movenext  
      wend
    else
      'we have records in rs and adodc1, now compare the name value:
      if rs.collect("name") = adodc1.collect("name") then
        'update the comments value
        adodc1.fields("comments").value = rs.Collect("comments")
        adodc1.update
        adodc1.movenext
        rs.movenext
      else
         if rs.collect("name") < adodc1.collect("name") then
           'we have a name in rs, but not in adodc1
           adodc1.addnew array("name", "comments"), array(rs.collect("name"), rs.Collect("comments"))
           adodc1.update
           rs.movenext  
         else
           'we have a name in adodc1, but not in rs,
           'nothing to do
           adodc1.movenext
         end if
      end if
    end if
  end if
wend


Please don't shoot at me if i made typo errors, i wrote this right into this box :-)

Cheers

Author

Commented:

how do i get the property "collect" which you have used in numerous places. I dont see any property or method by the name "collect" in rs. Kindly let me know abt it!

Thanks

Author

Commented:

is it rs.fields("name")???
Valliappan ANSenior Tech Consultant
BRONZE EXPERT

Commented:
If these 2 are tables, y dont u use just 2 queries like this:

Update adodctablename set comments=(select rstablename.comments from rstablename where rstablename.name=adodctablename)

Insert into adodctablename Select * from rstablename WHERE rstablename.name not in (select name from adodctablename)

'Hope I have understood your problem correct.
Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the .Collect is a hidden method, but it will work. It is the same as .Field().Value, but it is faster.

Cheers

Author

Commented:

thanks a lot. It really helped and I was able to learn something new. I had never heard about the Collect Method. So I guess I will give you the points. Even though I had to change the code(the addnew statement made the Adodc recordset to position itself at the EOF...so instead of Adodc1.movenext, i tried the movefirst method. Which did the trick, hope I did this correct.

Anyways Thank You.

Cheers,
Priya
Nitin SontakkeDeveloper
BRONZE EXPERT

Commented:
It's a bit too late to comment on this but couldn't resist:

As far as i persieve, "valli_an" has understood your problem correctly and the solution he is giving is most appropriate and most efficient. It is also the best solution to implement in case of large data, which is your scenario anyway.

You may go on any path you have already chosen, but this is for your information please.

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
NitinSontakke, you are absolutely correct, and i only provided the VB view of things...
That way is much faster BTW.
CHeers

Author

Commented:

Thanks a lot to everyone. I have tried using both at different places, so that i can make the best use of resources and thereby increasing the speed and performance.

Since the "collect" method was not know to me, I used that.

Anyways, thanks all of u for all the info.

Best Regards
Priya

Explore More ContentExplore courses, solutions, and other research materials related to this topic.