Avatar of Shroder
Shroder
 asked on

Merging Two ADODB.Recordsets

I'm wondering if there is an easy way to merge two seperate recordsets using vbScript in an ASP file. Basically I'm trying to take recordset a and place recordset b directly under so I can then run through the merged recordset in a while loop (using rs.MoveNext()).

I asked about this before and someone threw some functions out. Unfortunetly I wasn't able to get too far, so I ask that anyone posting functions also include a code example so I can see how it is suppose to be implemented.

If anyone needs more information or clarification I would appreciate it.

Best Wishes,
Justin
Programming

Avatar of undefined
Last Comment
b0lsc0tt

8/22/2022 - Mon
b0lsc0tt

Shroder,

Since you will want to loop through each recordset (same loop) then they most likely share some fields.  Can you use a UNION (http://www.w3schools.com/sql/sql_union.asp) in your SQL query to have the recordset that is returned contain both tables?  UNION will only return distinct records (e.g. a duplicate in the second table will be ignored) but you can use UNION ALL if you want all values.

If that won't work and you must have 2 recordsets to start then add the records from the second recordset (e.g. rs2) to the first (e.g. rs).  The code below should give you a basic idea.  Please provide details of your code if you want it to be more specific.

Do While NOT rs2.EOF
    rs.AddNew
    rs("fieldname1") = rs2("fieldname1")
    rs("fieldname2") = rs2("fieldname2")
    rs.Update
    rs2.MoveNext
Loop

Let me know if you have any questions or need more information.

b0lsc0tt
Shroder

ASKER
Maybe using union will be best. Basically I'm running one query that pulls records owned by the users and the second query pulls records owned by the user's company.

They need to order by one of the other fields. I was afraid that by adding the 'order by' it would cause the first query to sort somewhere else besides the top.

Let me know if this doesn't make sense.

I would like to see if using the union will work, or if I need to try the example you told me.
b0lsc0tt

You need to be more specific for me to say if it makes sense or not.  What are the names of the tables and the fields in each table and which field or fields will you sort by?  Are there duplicate records in and between the tables and do you want those?  What type of database is this?  Also please post the SQL queries you are currently using in your script.

You can do your own tests of different queries to see which works best without providing this information to me.  You may even have a way to run that type of test directly on the database without having to do it in ASP.  Then you can see what results UNION or UNION ALL will return and if one will work.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shroder

ASKER
The two recordsets are pulling data from the same table just by different criteria. So yes there would be duplicate records between the two recordsets and the table structure would be exactly the same.
b0lsc0tt

If the queries use different criteria then you probably can't combine them and you will have to try to use the second option I mentioned in my first comment, the loop.  Even that option depends on the fields returned in the recordsets being the same or similar.  Since you still have not provided specific details I can only guess and provide options.  If you aren't sure of the details needed then please look at my last comment.

If your last comment was just an update then I apologize.  I misread it and thought you wanted some response even though I am still missing details.  I hope this helps and let me know of something if you have a question about something.
Shroder

ASKER
I'm finding it hard to believe there isn't an easy way to accomplish what I am trying to do here. :) Do you know if .net (with vb or vb.net) has a work around for this?

Anyway, moving on to the second option.

I have actually tried this method already and stopped mid way, because it just seemed like a lot of work for something that should be easier to implement. Is there a way to make the example you gave more automated so that the fields are dynamic (and so is the number of fields)?

Best Wishes,
Justin
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Shroder

ASKER
Just a quick thought. Would it be possible to run a stored procedure that contains two SQL queries and have that go into one recordset? I'm not that familiar with how that works, so I'm not sure if that is far fetched.
b0lsc0tt

Justin,

DETAILS, DETAILS! <groan>  You are asking me to help simplify something that I only just barely know.  That limits how effective I can be. :)

The second option, the loop, is pretty easy.  The complexity comes with the number of fields and records involved and that will be the same no matter what you use.

I have not used stored procedures either but that may be an option.  Since those depend on the database used you will need to answer that question.  I have not played with .NET either and so I don't know if it has some built in functions that would make it easier.

As far as dynamic you can do this in a way.  In ASP (and probably everything else) the recordset does have a fields collection.  You can loop through it but I don't believe this is an option you want to use in this case.  You can return all of the fields but you won't know what they are or contain without looking at the value.  That would be even more work for this issue.

I hope this information is helpful.
Shroder

ASKER
b0lsc0tt,

The lack of details is because I'm trying to start off at a very general point. I am comming from a PHP background where this type of stuff is quiet simple (or atleast I'm used to it now with that language), and I'm trying to get my head around these type of restrictions.

I don't want to take up more time then the 500 points is worth, so I can split this question into a couple different threads if need be, but I'm not sure if that is going to be needed.

At this point I really just need to confirm one thing, and have one question answered.

1.)
It looks like doing this will be best:

Do While NOT rs2.EOF
    rs.AddNew
    rs("fieldname1") = rs2("fieldname1")
    rs("fieldname2") = rs2("fieldname2")
    rs.Update
    rs2.MoveNext
Loop

But from your message it sounds like there is no way to detect that "fieldname1" is really "fieldname1". That has to be staticaly set. So if that is the case maybe I'll need to set constants?

2.)
Is there a hashtable (asp.net) equivalent in asp?


Thank you for all your helpl so far. Look forward to your reply!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
b0lsc0tt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Shroder

ASKER
Sorry for the delay in accepting the answer. I was trying to see if I could implement the solution first and take care of any questions that would come up, but I don't think I'm going to get to that any time soon.


Thanks for your help!
b0lsc0tt

Your welcome!  I'm glad that you did come back to close this and that I could help.  Thank you for the grade, the points and the fun question.

bol