Combine two recordsets into temp table? 1

[meldrape]

Greetings, I have two recordsets:
computerinfo
equipment

These tables are unrelated but I need to combine them for the sake of a report. SO I've created the report with looping through two separate recordsets which works fine, except I don't know how to put in a page break. The page break thing works with one recordset using a counter for each 25 rows, inserting a header and page break but I don't know how to do it with two recordsets, so I'd like to combine them maybe in a temp table or something and run it like that. The field names are the same. Woe is me because I don't know how. Any ideas? Thanks in advance.

 

[Tarwn]

Why not simply do your select and loop with page breaks on the first table, then insert another page break, do your second select and subsequent loop with page breaks?

Personally I'm not sure what you mean by page breaks, are you simply breaking the table after a certain number of rows using an estimated page size or are you using CSS or ?

-T

 

[meldrape]

Thanks, Tarwn. Yes, that's what I meant about the pagebreaks. The pagebreak code I have now goes 25 rows then inserts the pagebreak code. It looks like this and I've used it before:

do while not details.eof
Dim iCount, iCount2
iCount2=iCount2+1
    if iCount = 25 then
        response.write "<p style=""page-break-before: always"">" & topheader 
        Response.write "<table width='100%' ><tr><th width='5%'>Line<th width='5%'>Qty"
         Response.Write "<tr><td width='5%'>" & iCount2 & "<td align=right width='5%'>" & details("pcs") 
        iCount = 0 '// Reset it to 0 for the next 25
    else
        Response.Write "<tr><td>" & iCount2 & "<td align=right>" & details("pcs") 
  end if
        iCount = iCount + 1
	
	details.movenext
	loop
	Response.Write "</table>"
	end if

But that's within one recordset loop. I don't know how to make it work with two. I don't want it to go 25 lines put in a page break then maybe only go leave 2 lines on the next page and move on to the next recordset and start paging all over again. That's why I was asking about a temp table so it's just one recordset. Does this make any sense? Many thanks for your thoughts.

 

[Tarwn]

Ah, I see now. So basically if you have 79 records in the first table then you want page 4 to show the last 4 records of the first recordset then immediately start displaying (or partialy display) the next recordset.

Why not determine where you are by looking at your counter? First move your Dim statement outside your loop. VBScript is letting you get away with it right now, but technically it should be redeclaring those variables local to each loop. Move them Dim's outside to my mind at ease
Now When you start your next loop don't reset iCount. In the example above it would still be set at 4 after the first recordset is done. Go ahead and put in a couple line breaks, start a new table and add those lines to your iCount variable and then loop through the second recordset exactly like you have through the first. Since the iCount variable will now be starting at something like 8 or 9 (given our 79 records example) then it will finish the page, reset the variable to 0, and continue on.

Example (with other changes):

'iCount is a line count, tCount is number of records
Dim iCount, tCount
Do Until details.EOF
   'increment total count
   tCount = tCount + 1

 
   If iCount mod 25 = 0 Then
      'if this is not the first time through we need to end any previous paragraph and table tags
      '   on first time through count will = 0, on subsequent times it will be 25
      If iCount = 25 Then 
         Response.Write "</table></p>"
         'since we are on page > 1, we need a page break
         Response.Write "<p style=""page-break-before: always"">" & topheader   
      Else
         'we are on page 1, no page break necessary
         Response.Write "<p>" & topheader
      End If

      'start the new table
      Response.Write "<table width='100%' ><tr><th width='5%'>Line</th><th width='5%'>Qty</th></tr>"

      iCount = 0 '// Reset it to 0 for the next 25
   End If

   'display the data row every time
   Response.Write "<tr><td width='5%'>" & iCount2 & "</td><td align=right width='5%'>" & details("pcs") & "</td></tr>"

   'increment line count + recordset
   iCount = iCount + 1
   details.MoveNext
Loop

'as long as we had data to loop through, tCount will be greater then 0 and there will be an open table and paragraph that needs to be closed.
If tCount > 0 Then Response.Write "</table></p>"

'pretty stuff
'   we want a couple line breaks before starting the next table
'   but there is no point unless we can add in the line breaks and the table header line without going past our 25 line count
If (iCount + 2 + 1) < 25 Then
   Response.Write "<br>" & vbCrLf & "<br>" & vbCrLf
   Response.Write "<p style=""page-break-before: always"">" & topheader
   Response.Write "<table width='100%' ><tr><th width='5%'>Line</th><th width='5%'>Qty</th></tr>"

   iCount = iCount + 3
Else
   'if there wasn't room to continue on this page then pretend we are starting from scratch
   iCount = 0
End If

'reset our tCount
tCount = 0

'start looping through second recordset

'----------------
'    this section copied and pasted from above w/ recordset name changed

Do Until recordset2.EOF
   
   'increment total count
   tCount = tCount + 1

   If iCount mod 25 = 0 Then
      'if this is not the first time through we need to end any previous paragraph and table tags
      '   on first time through count will = 0, on subsequent times it will be 25
      If iCount = 25 Then 
         Response.Write "</table></p>"
         'since we are on page > 1, we need a page break
         Response.Write "<p style=""page-break-before: always"">" & topheader   
      Else
         'we are on page 1, no page break necessary
         Response.Write "<p>" & topheader
      End If

      'start the new table
      Response.Write "<table width='100%' ><tr><th width='5%'>Line</th><th width='5%'>Qty</th></tr>"

      iCount = 0 '// Reset it to 0 for the next 25
   End If

   'display the data row every time
   Response.Write "<tr><td width='5%'>" & iCount2 & "</td><td align=right width='5%'>" & recordset2("pcs") & "</td></tr>"

   'increment line count + recordset
   iCount = iCount + 1
   recordset2.MoveNext
Loop

'as long as we had data to loop through, tCount will be greater then 0 and there will be an open table and paragraph that needs to be closed.
If tCount > 0 Then Response.Write "</table></p>"

Ok, several changes were made, i'll try to list them all:
1) You did not have end tags on your tr/th/td's so I added them
2) You were not ending your paragraph or table tags (except forthe last table after the loop) so I added in logic to close those tags and create less confusion for the browser
3) I moved the line that outputted the data row out of the if/then loop since this would be an identical step in every loop. No point in having it in two places, easier to edit this way.
4) I changed the if statement to check for mod 25 rather then = 25 so that there would actually be a paragraph and table tag created on the first page, i think the browser might have been guestimating that first table tag since you weren't outputting it. This makes the page less confusing to the browser and should be faster to render
5) added the if = 25 section inside the mod 25 section so we could tell whether we were starting the first page or a subsequent page and output the right paragraph tag (as well as close any outstanding tags)

There are several more minor modifications I could make, but this should solve your original dilemma as well as make the page load faster and load better across multiple browser types and versions.

-T

 

[meldrape]

Oh gosh, many thanks for your thoughts. I'll put this in place and let you know how wonderful it turns out. Thanks again.

m

 

[meldrape]

Thanks, Tarwn. Works like a charm.