Solved

Paging through recordsets with multiple databases

Posted on 2004-08-06
4
248 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 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