[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


GetRows in ASP

Posted on 2009-04-23
Medium Priority
Last Modified: 2012-05-06
I am trying get all the results to an Excel file. When i pass the dates to my SP the server hangs up. Because there are about 40000 to 60000 thousand rows. I have tried increasing the script time stuff and it doesn't work. I am creating a recordset and then geting the info from it. There are so many records that the server hangs up. So i did some research and came out with getrows stuff in ASP. Its been a whie i have done anything in ASP.
This is the old code how it is being done.
The GetRecordset passes the sql, database name , and the recordset. This will always remain the same and not change.
RecToTable Function makes the table and all the fields. I need to include RECTOTABLE function always in getrows.
This is the OLD CODE
Set rsGet=Server.CreateObject("ADODB.recordset")
If GetRecordset(sSQL, rsGet, eval(vDataBase)) Then
if not rsGet.EOF and not rsGet.BOF Then
      vTable = RecToTable (rsGet)
       Response.Write "There are no results to display"
end if
  set rsGet=nothing
end if
if vShowAsSpreadsheet=1 then Response.ContentType = "application/vnd.ms-excel"
Response.write vTable
end if

Function RecToTable (objRec)

'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205

      Dim strT                  ' table html string
      Dim fldF                  ' current field object
      Dim strValue                  ' field value

      ' build the table header
      strT = "<TABLE BORDER=1>" & _
               "<TR ALIGN=CENTER>"

      ' each field as a table column name
      For Each fldF In objRec.Fields
            strT = strT & "<TD nowrap>" & fldF.Name & "</TD>"
      strT = strT & "</TR>"

      ' now build the rows
      While Not objRec.EOF
            strT = strT & "<TR ALIGN=CENTER>"

            ' format each field according to its type
            For Each fldF in objRec.Fields
                  strValue = fldF.Value

            Select Case fldF.Type
                  Case adDBDate, adDBTimeStamp, adDBTime, adDate
                        If Not IsNull(strValue) Then
                              strValue = FormatDateTime (strValue, 2)
                        End If

                  Case adBoolean
                        If strValue = "True" Then
                              strValue = "Yes"
                              strValue = "No"
                        End If

                  Case adCurrency
                        If Not IsNull(strValue) Then
                              strValue = FormatCurrency(strValue)
                        End If

                  Case adBigInt, adDecimal, adDouble, adNumeric, adSingle, _
                         adSmallInt, adTinyInt, adUnsignedBigInt, adUnsignedInt, _
                         adUnsignedSmallInt, adUnsignedTinyInt
                        If Not IsNull(strValue) Then
                              strValue = FormatNumber (strValue)
                        End If

                  End Select
                  strT = strT & "<TD nowrap>" & NullToNBSP(strValue) & "</TD>"
            strT = strT & "</TR>"
      strT = strT & "</TABLE>"

      ' and finally return the table
      RecToTable = strT

End Function

This is how i did
Set rsGet=Server.CreateObject("ADODB.recordset")
If GetRecordset(sSQL, rsGet, eval(vDataBase)) Then
if rsGet.EOF then
Response.Write "There are no results to display"
arrResultSet = rsget.GetRows()
end if
set rsGet=nothing
iRowNumber = ubound(arrResultSet,2)
' This was only for test prupose
For iCounter= 0 to iRowNumber
Response.Write(arrResultSet(1,iCounter) & ", " & arrResultSet(2,iCounter) & " - " & arrResultSet(0,iCounter) & "")
end if
 I need to inlude the RecToTable Function  to getrows.
Any Help will be apperciated.

Question by:soorraj
  • 2
  • 2

Expert Comment

ID: 24221471
Is the problem that the query times out or the ASP page times out?  Unless the query is fairly complex, 40,000 to 60,000 rows isn't much when you really think about it.  What is your db backend?  You may want to change the script to use the adodb.command object that you can change the timeout in the ASP page for the query.  Only trouble then is to make sure your page doesn't time out before the query is done.  If indeed a query timeout and since you said it was using an SP, you may need to take a look at that and try to tweak some more speed out of it.

Author Comment

ID: 24226132
The SP doesn't timeout. The SP gives the results in 10 to 30 sec.The ASP code was written 7 to 8 yrs back by someone else. Same code is used for all the reports. it passes the SP and the database name etc in the code for different reports. It is smart enough to know which report to pull out. GetRecordset(sSQL, rsGet, eval(vDataBase)). Then RecToTable function passes the recordset to build the rows and fields. I cannot change it the command obj. BY gettingrows i thought it will hit the database once instead of hitting it everytime with the old code. I was curious how can i pass the RecToTable function in the gettingrows.

Accepted Solution

dan_neal earned 900 total points
ID: 24226275
If i remember the getrows function correctly, once you have your results from the db, bu simply calling arrayvar = recordsetobj.getrows.  However, it is just the data from the recordset (field values) and you lose all the other properties of the recordset object such as accessing the field names and type that the RecToTable function is using so that function would have to be redone in order to use getrows.  
If you really want to use this, I would suggest at least 3 arrays be created.
  1. Array containing field names
  2. Array containing field types
  3. Array created by getrows function
The first 2 arrays would have to be created prior to closing the recordset.  I could work up some examples of what I would do in this situation if you need.

Author Comment

ID: 24226552
So the rectotable function needs to be rewritten.  Could u setup some examples. That would be really apperciated. If Array containing field names , field types is made will that be used by other reports or  only one report. Function RecToTable (objRec) used by all the reports. Array should be smart enough to be used by all the reports for field names and field values the way Function RecToTable (objRec) is being used.  i hope i make sense. :)

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 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