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"
Set crApp = CreateObject("CrystalRunTime.Application.8"
sPath = DTSGlobalVariables("InputFolder".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"
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
'********************************************************
P.S. 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. If I can provide any more info, I'd be more than happy.
Regards,
gr8patra
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"
Set crApp = CreateObject("CrystalRunTime.Application.8"
sPath = DTSGlobalVariables("InputFolder"
.ValuesReportName = "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"
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
'********************************************************
P.S. 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. If I can provide any more info, I'd be more than happy.
Regards,
gr8patra