GetRows in ASP

Posted on 2009-04-23
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/"
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
    LVL 9

    Expert Comment

    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.
    LVL 1

    Author Comment

    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.
    LVL 9

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    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…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
    Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now