Need urgent help on Crystal reports

Hi,
I have 2-3 issues on generating the crystal report at run time using ASP:
1)I am facing difficulty in generating the crystal report connecting to oracle database.connecting to sql and access database are fine

Call Tables.Add(cstr(TableName), , , 1, "PDSODBC.DLL", cstr(dsn), "ODBC - Xtreme Sample Database", , cstr(UserName), cstr(Password))
fn works well for access and sql but not oracle

2)I am not able to generate a report based on a recordset
3)Do u know how to add a chart at run time?

here is the code that I have tried.
Thanks in advance
chan




<%
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' Creating a new report using unbound fields
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

'
' CONCEPT:
'
' This application is designed to demonstrate how to create a new report in an ASP
' application. This ASP uses the concept of "Unbound Fields" where you can add a field
' to a report at runtime.

'===================================================================================
'Create the Crystal Reports Objects
'===================================================================================
'
'You will notice that the Crystal Reports objects are scoped as session variables.
'This is because the page on demand processing is performed by a prewritten
'ASP page called "rptserver.asp". In order to allow rptserver.asp easy access
'to the Crystal Report objects, we scope them as session variables. That way
'any ASP page running in this session, including rptserver.asp, can use them.

reportname = Request.Form("ReportName")
GroupField = "{" & Request.Form("GroupField") & "}"
SortDirection = Request.Form("SortDirection")
FirstField = Request.Form("FirstField")
SecondField = Request.Form("SecondField")
ThirdField = Request.Form("ThirdField")

'This line creates a string variable called reportname that we will use to pass
'the Crystal Report filename (.rpt file) to the OpenReport method.
'To re-use this code for your application you would change the name of the report
'so as to reference your report file.

' CREATE THE APPLICATION OBJECT
If Not IsObject (session("oApp")) Then
Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")
End If

'This "if/end if" structure is used to create the Crystal Reports Application
'object only once per session. Creating the application object - session("oApp")
'loads the Crystal Report Design Component automation server (craxdrt.dll) into memory.
'
'We create it as a session variable in order to use it for the duration of the
'ASP session. This is to elimainate the overhead of loading and unloading the
'craxdrt.dll in and out of memory. Once the application object is created in
'memory for this session, you can run many reports without having to recreate it.

' CREATE THE REPORT OBJECT
'
'The Report object is created by calling the Application object's OpenReport method.

Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend

'This "While/Wend" loop is used to determine the physical path (eg: C:\) to the
'Crystal Report file by translating the URL virtual path (eg: http://Domain/Dir)

'OPEN THE REPORT (but destroy any previous one first)

If IsObject(session("oRpt")) then
Set session("oRpt") = nothing
End if

On error resume next

Set session("oRpt") = session("oApp").NewReport
'This line uses the "PATH" and "reportname" variables to reference the Crystal
'Report file, and open it up for processing.

If Err.Number <> 0 Then
Response.Write "Error Occurred creating Report Object: " & Err.Description
Set Session("oRpt") = nothing
Set Session("oApp") = nothing
Session.Abandon
Response.End
End If

'This On error resume next block checks for any errors on creating the report object
'we are specifically trapping for an error if we try to surpass the maximum concurrent
'users defined by the license agreement. By destroying the application and report objects
'on failure we ensure that this client session will be able to run reports when a license is free

'
'Notice that we do not create the report object only once. This is because
'within an ASP session, you may want to process more than one report. The
'rptserver.asp component will only process a report object named session("oRpt").
'Therefor, if you wish to process more than one report in an ASP session, you
'must open that report by creating a new session("oRpt") object.

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False

'These lines disable the Error reporting mechanism included the built into the
'Crystal Report Design Component automation server (craxdrt.dll).
'This is done for two reasons:
'
'1. The print engine is executed on the Web Server, so any error messages
' will be displayed there. If an error is reported on the web server, the
' print engine will stop processing and you application will "hang".
'
'2. This ASP page and rptserver.asp have some error handling logic desinged
' to trap any non-fatal errors (such as failed database connectivity) and
' display them to the client browser.
'
'**IMPORTANT** Even though we disable the extended error messaging of the engine
'fatal errors can cause an error dialog to be displayed on the Web Server machine.
'For this reason we reccomend that you set the "Allow Service to Interact with Desktop"
'option on the "World Wide Web Publishing" service (IIS service). That way if your ASP
'application freezes you will be able to view the error dialog (if one is displayed).

'====================================================================================
' Add Databases to the Report
'====================================================================================

Set Database = session("oRpt").Database
'Instantiate the Database Collection


Dim UserName,Password,dsn,TableName

UserName = Request.Form("UserName")
Password = Request.Form("Password")
dsn = Request.Form("DSN")
TableName = Request.Form("TableName")

set conn=Server.CreateObject("ADODB.Connection")
connStr = "DSN="& dsn &"; UID="& UserName &";PassWord="& Password &""

conn.ConnectionString = connStr
conn.Open "","","",adConnectUnspecified
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic

set rs = Server.CreateObject("ADODB.recordset")



rs.Open "Select * from " & TableName, conn

Database.SetDataSource rs




Set Tables = Database.Tables

'call Tables.Add "", , rs, , "p2smon.dll"
'Instantiate the Tables Collection



Call Tables.Add(cstr(TableName), , , 1, "PDSODBC.DLL", cstr(dsn), "ODBC - Xtreme Sample Database", , cstr(UserName), cstr(Password))
'This line adds the Customer Table from the Xtreme.mdb database
'(referenced through the "Xtreme Sample Database" ODBC Datasource Name

Set FirstTable = Tables.Item(1)

'FirstTable.SetPrivateData 3, rs
'Instantiate the First Table in the Tables Collection (only using one table in the report)
Set Fields = FirstTable.Fields
'Instantiate the Fields Collection for the table

Set GroupFieldIs = Fields.Item(cint(1))
'This loop iterates through each field in the table until the grouping field is found.
'We compare the field's name to the name of the field entered in the HTML page.
'Once we match the selected field with a field from the database we instantiate that field
'as an object that we pass (as the second parameter) to the AddGroup method



intsortdirection = 1
'This case statement compares the sort direction entered by the user

session("oRpt").AddGroup 0, GroupFieldIs, 14, intsortdirection
'Add the group to the report using the GroupFieldIs object as the grouped field.

'Add the Group Field to the Group Header section
Counter=1
iNumber=2000

For i = 1 to Fields.Count

test=Fields.Item(cint(i)).Name
session("oRpt").Sections.Item("D").AddUnboundFieldObject 10, cint(iNumber), 0
iNumber=iNumber+1000
Set FieldObject = session("oRpt").Sections.Item("D").ReportObjects.Item(cint(Counter))
FieldObject.SetUnboundFieldSource cstr(test)
Counter=Counter+1
iNumber=iNumber+1000
next

With session("oRpt").Sections(1)
'.AddSpecialVarFieldObject crSVTReportTitle, 2300, 100
End With


With session("oRpt").Sections(2)
Set txt1Obj = .AddTextObject("cellid", 2000, 1800)
txt1Obj.Font.Bold = True
txt1Obj.Font.Size = 8
Set txt2Obj = .AddTextObject("celltype", 4000, 1800)
txt2Obj.Font.Bold = True
txt2Obj.Font.Size = 9
Set txt3Obj = .AddTextObject("driverid", 6000, 1800)
txt3Obj.Font.Bold = True
txt3Obj.Font.Size = 9

Set txt3Obj = .AddTextObject("platenum", 8000, 1800)
txt3Obj.Font.Bold = True
txt3Obj.Font.Size = 9
End With


With session("oRpt").Sections(3)

'Set ln1Obj = .AddLineObject(2500, 0, 2500, 10)
'ln1Obj.LineThickness = 2
'ln1Obj.ExtendToBottomOfSection = True
'Set ln2obj = .AddLineObject(5000, 0, 5000, 10)
'ln2obj.LineThickness = 2
'ln2obj.ExtendToBottomOfSection = True
'Set ln3Obj = .AddLineObject(0, 10, 6500, 10)
'ln3Obj.LineThickness = 2
'Set ln5Obj = .AddLineObject(0, 0, 0, 10)
'ln5Obj.LineThickness = 2
'ln5Obj.ExtendToBottomOfSection = True
'Set ln6Obj = .AddLineObject(6500, 0, 6500, 10)
'ln6Obj.LineThickness = 2
'ln6Obj.ExtendToBottomOfSection = True
End With


With session("oRpt").Sections.Item(4)
' Set ln4Obj = .AddLineObject(0, 0, 8500, 0)
' ln4Obj.LineThickness = 8
End With


With pRpt.Sections.Item(5)
'.AddSpecialVarFieldObject crSVTDataDate, 30, 0
'.AddSpecialVarFieldObject crSVTDataTime, 10, 200
End With




'Each Case statement compares the values for fields entered by the user. Once we know which
'field is to be added, in which order, we then call the AddUnboundField method to add an
'an unbound field object for the field. An unbound field is basically a "container" field
'that we can give the value of a database field. Once the unbound field has been added to
'the report we then create a field object from the unbound field and finnaly assign
'the unbpund field a database field value
'selected. Once the field object has been created



'====================================================================================
' Retrieve the Records and Create the "Page on Demand" Engine Object
'====================================================================================




session("oRpt").Save Path & "\newreports\" & reportname

'====================================================================================
' Retrieve the Records and Create the "Page on Demand" Engine Object
'====================================================================================

On Error Resume Next
session("oRpt").ReadRecords

If Err.Number <> 0 Then
Response.Write "Error Occurred Reading Records: " & Err.Description
Set Session("oRpt") = nothing
Set Session("oApp") = nothing
Session.Abandon
Response.End
Else
If IsObject(session("oPageEngine")) Then
set session("oPageEngine") = nothing
End If
set session("oPageEngine") = session("oRpt").PageEngine
End If

' INSTANTIATE THE CRYSTAL REPORTS SMART VIEWER
'
'When using the Crystal Reports automation server in an ASP environment, we use
'the same page on demand "Smart Viewers" used with the Crystal Web Report Server.
'The are four Crystal Reports Smart Viewers:
'
'1. ActiveX Smart Viewer
'2. Java Smart Viewer
'3. HTML Frame Smart Viewer
'4. HTML Page Smart Viewer
'
'The Smart Viewer that you use will based on the browser's display capablities.
'For Example, you would not want to instantiate the Java viewer if the browser
'did not support Java applets. For purposes on this demo, we have chosen to
'define a viewer. You can through code determine the support capabilities of
'the requesting browser. However that functionality is inherent in the Crystal
'Reports automation server and is beyond the scope of this demonstration app.
'
'We have chosen to leverage the server side include functionality of ASP
'for simplicity sake. So you can use the SmartViewer*.asp files to instantiate
'the smart viewer that you wish to send to the browser. Simply replace the line
'below with the Smart Viewer asp file you wish to use.
'
'The choices are SmartViewerActiveX.asp, SmartViewerJave.asp,
'SmartViewerHTMLFrame.asp, and SmartViewerHTMLPAge.asp.
'Note that to use this include you must have the appropriate .asp file in the
'same virtual directory as the main ASP page.
'
'*NOTE* For SmartViewerHTMLFrame and SmartViewerHTMLPage, you must also have
'the files framepage.asp and toolbar.asp in your virtual directory.

viewer = "ActiveX"

'This line collects the value passed for the viewer to be used, and stores
'it in the "viewer" variable.

If cstr(viewer) = "ActiveX" then
%>

<%
ElseIf cstr(viewer) = "HTML Frame" then
Response.Redirect("htmstart.asp")
Else
Response.Redirect("rptserver.asp")
End If
'The above If/Then/Else structure is designed to test the value of the "viewer" varaible
'and based on that value, send down the appropriate Crystal Smart Viewer.
%>

' INSTANTIATE THE CRYSTAL REPORTS SMART VIEWER
'
'When using the Crystal Reports automation server in an ASP environment, we use
'the same page on demand "Smart Viewers" used with the Crystal Web Report Server.
'The are four Crystal Reports Smart Viewers:
'
'1. ActiveX Smart Viewer
'2. Java Smart Viewer
'3. HTML Frame Smart Viewer
'4. HTML Page Smart Viewer
'
'The Smart Viewer that you use will based on the browser's display capablities.
'For Example, you would not want to instantiate the Java viewer if the browser
'did not support Java applets. For purposes on this demo, we have chosen to
'define a viewer. You can through code determine the support capabilities of
'the requesting browser. However that functionality is inherent in the Crystal
'Reports automation server and is beyond the scope of this demonstration app.
'
'We have chosen to leverage the server side include functionality of ASP
'for simplicity sake. So you can use the SmartViewer*.asp files to instantiate
'the smart viewer that you wish to send to the browser. Simply replace the line
'below with the Smart Viewer asp file you wish to use.
'
'The choices are SmartViewerActiveX.asp, SmartViewerJave.asp,
'SmartViewerHTMLFrame.asp, and SmartViewerHTMLPAge.asp.
'Note that to use this include you must have the appropriate .asp file in the
'same virtual directory as the main ASP page.
'
'*NOTE* For SmartViewerHTMLFrame and SmartViewerHTMLPage, you must also have
'the files framepage.asp and toolbar.asp in your virtual directory.



'This line collects the value passed for the viewer to be used, and stores
'it in the "viewer" variable.


'The above If/Then/Else structure is designed to test the value of the "viewer" varaible
'and based on that value, send down the appropriate Crystal Smart Viewer.
%>