Solved

Combining Recordsets not through UNION

Posted on 2004-08-11
7
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

688 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