How? ASP/Crystal Reports 10/Oracle 9i stored procedure

Hi,
I need to run a report with an Oracle 9i stored procedure backend, from ASP.
The report works fine.
Am I overlooking something? The fundamentals of this question have been asked many times before. However, I need a DSN-less solution.

Ideally I'd like:
1. Basic crystal report (not essential as I could generate a report manually).
2. Script to create the stored procedure and any tables.
3. ASP code to run the report (Im assuming most of the code will be in the examples such as aspxmps10, aspxmps11 & aspxmps115)

I've looked at the examples and can run the "BeginHere" example, but I cannot find anything valid which connects to a (real) database.
I'm using an Oracle Server (ie DSN-less) connection to Oracle from crystal reports.

Thx.
kev1970Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
>>1. Basic crystal report (not essential as I could generate a report manually).
You say you already have the report.  What do you need for this?

>>2. Script to create the stored procedure and any tables.
This should be done in the database.

>>3. ASP code to run the report (Im assuming most of the code will be in the examples such as aspxmps10, aspxmps11 & aspxmps115)
If the report was built against the database and you aren't changing that hten there is no need to pass connection information.  Eliminate that from the code.

mlmcc
0
dylanyeeCommented:
sorry kev1970... but what to you want to know from us? since you said the report works fine...

if you want a DSNless connection, just create an Oracle OLEDB connection using below connection string
"Provider=OraOLEDB.Oracle.1;Password=password;User ID=username;Data Source=yourOracleServicename"

dylan
0
mlmccCommented:
What data source did you use to create the report?

mlmcc
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

kev1970Author Commented:
Original problem rephrased:
My report works fine in crystal reports (ie cr developer 10 & 11) and crystal enterprise 10 and has data from an Oracle 9i stored procedure (with 5 IN parameters,and 1 OUT parameter(the REF CURSOR)).
How do I run this report from ASP?

Can someone could give me an appropriate example?
I would need, at least:
1. A crystal report (not essential as I could generate a report manually).
2. A script to create the stored procedure and any tables.
3. ASP code to run the report (Im assuming most of the code will be in the examples such as aspxmps10, aspxmps11 & aspxmps115)

0
mlmccCommented:
Again, what data source did you use to create the report?  If you used a DSN then you are stuck with using a DSN for the application.

Here are some ASP examples
http://support.businessobjects.com/communityCS/FilesAndUpdates/aspxmps10.exe.asp

mlmcc
0
kev1970Author Commented:
mlmcc,
I repeat,
"...3. ASP code to run the report (Im assuming most of the code will be in the examples such as aspxmps10, aspxmps11 & aspxmps115)

I've looked at the examples and can run the "BeginHere" example, but I cannot find anything valid which connects to a (real) database.
I'm using an Oracle Server (ie DSN-less) connection to Oracle from crystal reports..."

This problem has surprised me because I thought a lot a crystal reports/oracle users would've had the same problem and resolved it.

Which example in aspxmps10.exe do you think will help?
0
mlmccCommented:
Sorry.  Not familiar enough with the ASP methods to really help.

I suspect the

SimpleSetLogonInfo  or SimpleTableLocation

mlmcc
0
kev1970Author Commented:
Fixed.
Business objects technical support sent me a demo report using a stored proc.
I verified the support files were correct (AlwaysRequiredSteps.asp, Cleanup.asp, MoreRequiredSteps.asp, RDCrptserver10.asp & SmartViewerActiveX.asp) and removed the <FORM> tag from my report (as it produced a blank page).

My code amounted to:
'==================================================================
'==================================================================
reportname = "Product Throughput.rpt"
'==================================================================
' ALWAYS REQUIRED STEPS
'
' Include the file AlwaysRequiredSteps.asp which contains the code    
' for steps:
'   - create the application object
'   - create the report object
'   - open the report
'==================================================================
%>                                                                    
<!-- #include file="AlwaysRequiredSteps.asp" -->                      

<%
'=======================================================================
'   WORKING WITH A REPORT BASED OFF OF A PARAMETERIZED STORED PROCEDURE
'=======================================================================
Set mainReportTableCollection = Session("oRpt").Database.Tables

For Each mnTable in mainReportTableCollection
  With mnTable.ConnectionProperties
   .Item("Server") = "BPROJECT" //provide the service name of Oracle server
   .Item("Password") = "testpass" //provide the password
   .Item("User ID") = "test" //provide the user id
  End With
Next

'Set Parameters
'The following section shows setting single valued parameters of various data types.
Session("oRpt").ParameterFields.GetItemByName("Assembly Name").AddCurrentValue(CStr("Assembly1"))
Session("oRpt").ParameterFields.GetItemByName("Product").AddCurrentValue(CStr("TestUnit1"))
Session("oRpt").ParameterFields.GetItemByName("strBankHolidays").AddCurrentValue(CStr(""))
Session("oRpt").ParameterFields.GetItemByName("strSaturdays").AddCurrentValue(CStr(""))
Session("oRpt").ParameterFields.GetItemByName("strSundays").AddCurrentValue(CStr(""))
Session("oRpt").ParameterFields.GetItemByName("strDelayInfo").AddCurrentValue(CStr(""))
'==================================================================
'==================================================================
'==================================================================
'
'  MORE ALWAYS REQUIRED STEPS
'
'  Include the file MoreRequiredSteps.asp which contains the code
'  for the steps:
'   - retreive the records                                        
'   - create the page engine                                      
'   - create the smart viewer and point it to rptserver.asp
'
'==================================================================
'
%>
<!-- #include file="MoreRequiredSteps.asp" -->
<%
'=============================================================================
'  DISPLAY THE REPORT
'   - display the report using a smart viewer
'
' Include one of the Smart Viewers.
'  - Report Viewer for ActiveX =   SmartViewerActiveX.asp
'  - Report Viewer for Java Using Java Plugin      =   JavaPluginViewer.asp
'=============================================================================
%>
<!-- #include file="SmartViewerActiveX.asp" -->        
<%
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Does that solve the problem?

mlmcc
0
kev1970Author Commented:
Yes, although altered slightly, for security reasons, the above code runs my crystal 10 report in IE6.
I'm, also, running the crystal reports 10 developer edition & IIS on my machine (W2k).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.