string VB.Net水晶报表连接字符串

VB.Net crystal report connection string

提问by Hilal Al-Rajhi

I am using vb.net 2010to develop my software. Also have crystal reportsin my projects and things are working perfectly in my PC.

我正在使用vb.net 2010开发我的软件。crystal reports在我的项目中也有，并且在我的 PC 中一切正常。

My problem is that I design the crystal report in my PC with wizard and my PC is not the server, then upload it to the server so it would be accessible to users. But when try to open report a connection problemto the database pops up. I know that is due to the connection property when I designed the reports in my PC.

我的问题是我在我的 PC 中使用向导和 设计水晶报告my PC is not the server，然后将其上传到服务器，以便用户可以访问。但是当尝试打开connection problem数据库时会弹出一个报告。我知道这是由于我在 PC 中设计报告时的连接属性。

How can I solve this problem.

我怎么解决这个问题。

回答by Mahesh ML

It is showing the popup for giving userid and password. I want to give the server connection programatically.

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

i given the following code still it showing the popup

我给出了以下代码仍然显示弹出窗口

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

How can i solve this?

我该如何解决这个问题？

回答by Mahesh ML

Add this code in the module(for common access)

在模块中添加此代码（用于公共访问）

 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

Inside each crystal report viewer give the below code it will override the old connection with connectionstring

在每个水晶报告查看器中给出以下代码，它将用连接字符串覆盖旧连接

 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

回答by Greg Delaney

I know this post is a few years old, but after struggling with these same problems, I thought I would add a variation of Mahesh ML's routine for MS SQL 2016 server.

我知道这篇文章已经有几年了，但是在解决了这些相同的问题之后，我想我会为 MS SQL 2016 服务器添加 Mahesh ML 例程的变体。

A three things to note:

需要注意的三点：

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

• reportDocument.DataSourceConnections(0).IntegratedSecurity = Trueis 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
  

• SqlClient.SqlConnectionStringBuilder用于代替 DbConnectionStringBuilder
• zSecurity（添加）用于 Window 的身份验证而不是数据库用户

• reportDocument.DataSourceConnections(0).IntegratedSecurity = True使用 Windows 身份验证时需要

  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
  

回答by campagnolo_1

So to put this into a proper answer:

所以把它变成一个正确的答案：

Your first problem is solved by creating the same DSN on your server that you have on your PC. Your second problem can be solved using code that looks something like this:

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

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

or

或者

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

回答by Mahesh ML

Hi guys i got the answer

大家好，我得到了答案

 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