Welcome to weblogs.com.pk Sign in | Join | Help

Aziz ur Rahman

Random Thoughts

News

  • The WeatherPixie
    Listed on BlogShares


    Counter : simple hit counter
Changing Sql Connection dynamically in Crystal Reports 10 using DotNet

Dim myReport As New crDB()
myReport.SetDatabaseLogon(Me.txtUserId.Text, Me.txtPass.Text, Me.txtServer.Text, Me.txtDB.Text)
Dim myLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

For
Each myTable In myReport.Database.Tables
   myLogonInfo = myTable.LogOnInfo
   With myLogonInfo.ConnectionInfo
      .ServerName = Me.txtServer.Text
      .DatabaseName = Me.txtDB.Text
      .UserID = Me.txtUserId.Text
      .Password = Me.txtPass.Text
  End With

   Try
     
myTable.ApplyLogOnInfo(myLogonInfo)
   Catch
     
MessageBox.Show("Login Failed")
      Exit
Sub
   End Try

   'Note: The next line is only necessary for SQL Server
  
myTable.Location = myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
Next myTable
CrystalReportViewer1.ReportSource = myReport

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:

pubs.dbo.Customers

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. Consequently, 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.

myTable.Location = "Customers"

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)

HTH

 

 

Posted: Tuesday, July 20, 2004 11:12 AM by aziz
Filed under: , ,

Comments

No Comments

Anonymous comments are disabled