I am using Visual Basic 6 front end with SQL server 2000 backend. I'm using Crystal Reports 10 for my external reports. When I run my report, there is either minimal data or no data in the report. If I get out of the vb program and double click on the external report, the data is there. Can someone tell me why I'm not seeing the data in the report. Here is my code to execute the crystal report.
Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report
Dim sql As String
Dim m_Dataconn As ADODB.Connection
Private Sub Form_Load()
On Error GoTo Error_Handler
Const NAME_ROUTINE As String = "cmdGetReport_Click"
g_oEventManager.Push NAME_MODULE & "-" & NAME_ROUTINE
Dim StrDataSelection As String
Dim logon As Integer
If getdates Then
Select Case Reportname
Case MW3
Set m_Dataconn = getDataConnection()
sql = "if exists (select * from sysobjects where id = object_id(N'[vw_Well3_12]') and OBJECTPROPERTY(id, N'IsView') = 1)" & _
" drop view [vw_Well3_12]"
m_Dataconn.Execute sql
sql = "create view vw_Well3_12 as select * from vw_Well3_1 where samplingdate >='" & FromDate & "' and samplingdate <='" & ToDate & "'"
m_Dataconn.Execute sql
sql = "if exists (select * from sysobjects where id = object_id(N'[vw_Well3]') and OBJECTPROPERTY(id, N'IsView') = 1)" & _
" drop view [vw_Well3]"
m_Dataconn.Execute sql
sql = "CREATE VIEW vw_Well3 AS " & _
"SELECT tbl_Parameter2_GW.Storet AS STORETS, vw_well3_12.SamplingDate, " & _
" vw_well3_12.AnalysisDate, vw_well3_12.Units, vw_well3_12.ConcentrationReported, " & _
" vw_well3_12.Comments, vw_well3_12.DL, vw_well3_12.AQL, vw_well3_12.sMIN, " & _
" vw_well3_12.sMAX, vw_well3_12.ActionLevel_MW3, vw_well3_12.Method, " & _
" vw_well3_12.Parameter2_GW, vw_FetchReportnamewiselab_well3_0.DatePrepared, " & _
" vw_FetchReportnamewiselab_well3_0.ReportingPeriod,vw_FetchReportnamewiselab_well3_0.LabID , " & _
" vw_FetchReportnamewiselab_well3_0.LabName FROM tbl_Parameter2_GW LEFT " & _
" OUTER JOIN vw_well3_12 ON tbl_Parameter2_GW.Sort = vw_well3_12.Sort " & _
" CROSS JOIN vw_FetchReportnamewiselab_well3_0 " & _
" where samplingdate >='" & FromDate & "' and samplingdate <='" & ToDate & "'"
m_Dataconn.Execute sql
Set Report = Appl.OpenReport(App.Path & "\rptMW3.rpt")
Set objCPProperties = Report.Database.Tables(1).ConnectionProperties
objCPProperties.DeleteAll
objCPProperties.Add "Provider", "SQLOLEDB.1"
objCPProperties.Add "Integrated Security", "SSPI"
objCPProperties.Add "Data Source", "seims"
objCPProperties.Add "Initial Catalog", "smrfs"
For Each objDBTable In Report.Database.Tables
objDBTable.Location = objDBTable.Name 'Report.Database.Tables(1).Name 'getDataConnection()
Next
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
CRViewer91.Refresh
Dim Appl As New CRAXDRT.Application
Dim Report As New CRAXDRT.Report
Dim sql As String
Dim m_Dataconn As ADODB.Connection
Private Sub Form_Load()
On Error GoTo Error_Handler
Const NAME_ROUTINE As String = "cmdGetReport_Click"
g_oEventManager.Push NAME_MODULE & "-" & NAME_ROUTINE
Dim StrDataSelection As String
Dim logon As Integer
If getdates Then
Select Case Reportname
Case MW3
Set m_Dataconn = getDataConnection()
sql = "if exists (select * from sysobjects where id = object_id(N'[vw_Well3_12]') and OBJECTPROPERTY(id, N'IsView') = 1)" & _
" drop view [vw_Well3_12]"
m_Dataconn.Execute sql
sql = "create view vw_Well3_12 as select * from vw_Well3_1 where samplingdate >='" & FromDate & "' and samplingdate <='" & ToDate & "'"
m_Dataconn.Execute sql
sql = "if exists (select * from sysobjects where id = object_id(N'[vw_Well3]') and OBJECTPROPERTY(id, N'IsView') = 1)" & _
" drop view [vw_Well3]"
m_Dataconn.Execute sql
sql = "CREATE VIEW vw_Well3 AS " & _
"SELECT tbl_Parameter2_GW.Storet AS STORETS, vw_well3_12.SamplingDate, " & _
" vw_well3_12.AnalysisDate, vw_well3_12.Units, vw_well3_12.ConcentrationReported, " & _
" vw_well3_12.Comments, vw_well3_12.DL, vw_well3_12.AQL, vw_well3_12.sMIN, " & _
" vw_well3_12.sMAX, vw_well3_12.ActionLevel_MW3, vw_well3_12.Method, " & _
" vw_well3_12.Parameter2_GW, vw_FetchReportnamewiselab_well3_0.DatePrepared, " & _
" vw_FetchReportnamewiselab_well3_0.ReportingPeriod,vw_FetchReportnamewiselab_well3_0.LabID , " & _
" vw_FetchReportnamewiselab_well3_0.LabName FROM tbl_Parameter2_GW LEFT " & _
" OUTER JOIN vw_well3_12 ON tbl_Parameter2_GW.Sort = vw_well3_12.Sort " & _
" CROSS JOIN vw_FetchReportnamewiselab_well3_0 " & _
" where samplingdate >='" & FromDate & "' and samplingdate <='" & ToDate & "'"
m_Dataconn.Execute sql
Set Report = Appl.OpenReport(App.Path & "\rptMW3.rpt")
Set objCPProperties = Report.Database.Tables(1).ConnectionProperties
objCPProperties.DeleteAll
objCPProperties.Add "Provider", "SQLOLEDB.1"
objCPProperties.Add "Integrated Security", "SSPI"
objCPProperties.Add "Data Source", "seims"
objCPProperties.Add "Initial Catalog", "smrfs"
For Each objDBTable In Report.Database.Tables
objDBTable.Location = objDBTable.Name 'Report.Database.Tables(1).Name 'getDataConnection()
Next
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
CRViewer91.Refresh