Results 1 to 14 of 14
  1. #1
    Join Date
    May 2002
    Posts
    10

    Unanswered: RecordSet.Open error...

    Hi,

    Am having an error with the following piece of code that connects to an Oracle Database using Microsoft ODBC drivers and retrieves a set of records:

    Set objRecordSet = Server.CreateObject("ADODB.RecordSet")
    objRecordSet.Open strSQL, cnnOraDatabase

    The SQL statement (strSQL) retrieves records from a table. The cnnOraDatabase contains correct connection string, et all......

    The issue is:
    The .Open statement fails when the number of records exceeds 25,000 or so. Below that number, it fetches the records correctly. The error I receive is:

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array ORA-06512: at line 1

    So..... can anyone help on how to resolve this issue? Thanks...

    - G.

  2. #2
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    try using command object with parameter.

    cyrus

  3. #3
    Join Date
    May 2002
    Posts
    10
    How do I do that, Cyrus? Can you provide any code examples please?


    Originally posted by cyrus
    try using command object with parameter.

    cyrus

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Code:
    <%
      Dim objDataCmd, objConn, objRS
      Dim sConnString, sSQL
    
      Set objDataCmd = Server.CreateObject("ADODB.Command")
      Set objConn = Server.CreateObject("ADODB.Connection")
      Set objRS = Server.Create Object("ADODB.Recordset")
    
      sConnString = "Insert connection string here"
    
      objConn.Open sConnString
      objDataCmd.ActiveConnection = objConn
    
      sSQL = "Insert SQL or Stored Procedure here"
      objDataCmd.CommandText = sSQL
      Set objRS = objDataCmd.Execute( , , adCmdText)
    
      '''Work the results'''
    
      objRS.Close
      objConn.Close
      
      Set objRS = Nothing
      Set objConn = Nothing
      Set objDataCmd = Nothing
    %>
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    May 2002
    Posts
    10
    Well, none of the solutions worked! :-( Beyond a certain number of records (30,000+), the program generates an "Overflow" error message. So once again --- how do I do it?


    Originally posted by Seppuku
    Code:
    <%
      Dim objDataCmd, objConn, objRS
      Dim sConnString, sSQL
    
      Set objDataCmd = Server.CreateObject("ADODB.Command")
      Set objConn = Server.CreateObject("ADODB.Connection")
      Set objRS = Server.Create Object("ADODB.Recordset")
    
      sConnString = "Insert connection string here"
    
      objConn.Open sConnString
      objDataCmd.ActiveConnection = objConn
    
      sSQL = "Insert SQL or Stored Procedure here"
      objDataCmd.CommandText = sSQL
      Set objRS = objDataCmd.Execute( , , adCmdText)
    
      '''Work the results'''
    
      objRS.Close
      objConn.Close
      
      Set objRS = Nothing
      Set objConn = Nothing
      Set objDataCmd = Nothing
    %>

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Do you need all 30,000+ on a single ASP page?
    That which does not kill me postpones the inevitable.

  7. #7
    Join Date
    May 2002
    Posts
    10
    Yes.... the reason being they are not all being displayed on that ASP page. I am actually sending a "Content-type: application/vnd.ms-excel" header and dumping the records so that all records are dumped to the user as an Excel spreadsheet. The user needs to download the data in Excel format. That's the main issue.

    I am open to any other ways in which I can achieve the same result, in ASP, of course. But nothing that uses proprietary components.

    Originally posted by Seppuku
    Do you need all 30,000+ on a single ASP page?

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Try setting the recordset's "PageSize" property, then use "PageCount" to find out how many pages are available, then loop through the total page count, setting the active page with "AbsolutePage".

    Code:
    '
    'Do call to DB to retrieve recordset
    '
    oRS.PageSize = 1000
    
    Dim iCounter, iRowNum
    For iCounter = 1 To oRS.PageCount
      oRS.AbsolutePage = iCounter
      While NOT oRS.EOF
    
        'Get the row number (if you need it)
        iRowNum = oRS.AbsolutePosition
    
        'Read RS data
    
        oRS.MoveNext
      WEnd
    Next
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Hello ,
    Create a csv file instead.


    Response.Buffer = true
    Response.AddHeader("Content-Disposition", "filename=Export.csv")
    Response.ContentType = "text/csv"

    Response.Write(Chr(34) & "Distributor" & Chr(34) & ",")
    Response.Write(Chr(34) & "Customer Number" & Chr(34) & ",")
    while (conditon)
    Response.Write(Chr(34) & Your rs field & Chr(34) & ",")
    end while

    I am passing more than 30,000 record using the CSv file.

    CSv file opens as a excel document.
    Cyrus

  10. #10
    Join Date
    May 2002
    Posts
    10
    Seppuku/Cyrus,

    No.... what you both are saying is NOT possible. Reason: the program is failing with an "Overflow" error right at the Command.Execute or RecordSet.Open statement!! The program halts right there and it does not proceed with executing any further statements....... Hence, cant loop through the pages or anything.

    :-) So.... what next?

    - GK

    Originally posted by cyrus
    Hello ,
    Create a csv file instead.


    Response.Buffer = true
    Response.AddHeader("Content-Disposition", "filename=Export.csv")
    Response.ContentType = "text/csv"

    Response.Write(Chr(34) & "Distributor" & Chr(34) & ",")
    Response.Write(Chr(34) & "Customer Number" & Chr(34) & ",")
    while (conditon)
    Response.Write(Chr(34) & Your rs field & Chr(34) & ",")
    end while

    I am passing more than 30,000 record using the CSv file.

    CSv file opens as a excel document.
    Cyrus

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by gopalkamat
    Seppuku/Cyrus,

    No.... what you both are saying is NOT possible. Reason: the program is failing with an "Overflow" error right at the Command.Execute or RecordSet.Open statement!! The program halts right there and it does not proceed with executing any further statements....... Hence, cant loop through the pages or anything.

    :-) So.... what next?

    - GK
    Sorry.. sorry... is your recordset returning only the required fields? If your table has 20 fields, but you only need 10 of them to create the Excel file, are you requesting all 20 fields (with a "*") or only the 10 you need?
    That which does not kill me postpones the inevitable.

  12. #12
    Join Date
    May 2002
    Posts
    10
    Requesting only the ones needed. Just 10, I mean, not the 20.

    Originally posted by Seppuku
    Sorry.. sorry... is your recordset returning only the required fields? If your table has 20 fields, but you only need 10 of them to create the Excel file, are you requesting all 20 fields (with a "*") or only the 10 you need?

  13. #13
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Could it be that you need to set the PageSize property in the RS BEFORE you do the .Open?
    That which does not kill me postpones the inevitable.

  14. #14
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    can u do a trial.
    try running the same thing in VB.
    Most of the time if i am facing a problem in ASP
    I use the same code in VB and try to find the bug.

    I know its not practical but some times u have to go out of the way and do the things

    I don't understand why r u getting overflow error for a recordset open!!!!!
    if possible show us ur code

    may be we will try to run same code!!!!!!!!

    cyrus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •