Merging Two ADODB.Recordsets

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

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.

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.

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.

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.

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

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.

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.

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!

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!

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