Using the ReportDocument Object
Listing 17-14 shows how to change the data source of a table as well set the user credentials. This is very similar to Listing 17-10 which only demonstrated how to set the logon credentials. In that listing, different logon credentials were applied depending upon the table name. In this listing, all tables are treated the same and they are all set to the same data source.
Private Sub LogonToTables(ByVal UserId As String, ByVal Password As String, _
Optional ByVal ServerName As String = "", Optional ByVal DatabaseName _
As String = "")
Dim MyReport as New CrystalReport1()
Dim MyTable As CrystalDecisions.CrystalReports.Engine.Table
Dim MyConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
Dim MyLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo
If ServerName <> "" Then
MyConnectionInfo.ServerName = ServerName
MyConnectionInfo.DatabaseName = DatabaseName
End If
MyConnectionInfo.UserID = UserId
MyConnectionInfo.Password = Password
For Each MyTable In MyReport.Database.Tables
MyLogonInfo = myTable.LogOnInfo
MyLogonInfo.ConnectionInfo = MyConnectionInfo
MyTable.ApplyLogOnInfo(MyLogonInfo)
'Note: The next line is only necessary for SQL Server
If ServerName <> "" Then
MyTable.Location = MyTable.Location.Substring(MyTable.Location.LastIndexOf(".") + 1)
End If
Next
CrystalReportViewer1.ReportSource = MyReport
End Sub
Since the code is very similar to the code in Listing 17-11, only a few differences need to be mentioned. The parameters being passed to the procedure are a user Id, password, server name and database name. Both the server name and database name are optional. If they are passed a value, then the report is assigned the new data source. If nothing is passed, then those properties aren’t modified and the report uses the default server and database that was assigned during design time.
Just as before, the code loops through each table in the report and sets it properties. In addition to setting the UserId and Password properties of the ConnectionInfo object, it also sets the ServerName and DatabaseName properties (assuming that they were passed as parameters). Lastly, it calls the ApplyLogOnInfo() method to save the property values.
A very important piece of code is the assignment of the myTable.Location property near the end of the listing. A requirement for changing the data source of a SQL Server table is that you must also change the Location property of the Table object. The Location property is a string value that has the name of the server as part of the string. It lists the database name, the table owner and the table name. For example, it looks similar to the following:
If you leave the Location property alone and don’t overwrite it, then the report’s server and database name won’t change. Instead, you have to reset it to just the table name. By removing the database name from the string, the report has to look at the new properties you just set to get this information. This results in the server and database changing.
The easiest change to make would be to overwrite the Location property with a string constant. For example, the following line of code would overwrite it with the Customers table.
This works fine if the report only uses one table. But when you have multiple tables, you have to get more creative because you can’t assign the same table name to every table object. Rather than use a string constant, the code parses out the table name from the Location property and reassigns it to itself. This has the effect of resetting the value, but dropping the database name and table owner from the string.
myTable.Location = myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
