Solved

Paging through recordsets with multiple databases

Posted on 2004-08-06
4
244 Views
Last Modified: 2012-06-21
I hope this is going to make sense. I am pasting most of my code because I am not sure where my problem is located. If I have only one objrst.Pagesize then it returns records where they match but in the database that does not have a matching record it returns error '80020009'. I am new at this so I need the answer to be very descriptive. One of you helped me setup my multiple connections and queries to the multiple databases but now I cannot seem to get it to return the date matches without returning an error for the database that does not have any matching results. The way I have my code setup right now does not return anything. It says 0 records found. There should be two matching records. Once I get it working I need to add two more databases for it to query. Thank you!


' Create the Connection Object
      Set objConn = Server.CreateObject("ADODB.Connection")
      Set objConn1 = Server.CreateObject("ADODB.Connection")
      Set objConn2 = Server.CreateObject("ADODB.Connection")
      
      
            ' map database
                  filePath = "\\ppi-lmk\wwwroot\ftl\ftl.mdb"
                  filePath1 = "\\ppi-lmk\wwwroot\dfb\dfb.mdb"
                  filePath2 = "\\ppi-lmk\wwwroot\dpg\dpg.mdb"
                  

                  Set objConn = Server.CreateObject("ADODB.Connection")
                  Set objConn1 = Server.CreateObject("ADODB.Connection")
                  Set objConn2 = Server.CreateObject("ADODB.Connection")
                              
                  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath            
                  objConn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath1            
                objConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath2            

            
      If Request.Form("txtdate") <> "" Then
            strdate = Request.Form("txtdate")
      Else
            strdate = Request.QueryString("date")
      End If
      
      
      Sql = "Select PRODUCT,INSTALLED_DATE,LINER_VALUE From [Service Line Repair/Main to Property Line] where installed_date = #" & strdate       & "#"
      Sql1 = "Select PRODUCT,INSTALLED_DATE,LINER_VALUE From [Service Line Repair/Main to Property Line] where installed_date = #" & strdate       & "#"
    Sql2 = "Select PRODUCT,INSTALLED_DATE,LINER_VALUE From [Service Line Repair/Main to Property Line] where installed_date = #" & strdate       & "#"
    'Sql3 = "Select PRODUCT,INSTALLED_DATE,LINER_VALUE From [Service Line Repair/Main to Property Line] where installed_date = #" & strdate       & "#"
           
            ' Create you Recordset Object
      Set objRst = Server.CreateObject("ADODB.Recordset")
      Set objRst1 = Server.CreateObject("ADODB.Recordset")
      Set objRst2 = Server.CreateObject("ADODB.Recordset")
      
      
      objRst.CursorLocation = 3      'adUseClient
      objRst.CursorType = 3            'adOpenStatic
      objRst.ActiveConnection = objConn
      objRst1.CursorLocation = 3      'adUseClient
      objRst1.CursorType = 3            'adOpenStatic
      objRst1.ActiveConnection = objConn1
      objRst2.CursorLocation = 3      'adUseClient
      objRst2.CursorType = 3            'adOpenStatic
      objRst2.ActiveConnection = objConn2
      
      
      ' Open the recordset.
      objRst.Open Sql
      objRst1.Open Sql1
      objRst2.Open Sql2
            
      
      ' Set the PageSize, CacheSize, and populate the intPageCount and
      ' intRecordCount variables.
      objRst.PageSize = 4
      objrst1.PageSize =4
      objrst2.PageSize = 4
            
      
                  
      ' The cachesize property sets the number of records that will be cached
      ' locally in memory.
      objRst.CacheSize = objRst.PageSize
      intPageCount = objRst.PageCount
      intRecordCount = objRst.RecordCount
      objRst1.CacheSize = objRst1.PageSize
      intPageCount = objRst1.PageCount
      intRecordCount = objRst1.RecordCount
    objRst2.CacheSize = objRst2.PageSize
      intPageCount = objRst2.PageCount
      intRecordCount = objRst2.RecordCount

      
      
      
      
      If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
      If CInt(intPage) <= 0 Then intPage = 1
      
      
      If intRecordCount > 0 Then
            objRst.AbsolutePage AND objrst1.absolutePage and objrst2.absolutePage = intPage
            
            intStart = objRst and objRst1 and objrst2.AbsolutePosition
      

            If CInt(intPage) = CInt(intPageCount) Then
                  intFinish = intRecordCount
            Else
            response.write "no file"
                  intFinish = intStart + (objRst.PageSize - 1) And (objrst1.Pagesize - 1) AND (objrst2.Pagesize - 1)
            End if
      End If
%>



Your search for date installed on <%=strdate%>
returned <%=intRecordCount%> records.</font></h4>
<%If intRecordCount > 0 Then
      
%>
      <h4><font color="#288838">You are now viewing records
      <%=intStart%> through <%=intFinish%>.</font></h4></font>

       
       <%Response.Write "<table border=1 cellpadding=5 cellspacing=1 width=1000>"
               Response.Write "<tr bgcolor=#ccccff>"  
               Response.Write "<td align=center width =150><font face=arial size=2><b>INSTALLED_DATE</b></td>"
               Response.Write "<td align=center width =150><font face=arial size=2><b>PRODUCT</b></td>"
               Response.Write "<td align=center width =150><font face=arial size=2><b>LINER_VALUE</b></td>"
                     Response.Write "</tr>"%></font> <%
      
            
            For intRecord = 1 to objRst.PageSize
            If i MOD 2= 0 Then                               
                             Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst("installed_date") & "</font></td>"
                         Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst("PRODUCT") & "</font></td>"
                         Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst("liner_value") & "</font></td>"
                         Response.Write "<tr bgcolor=#ccccff>"  
                         Response.Write "</tr>"%></font>
                 <%    Else                                    
                          Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst("installed_date") & "</font></td>"
                          Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst("product") & "</font></td>"
                          Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst("liner_value") & "</font></td>"
                          Response.Write "<tr bgcolor=#ccccff>"
                          Response.Write "</tr>"%></font> <%
                      End If
                      i=i+1                   
                  objRst.MoveNext
                  If objRst.EOF  Then Exit for
                  Next
   
            For intRecord = 1 to objRst1.PageSize
            If i MOD 2 = 0 Then
                                         
                         Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst1("installed_date") & "</font></td>"
                         Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst1("Product") & "</font></td>"
                         Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst1("liner_value") & "</font></td>"
                                     Response.Write "<tr bgcolor=#ccccff>"
                                     Response.Write "</tr>"%></font><%                  
                         else                        
                          Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst1("installed_date") & "</font></td>"
                                      Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst1("Product") & "</font></td>"
                                      Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst1("liner_value") & "</font></td>"
                          Response.Write "<tr bgcolor=#ccccff>"
                          Response.Write "</tr>"%></font><%
                          End If
                      i=i+1                   
                  objRst1.MoveNext
                  If ObjRst1.EOF Then Exit for
                  Next

            For intRecord = 1 to objRst2.PageSize
            If i MOD 2 = 0 Then
                          Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst2("installed_date") & "</font></td>"
                                    Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst2("Product") & "</font></td>"
                        Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#66ccff'><font face=arial size=-2>" & objrst2("liner_value") & "</font></td>"
                                  Response.Write "</tr>"
                                  else      
                        Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst2("installed_date") & "</font></td>"
                        Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst2("product") & "</font></td>"
                                    Response.Write "<td align=center width='" & dynaWidth & "' bgcolor='#F7F7F7'><font face=arial size=-2>" & objrst2("liner_value") & "</font></td>"
                        Response.Write "</tr>"
                        End If
                      i=i+1                   
                  objRst2.MoveNext
                  If objRst2.EOF Then Exit for
                  Next
                  %>


      
      
      
      <%      
      If cInt(intPage) > 1 Then
      %>
         <a href="sam2.asp?NAV=<%=intPage - 1%>&date=<%=strdate%>">
         << Prev</a>
      <%End IF%>

      <%
            
            If cInt(intPage) < cInt(intPageCount) Then
      %>
         <a href="sam2.asp?NAV=<%=intPage + 1%>&date=<%=strdate%>">Next >></a>
        <%End If%>      
<%End If%>      






0
Comment
Question by:wkiest7
[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
  • 3
4 Comments
 

Author Comment

by:wkiest7
ID: 11738724
I should have added that I know this line is incorrect:
objRst.AbsolutePage AND objrst1.absolutePage and objrst2.absolutePage = intPage

but if I do just objrst.AbsolutePage = intPage then it returns the matching fields but then gives the error for the database that does not have a record. I feel like this is confusing - mostly because I am way over my head on this one! Thanks again.
0
 
LVL 6

Accepted Solution

by:
casstd earned 375 total points
ID: 11759346
Hi,

1. For this don't use like this objRst.PageSize = 4, absoulutepostion etc.,
     
recperpage = 4
pagesize = recperpage
pageno = Request("pageno")                    ' passing through querystring in previous next buttons.
if CInt(pageno) = 0 or pageno="" then
      pageno = 1                              
else
      pageno = pageno       
end if

2. For total records don't use like this pagecount, pagesize is based on the total records in 3 recordset object so you must calculate it by dividing the total no of records by 4.

objRst.CacheSize = objRst.PageSize
     intPageCount = objRst.PageCount
     intRecordCount = objRst.RecordCount
     

Use Like this for total no of records.

RecCount1 = objRst.RecordCount
RecCount2 = objRst1.RecordCount
RecCount3 = objRst2.RecordCount

TotalRecords = Cint(RecCount1+RecCount2+RecCount3)
Totalpage = Mustcalcuate from Totalrecords/4              ' nice way to doing it using vbscript.

Hope this would be helpful.
0
 

Author Comment

by:wkiest7
ID: 11761879
Thank you for your response. I am going to work on trying your suggestion now to make sure I understand exactly how it will work. Makes sense when reading it. I just do not want to close this out too soon until I am positive it works. Thanks again will send another update later today.
0
 

Author Comment

by:wkiest7
ID: 11763532
That worked perfect! Thank you so much - A++++!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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