Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Paging through recordsets with multiple databases

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
wkiest7
Asked:
wkiest7
  • 3
1 Solution
 
wkiest7Author Commented:
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
 
casstdCommented:
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
 
wkiest7Author Commented:
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
 
wkiest7Author Commented:
That worked perfect! Thank you so much - A++++!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now