VB.Net Crystal Report 连接字符串

Question

我正在使用vb.net 2010开发我的软件，并在项目中使用了crystal reports。在我的电脑上一切都运作得很完美。

但是我的问题是，我在我的电脑上使用向导设计了crystal report，但是我的电脑不是服务器。然后，我将它上传到服务器，以便用户可以访问它。但是当尝试打开报告时，会弹出与数据库的连接问题。我知道这是由于我在我的电脑上设计报告时的连接属性所致。

我该如何解决这个问题？

- Hilal Al-Rajhi

---

什么是DSN？我所做的就是使用Visual Studio 2010设计这些报表。 - Hilal Al-Rajhi

你指的是 DNS 吗？域名服务器。 - Hilal Al-Rajhi

数据源名称 = DSN。你需要将报表与数据源（数据库）连接起来。显然，你可以在自己的电脑上完成这个任务。但是一旦你切换到服务器上，那个数据源就不再可用了。那么你如何将报表连接到数据源呢？ - campagnolo_1

没错，这就是我的问题。我正在询问一种以编程方式保存数据库连接字符串的方法，这样无论服务器 DSN 是什么，我只需修改源代码，然后报告就可以在任何服务器上运行。 - Hilal Al-Rajhi

知道了。那是解决问题的一种方法。但如果我想在我的代码中指定服务器名称，该怎么做？ - Hilal Al-Rajhi

Answer 1

它正在显示弹出窗口以提供用户ID和密码。我想通过编程方式提供服务器连接。

我已经给出了以下代码，但仍然显示弹出窗口。

Private Sub CrystalReportViewer1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load
        Dim cryRpt As New ReportDocument
        Dim crtableLogoninfos As New TableLogOnInfos
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crConnectionInfo As New ConnectionInfo
        Dim CrTables As Tables
        Dim CrTable As Table
        cryRpt.Load("E:\ColorLab1\colorlab\colorlab\rpt_bill.rpt")

        With crConnectionInfo
            .ServerName = "MAHESH\SQLEXPRESS"
            .DatabaseName = "cc"
            .UserID = "erp"
            .Password = "123"
        End With

        CrTables = cryRpt.Database.Tables
        For Each CrTable In CrTables
            crtableLogoninfo = CrTable.LogOnInfo
            crtableLogoninfo.ConnectionInfo = crConnectionInfo
            CrTable.ApplyLogOnInfo(crtableLogoninfo)
        Next
        CrystalReportViewer1.RefreshReport()
    End Sub

我怎样解决这个问题？

- Mahesh ML

---

这是我解决问题的方法。其他解决方案几乎相同，但它们涉及更改我的电脑名称，而我不喜欢这样做。在这个解决方案中，你只需将服务器名称放入代码中即可。 - Hilal Al-Rajhi

Answer 2

将以下代码添加到模块中（以便通用访问）：

 Public Sub SetReportDb(ByVal ConnectionString As String, ByRef CrystalReportViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer, ByRef reportDocument As ReportClass)
        'Get SQL Server Details
        Dim builder As New System.Data.Common.DbConnectionStringBuilder()

        builder.ConnectionString = ConnectionString


        Dim zServer As String = TryCast(builder("Data Source"), String)
        Dim zDatabase As String = TryCast(builder("Initial Catalog"), String)
        Dim zUsername As String = TryCast(builder("User ID"), String)
        Dim zPassword As String = TryCast(builder("Password"), String)

        Dim ciReportConnection As New ConnectionInfo

        ciReportConnection.ServerName = zServer
        ciReportConnection.DatabaseName = zDatabase
        ciReportConnection.UserID = zUsername
        ciReportConnection.Password = zPassword

        'Assign data source details to tables

        For Each table As Table In reportDocument.Database.Tables
            table.LogOnInfo.ConnectionInfo = ciReportConnection
            table.ApplyLogOnInfo(table.LogOnInfo)
        Next

        For Each subrep As ReportDocument In reportDocument.Subreports
            For Each table As Table In subrep.Database.Tables
                table.LogOnInfo.ConnectionInfo = ciReportConnection
                table.ApplyLogOnInfo(table.LogOnInfo)
            Next
        Next

        'Assign data source details to the report viewer
        If CrystalReportViewer.LogOnInfo IsNot Nothing Then
            Dim tlInfo As TableLogOnInfos = CrystalReportViewer.LogOnInfo
            For Each tbloginfo As TableLogOnInfo In tlInfo
                tbloginfo.ConnectionInfo = ciReportConnection
            Next
        End If
        reportDocument.VerifyDatabase()
        reportDocument.Refresh()
        CrystalReportViewer.ReportSource = reportDocument
        CrystalReportViewer.Refresh()
    End Sub

在每个Crystal报表查看器中，输入以下代码，它将使用连接字符串覆盖旧连接。

 Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load
        SetReportDb(My.Settings.colorlabConnectionString, CrystalReportViewer1, rpt_inwardreport1)
    End Sub

- Mahesh ML

---

这是非常好的答案。它减少了为每个报告编写相同代码的工作量。 - Hilal Al-Rajhi

Answer 3

因此，为了给出一个正确的答案：

您的第一个问题可以通过在服务器上创建与PC上相同的DSN来解决。 您的第二个问题可以通过使用类似以下代码的代码来解决：

connection.DatabaseName = [DatabaseName]
connection.UserID = [UserID]
connection.ServerName = [ServerName]
connection.Password = [Password]

或者

myCrystalReport.SetDatabaseLogon("myUsername", "myPassword","servername","dbname");

- campagnolo_1

---

我已经进行了操作。我在我的代码中添加了登录详细信息并更改了服务器名称为server，但仍然遇到问题：无法打开连接。详情：[数据库供应商代码：17]....！ - Hilal Al-Rajhi

Answer 4

我知道这篇文章已经写了几年，但是在遇到同样的问题后，我想添加Mahesh ML例程在MS SQL 2016服务器上的变化。

需要注意以下三点：

• SqlClient.SqlConnectionStringBuilder is used instead of DbConnectionStringBuilder
• zSecurity (added) for Window's authentication instead of database user

• reportDocument.DataSourceConnections(0).IntegratedSecurity = True is needed when Windows authentication is used

  Public Sub SetReportSQL(ByVal ConnectionString As String,
                     ByRef CrystalReportViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer,
                     ByRef reportDocument As ReportClass)
      'Get SQL Server Details
      Dim builder As New SqlClient.SqlConnectionStringBuilder
  
      builder.ConnectionString = ConnectionString
  
      Dim zServer As String = TryCast(builder("Data Source"), String)
      Dim zDatabase As String = TryCast(builder("Initial Catalog"), String)
      Dim zSecurity As Boolean = Boolean.TryParse(builder("Integrated Security"), zSecurity)
      Dim zUsername As String = TryCast(builder("User ID"), String)
      Dim zPassword As String = TryCast(builder("Password"), String)
  
      Dim ciReportConnection As New ConnectionInfo
  
      ciReportConnection.ServerName = zServer
      ciReportConnection.DatabaseName = zDatabase
      ciReportConnection.IntegratedSecurity = zSecurity
      If zSecurity = False Then
          ciReportConnection.UserID = zUsername
          ciReportConnection.Password = zPassword
      Else
          reportDocument.DataSourceConnections(0).IntegratedSecurity = True
      End If
  
      'Assign data source details to tables
      For Each table As Table In reportDocument.Database.Tables
          table.LogOnInfo.ConnectionInfo = ciReportConnection
          table.ApplyLogOnInfo(table.LogOnInfo)
      Next
  
      For Each subrep As ReportDocument In reportDocument.Subreports
          For Each table As Table In subrep.Database.Tables
              table.LogOnInfo.ConnectionInfo = ciReportConnection
              table.ApplyLogOnInfo(table.LogOnInfo)
          Next
      Next
  
      'Assign data source details to the report viewer
      If CrystalReportViewer.LogOnInfo IsNot Nothing Then
          Dim tlInfo As TableLogOnInfos = CrystalReportViewer.LogOnInfo
          For Each tbloginfo As TableLogOnInfo In tlInfo
              tbloginfo.ConnectionInfo = ciReportConnection
          Next
      End If
      reportDocument.VerifyDatabase()
      reportDocument.Refresh()
      CrystalReportViewer.ReportSource = reportDocument
      CrystalReportViewer.Refresh()
  End Sub
  

- Greg Delaney

Answer 5

大家好，我得到了答案

 Private Sub rpt_billform_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.CrystalReportViewer1.LogOnInfo.Item(0).ConnectionInfo.ServerName = "MAHESH\SQLEXPRESS"
    Me.CrystalReportViewer1.LogOnInfo.Item(0).ConnectionInfo.DatabaseName = "cc"
    Me.CrystalReportViewer1.LogOnInfo.Item(0).ConnectionInfo.UserID = "erp"
    Me.CrystalReportViewer1.LogOnInfo.Item(0).ConnectionInfo.Password = "123"
End Sub

- Mahesh ML

---

这很好，但我的电脑上是我之前所有项目的开发环境，有时需要修改一个项目。这将使多次更改计算机名称变得低效。因此需要一个可编程的解决方案。 - Hilal Al-Rajhi