Unable to return all records in ASP page 2

[FengShui1998]

I seem to have a problem with my IIS 6.0 web server where I was able to return over 3000 records from an SQL database in an ASP page. Now it will return only about 1000 records.

This appears to be a buffering problem but I could not find any parameters to change. Anyone have any ideas? This function was working prior to our upgrade so the ASP code has not changed.


Thanks,
fengshui_1998

 

[steven290]

post your code

 

[FengShui1998]

steven290,

I don't think it is the code since this was working prior to updating the IIS server. Plus I have a counter that returns the number of records and if it is less than 1165 records, the table appears on theweb page. If it goes past that number of records it doesn't work.

I also have another web site that does the same thing. I used to be able to display all 4000 records, now only about 1700 records. it appears to be the amount of data that is being display back to the browser.

fengshui_1998

 

[bslintx]

what else but the code could it be? yeah i know that's what you're asking but the only way to find out is to see code, thanks

 

[FengShui1998]

Here's the code. The count variable is just a test variable. If I set it to less than 1165 records, then the table displays. If greater, it does not display.

fenghsui_1998


' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Edit_Table(rstmp, strTable, fmt, efile, acttype, strField)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
strAlign = "<TH ALIGN=""center"">"
strFont = "<FONT style=""font-family:Helvetica;color:#FFFFFF;font-size:8.5pt"">"
strFont2 = "<FONT style=""font-family:Arial;font-size:8.5pt;color:#00008C"">"
strFont3 = "<FONT style=""font-family:Arial;font-size:8.5pt;color:GREEN;font-weight:bold"">"
strFont4 = "<FONT style=""font-family:Arial;font-size:8.5pt;color:RED;font-weight:bold"">"
strTD = "<TD NOWRAP ALIGN=""center"" style=color:#00008c>"
mso = "<TR BGCOLOR='#FFFFFF' onMouseOver=""this.style.backgroundColor='yellow'""

onMouseOut=""this.style.backgroundColor='#FFFFFF'"">"

Dim QArray
If Not rstmp.EOF Then
QArray = rstmp.GetRows()

if Fmt then Response.ContentType = "application/vnd.ms-excel"

response.write "<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1><TR BGCOLOR='#000080'>"

For I = 0 To UBound(QArray, 1)
response.write strAlign & strFont & "<B>" &Trim(rstmp.Fields(I).Name) & "</B></FONT></TH>"
Next

response.write "</TR>"
count = 0
For I = 0 To UBound(QArray, 2)
Response.Write mso
count = count + 1
if count = 1165 then exit for
For J = 0 to UBound(QArray, 1)

Value = Trim(QArray(J, I))
If Value = "" or IsNull(value) Then Value = "&nbsp"
If ucase( (rstmp.Fields(J).Name) ) = ucase(strField) then
response.write strTD & strfont2 & "<input class='btn' type='button' name='e' value='Edit'

onclick=""javascript:top.document.location='" & efile & "?Action=" & acttype & "&indx=" & value & "'""></FONT></TD>"
Else
If (ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "HWREQ") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "APPROVED") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "HWREQ") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "APPROVED") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "DELETE_APPROVED") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "DELETE_APPROVED") then
response.write strTD & strfont3 & Value & "</FONT></TD>"

Elseif (ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "NEW") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "DELETE") or _
(ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "DELETE") then
response.write strTD & strfont4 & Value & "</FONT></TD>"

Else
response.write strTD & strfont2 & Value & "</FONT></TD>"

End if
End If


Next

Response.Write "</TR>"
Next

End If
Response.Write "</Table>"
rstmp.Close
Set rstmp = Nothing

End Sub

 

[bslintx]

what happens if you do a recordset count or run a simple sql statement to select * from the table w/o the extra sub ifs/ etc..just the data itself?

 

[steven290]

try this

Sub test_table(rstmp)

      Dim QArray
      If Not rstmp.EOF Then
         QArray = rstmp.GetRows()

      End If
      rstmp.Close
      Set rstmp = Nothing
	response.write "<table>"& vbcrlf
         For I = lBound(QArray, 2) To UBound(QArray, 2)

	  response.write "<tr>"& vbcrlf
             For J = lBound(QArray, 1) to UBound(QArray, 1)
		response.write "<td>" & Trim(QArray(J, I)) & "</td>"& vbcrlf        
             Next
	  response.write "</tr>"& vbcrlf
          Next
	response.write "</table>"& vbcrlf	

 End Sub

 

[bslintx]

yeah...like that ;-) ..lol

 

[FengShui1998]

steven290,

Well damn! it displays all the records! Why would it display records in the CALL function if I limited the number of records? Maybe it is the formatting. But why did it also work in our previous version of IIS? Unless there was some subtle change.

fengshui_1998

 

[bslintx]

i imagine it had to be a subtle change because it's asp...and it's using the same asp coding as stevens..only he used the way getRows mathod is normally formatted to get all the rows and show in a table...should have written that sorry but just wanted to start w/ the simple things first...didn't want to overlook the obvious ya know? anyways good job steven, again

Brian

 

[FengShui1998]

I would have to think it was a formatting problem since I am doing a GetRows as steven290. It's certainly not an SQL problem because the record count is the same and all the data is there. It is just displaying the data to the browser. I'll try modifying your CALL routine one step at a time until it breaks or until it is successful. Thanks for your help! OR maybe I found a bug?


fengshui_1998

 

[bslintx]

gr8...good luck and if you get it to work please thank all you thought deseved credit in getting you into the right direction...a lil recognition goes a long ways;-)

v/r
Brian

 

[FengShui1998]

It doesn't like the column header portion. Don't know why yet but I'm checking it out.

fenshui_1998

 

[steven290]

i believe the problem is this, the if statements are working, but that is the problem

consider this line, just as an example

(ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "APPROVED")

look at this if statement, you are missing DELETE

 If (ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "HWREQ") or _
                       (ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "APPROVED") or _
                       (ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "HWREQ") or _
                       (ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "APPROVED") or _
                       (ucase( (rstmp.Fields(J).Name) ) = "NEWSTATUS" and value = "DELETE_APPROVED") or _
                       (ucase( (rstmp.Fields(J).Name) ) = "NEWACTCELLSVC" and value = "DELETE_APPROVED")

off the back you are missing NEWSTATUS=DELETE so those won't display, not to mention anything else you might be missing

 

[FengShui1998]

It appears to be this line:

<font style='font-family:Helvetica;color:#FFFFFF;font-size:9pt'>

When I remove it, the data displays. When I add it, no data. I also used the </font> to make sure the tag is done. ANy ideas?

fengshui_1998

 

[FengShui1998]

Hi All;

Apparently when the above line was written out, the <font> tag was writing to every cell in all the records. This did cause a buffer overload on on IIS or the way it sends out the display. I moved the format to the whole row instead of each cell and all the records are displayed.

So basically, it appears something has changed in IIS but this was a better solution any ways.

Cheers and thanks for all your help!

fengshui_1998