CR9 problems

[lucia]

I am using Crystal Report 9 and vb6 with Sql server 2000 as backend.

I have a report work perfectly on one server. Now I need to change it to work on different servers.

The fields are selected from 4 tables on design mode. In vb code, it set criteria to select the records to display.
Here is the original code to run on current server.


strReport = "\Overrides.rpt"


strSelection = "{TRANS.TRANDATE} = DateTime (" & Format(dtpDate.Value, "yyyy") & "," & Format(dtpDate.Value, "mm") & "," & Format(dtpDate.Value, "dd") & ", 00, 00, 00) and "
strSelection = strSelection & "{TRANS.ROUTE_NUM} = '" & gsCheckinRouteNum & "' and "
strSelection = strSelection & " {TRANS.PACKET_NUM} <> '' "

If cmbBranch.Text <> "ALL" Then
strSelection = strSelection & "and {Person.Per_Branch} = '" & cmbBranch.Text & "' "
End If

Set crReport = crApp.OpenReport(gsTrack & "\Reports\" & strReport)
crReport.RecordSelectionFormula = strSelection


If optPrint(0).Value = True Then
' Print to screen
frmCR9Report.Visible = False
frmCR9Report.CRV9.ReportSource = crReport

frmCR9Report.CRV9.ViewReport
frmCR9Report.CRV9.EnableGroupTree = False
frmCR9Report.Show vbModal
Set frmCR9Report = Nothing
Else
crReport.PrintOut False, 1
End If





Now I need change it able to connect to different server,

I added codes (in red), but didn't get the same result.

strReport = "\Overrides.rpt"


' Set the connection to the SQL database that we will pass to the report
strConnection = "Driver={SQL Server};" & _
"Server=" & gsTrackingSQL & ";" & _
"Database=" & gsTrackDatabaseName & ";" & _
"Uid=" & gstrTrkUsr & ";" & _
"Pwd=" & gstrTrkPwd

strSelection = "{TRANS.TRANDATE} = DateTime (" & Format(dtpDate.Value, "yyyy") & "," & Format(dtpDate.Value, "mm") & "," & Format(dtpDate.Value, "dd") & ", 00, 00, 00) and "
strSelection = strSelection & "{TRANS.ROUTE_NUM} = '" & gsCheckinRouteNum & "' and "
strSelection = strSelection & " {TRANS.PACKET_NUM} <> '' "

If cmbBranch.Text <> "ALL" Then
strSelection = strSelection & "and {Person.Per_Branch} = '" & cmbBranch.Text & "' "
End If

Set crReport = crApp.OpenReport(gsTrack & "\Reports\" & strReport)
Set adoRS = New ADODB.Recordset
adoRS.Open "Trans", strConnection, adOpenDynamic, adLockBatchOptimistic
crReport.Database.Tables(1).SetDataSource adoRS, 3
crReport.RecordSelectionFormula = strSelection


If optPrint(0).Value = True Then
' Print to screen
frmCR9Report.Visible = False
frmCR9Report.CRV9.ReportSource = crReport

frmCR9Report.CRV9.ViewReport
frmCR9Report.CRV9.EnableGroupTree = False
frmCR9Report.Show vbModal
Set frmCR9Report = Nothing
Else
crReport.PrintOut False, 1
End If







Could any one point out what's wrong in my code? Thanks a lot.

[jggtz]

Crystal Reports' report designed using OLEDB (ADO) as data source

This example demonstrates how to connect to an OLEDB (ADO) data source,
change the data source, and change the database by using the ConnectionProperty Object, as well as how to change the table name by using the Location property of the DatabaseTable Object.
CrystalReport1 is created using an ODBC data source connected to the pubs database on Microsoft SQL Server.
The report is based off the authors table.


' Create a new instance of the report.
Dim Report As New CrystalReport1

Private Sub Form_Load()

' Declare a ConnectionProperty object.
Dim CPProperty As CRAXDRT.ConnectionProperty

' Declare a DatabaseTable object.
Dim DBTable As CRAXDRT.DatabaseTable

' Get the first table in the report.
Set DBTable = Report.Database.Tables(1)

' Get the "Data Source" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("Data Source")

' Set the new data source (server name).
' Note: You do not need to set this property if
' you are using the same data source.
CPProperty.Value = "Server2"

' Get the "User ID" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("User ID")

' Set the user name.
' Note: You do not need to set this property if
' you are using the same user name.
CPProperty.Value = "UserName"

' Get the "Password" property from the
' ConnectionProperties collection.
Set CPProperty = DBTable.ConnectionProperties("Password")

' Set the password.
' Note: You must always set the password if one is required.
CPProperty.Value = "Password"

' Get the "Initial Catalog" (database name) property from the
' ConnectionProperties collection.
' Note: You do not need to set this property if
' you are using the same database.
Set CPProperty = DBTable.ConnectionProperties("Initial Catalog")

' Set the new database name.
CPProperty.Value = "master"

' Set the new table name.
DBTable.Location = "authors2"

Screen.MousePointer = vbHourglass
' Set the report source of the viewer and view the report.
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault

End Sub


Hope this help