Need Help with Connection

[Cheryl3D]

Hi:

Below is the code for an ASP page that I trying to use to generate a report that I created in Crystal Reports 8.5. Some of this ASP code was generated using Dreamweaver MX (i.e. MM_sar_STRING for the connection string).

Begin ASP code ---------------------------------------------

<%@ LANGUAGE="VBSCRIPT" %>
<!--#include file="../Connections/sar.asp" -->

<%
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
' Reporting directly off an ADO Recordset
' = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

''Create the ADO Database Connection:

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open MM_sar_STRING

set session("oRs") = Server.CreateObject("ADODB.Recordset")

session("oRs").ActiveConnection = oConn
'defines the ADO Connection Object the recordset will use

session("oRs").Open "QryUnionBase_View "

'Create the Crystal Reports Objects

reportname = "Pending_Suspense_Report.rpt"


' CREATE THE APPLICATION OBJECT

If Not IsObject (session("oApp")) Then
Set session("oApp") = Server.CreateObject("CrystalRuntime.Application.8.5")
End If


' CREATE THE REPORT OBJECT


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


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

On error resume next

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)


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

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

session("oRpt").DiscardSavedData
set Database = session("oRpt").Database

'Instantiates a database collection which references the database(s) used in the report.

set Tables = Database.Tables

'Instantiates a Tables collection which references the Tables of the Database object.

set Table1 = Tables.Item(1)
'Instantiates a table object which references the first table used in the report.
'In this case this table object currently refers to the ADORecordset.ttx file.

Table1.SetPrivateData 3, session("oRs")

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

viewer = Request.Form("Viewer")

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

If cstr(viewer) = "ActiveX" then
%>
<!-- #include file="SmartViewerActiveX.asp" -->
<%
ElseIf cstr(viewer) = "Netscape Plug-in" then
%>
<!-- #include file="ActiveXPluginViewer.asp" -->
<%
ElseIf cstr(viewer) = "Java using Browser JVM" then
%>
<!-- #include file="SmartViewerJava.asp" -->
<%
ElseIf cstr(viewer) = "Java using Java Plug-in" then
%>
<!-- #include file="JavaPluginViewer.asp" -->
<%
ElseIf cstr(viewer) = "HTML Frame" then
Response.Redirect("htmstart.asp")
Else
Response.Redirect("rptserver.asp")
End If

%>

---------------------------------------- End of ASP code

The problem is that every time that I try to run the report within the application, I receive the following error: “Error Occurred Reading Records: Server has not yet been opened.”?

Is there a simple way to connect to a SQL Server 2000 database using an ASP page developed on a Dreamweaver MX type site, all in order to generate a report created in Crystal Reports 8.5 via the web-based application?

Please be aware that I’m using SQL Server 2000 to connect to Crystal Reports 8.5.


Within Crystal Reports 8.5, I’m using Windows NT authentication to connect from the SQL Server 2000 database to an ASP page, that I developed in order to run the Crystal Report - .rpt

This is the kind of connectivity that I used to create the report. In the Data Explorer, I chose “More Data Sources” then under that I chose “Microsoft SQL Server” and created a trusted connection. Is this an OLE DB kind of connection?

I went to Crystal Decisions web site to trouble shoot the error message. I found a white paper entitled “Crystal Reports RDC ASP and COM Reporting” that discusses the many possible causes for the error message. After running through a few troubleshooting scenarios on the web server PC, like verifying the database via Crystal Reports Designer (Received message that “the database is up to date”), checking mismatch file versions P2sodbc.dll and Craxdrt.dll (and that checked out okay), etc.

I determined that my Windows NT authentication was not part of the problem when receiving the error message “Error Occurred Reading Records: Server has not yet been opened.”

Also, the Windows NT authentication should be okay to use in this case because the SQL Server database and the web server are on the same machine (PC).

When I create the ADO Connection and record set, I’m attempting to preview a report via the web that connects to an ADO command object.

Also, do I need to take the following code and provide the correct pathname (BOL_servername/SARTS….) for the OpenReport method?

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

If anyone can help me with the connection problem, it would be very much appreciated.

Thanks,
Cheryl