Solved

Paging through recordsets with multiple databases

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

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 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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

21 Experts available now in Live!

Get 1:1 Help Now