| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-08-03, 16:02
|
|
Registered User
|
|
Join Date: May 2002
Posts: 10
|
|
|
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.
|
|
10-09-03, 00:08
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Pune
Posts: 59
|
|
try using command object with parameter.
cyrus
|
|
10-09-03, 08:51
|
|
Registered User
|
|
Join Date: May 2002
Posts: 10
|
|
|
|
How do I do that, Cyrus? Can you provide any code examples please?
Quote:
Originally posted by cyrus
try using command object with parameter.
cyrus
|
|
|
10-09-03, 16:29
|
|
Useless...
|
|
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.
|
|
10-09-03, 16:41
|
|
Registered User
|
|
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?
Quote:
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
%>
|
|
|
10-09-03, 16:54
|
|
Useless...
|
|
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.
|
|
10-09-03, 17:04
|
|
Registered User
|
|
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.
Quote:
Originally posted by Seppuku
Do you need all 30,000+ on a single ASP page?
|
|
|
10-09-03, 17:33
|
|
Useless...
|
|
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.
|
|
10-10-03, 01:02
|
|
Registered User
|
|
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-03, 08:52
|
|
Registered User
|
|
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
Quote:
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
|
|
|
10-10-03, 11:52
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
Quote:
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.
|
|
10-10-03, 12:22
|
|
Registered User
|
|
Join Date: May 2002
Posts: 10
|
|
Requesting only the ones needed. Just 10, I mean, not the 20.
Quote:
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?
|
|
|
10-10-03, 12:24
|
|
Useless...
|
|
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.
|
|
10-14-03, 00:54
|
|
Registered User
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
RecordSet.Open error...
