Report based on an ADO recordset in an ActiveX script

[gr8patra]

I am calling the Crystal Reports 8.0 component from an ActiveX script inside a DTS package in a SQL Server 2000 database. That in return prints the report to a network printer. The report that I am calling is based on two database tables. Inside the script, I print one page of the report for every recordset. That works fine. Now, I need to convert the report to use an ADO recordset (TTX file) so that I use a stored procedure that would populate the recordset, as this way is more efficient than tables. When I convert the report to use the ADO recordset, it returns blank pages. If I connect to the stored procedure instead, I get the required printouts with data. My code follows. Any input?


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim crApp
Dim crReport
Dim oRS
Dim oCN
Dim sConnect
Dim sPath

Set oCN = CreateObject("ADODB.Connection&quot
Set crApp = CreateObject("CrystalRunTime.Application.8&quot
sPath = DTSGlobalVariables("InputFolder&quot.Value
sReportName = "LoanPaymentPastDueNotice.rpt"
oCN.CommandTimeout = 360
oCN.Open "driver={SQL Server};server=HQSQL;uid=sa;pwd=;database=BUSINESSOBJECTS"

sSQL = "EXEC ap_LoanPaymentPastDueNotice "
Set oRS = oCN.Execute(sSQL)

' Do While Not oRS.EOF
sPlanID = oRS.Fields("PlanID&quot
Set crReport = crApp.OpenReport(sPath & "\" & sReportName)
crReport.DiscardSavedData
crReport.RecordSelectionFormula = " {LoanPaymentPastDueNotice_ttx.PlanID} = '" & sPlanID & "'"
crReport.DisplayProgressDialog = False
crReport.PrintOut False
oRS.MoveNext
' Loop

Set crApp = Nothing
Set crReport = Nothing
oRS.Close
Set oRS = Nothing
oCn.Close
Set oCn = Nothing

Main = DTSTaskExecResult_Success
End Function

 

[synapsevampire]

I fear that you need to reopen the report from Crystal, and reset the datasource to the ADO SP, not two tables.

And since you once had 2 datasources rather than 1 (2 tables vs. 1 SP), you'll probably have to reassign each field/formula in the report referencing the datasource.

Not a painless transition, but as you know, the increased performance is worth it.

Keep in mind that a SQL Server SP can be used directly from within Crystal, so you don't need to use ADO.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[gr8patra]

Kai,
I appreciate your response. I have reopened the report and reset the datasource to the SP that resides on SQL Server. Is that what you meant? I have reset every field to the actual data source. In fact, I have deleted all fields except a couple for a test, and have reset them to point to the fields from the SP. The reason I use the ActiveX script is to print the report from SQL Server, as I actually have 7 reports that I have run in the past, in a collated order, so that the pages of the same Social Security Number get printed from every report. You can imagine that what I need printed is a letter (in portrait orientation), a report (in landscape), a letter, a report, a letter, a report and then another report. This process was working before, but I need to convert everything to use ADO recordsets and not stored procedures. That is where things stumble. I have posted the same question to other boards but I have gotten no satisfactory answers; you are actually the first one to come close to the problem. If I can provide any more info, I'd be more than happy.
Regards,
Andreas