Solved

Combining Recordsets not through UNION

Posted on 2004-08-11
7
410 Views
Last Modified: 2012-06-27
I have 2 databases:
db1 - Contains tables unique to a webpage name. ie tblIndex, tblPage1, tblPage2...Fields ID, LinkID, CategoryOverride

db2 - Contains one large table...ID, URL, Text, Category

I need to accomplish:
- Write ASP script in conjunction with asp to list the links referred to in a db1 that pulls the information from db2.

Problems:
- db2 ID's are autonumber, meaning there is no organization to the list
- when reading from db1, there is no means to organize it (since its just a list of numbers)
- my script i have now is exceptionally slow.  Though it was slower before when i had db2 checking with db1 instead of the reverse. (doing the prior solved problems 1 and 2, but time was a big compromise in that case)

Heres what i have so far:
showDSNStatement = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=db1.mdb"
showConn.Open showDSNStatement
Set secondConn = Server.CreateObject("ADODB.Connection")
secondDSNStatement = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=db2.mdb"
secondconn.open secondDSNStatement
Set secondRS = Server.CreateObject("ADODB.Recordset")
Set showrs = Server.CreateObject("ADODB.Recordset")
 showRS.open "Select Distinct LinkID,CategoryOverride from "&thispagemenutable&" order by LinkID", showconn
       while NOT showRS.EOF
             found=false
             showthisID = showrs.fields("LinkID")
             if session("cat") = "all" OR session("cat") = "" then
                   secondrs.open "Select * from tblDownloads Order By ThisCategory DESC, HeadCategory DESC, DispText", secondconn
             else
                   secondrs.open "Select * from tblDownloads WHERE ThisCategory LIKE '%"&session("cat")&"%'Order By ThisCategory DESC, HeadCategory DESC, DispText", secondconn
             end if
                   while NOT secondrs.EOF AND found=false
                   if showthisID = secondrs.fields("ID") then
                                                             'do stuff
                                                end if
                                                secondrs.MoveNext
                  wend
            secondrs.close
            showRS.moveNext
      wend
showrs.close
showconn.close
secondconn.close


**** I REALIZE THAT I DO NOT HAVE TO LOOP THROUGH ALL OF DB2.  The only reason its in this format is because i USED to have to loop through DB2 multiple times (since it was ordered) and find a match with DB1.  It would be a magnitude greater if i could somehow set up the recordsets then create a third recordset that is Ordered and is where LinkID = ID
0
Comment
Question by:dtburke
  • 4
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 11780280
Surely it would be easier to link one database to the other and then create queries off the combined tables.

Just my 2 cents worth.
0
 

Author Comment

by:dtburke
ID: 11780426
so wait....how does that work using an access database on microsoft 2000 server...using SQL and asp and ADO...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 11784076
>>how does that work using an access database on microsoft 2000 server<<
Can you elaborate what this means?  Do you mean your database is linked into MS SQL Server 2K?  If so, so what?  I do not see any reference to any data in the SQL Server 2K.  All your connection strings are using JET providers.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:dtburke
ID: 11785225
Obviously i dont know what im talking about...perhaps some sample code will make me understand. Or whatever process it takes to "link one database to the other"

I'm looking for a process that will be efficient.
0
 

Author Comment

by:dtburke
ID: 11787055
so using access i found that had the tables been in the same database, then the query could be...

SELECT tblDownloads.*, tblDownloads.ID, tblPub880System.CategoryOverride
FROM tblPub880System INNER JOIN tblDownloads ON tblPub880System.LinkID = tblDownloads.ID
WHERE (([tblDownloads].[ID]=[tblPub880System].[LinkID]))
ORDER BY tblDownloads.ID;

But I have two different databases...how will this work???

[the question seems to have gotten a whole lot easier]
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11789956
>>But I have two different databases...how will this work???<<
Then all you need to do is link in the table(s) from one database into the other and then use only one connection string.  Supposing you decide to use the connection string to DB1 than do the following:
1. In MS Access open DB1
2. In Tables select New
3. Select "Link Table"
4. Select DB2
5. Select the required table(s)

You can now use those tables as if they were in the one database.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11790031
Also, please re-read the EE Guidelines regarding grading:
What's the right grade to give?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi73

And specifically this section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.
Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.
</quote>

Also, see here:
Can I get a grade changed?
http://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi18
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now