Solved

Question about assigning SQL tables logon info for Crystal Reports XI

Posted on 2007-03-27
12
405 Views
Last Modified: 2012-06-27
Hello,

I'm trying to skip the authentication screen in Crystal Reports XI by assigning the logon info to all of the tables in my SQL db.  I have the following code, but it's breaking in the first iteration in the loop.  Does anyone have experience with this?

I posted the question already in the CR forum, but no luck.  Hopefully someone here can help me out!

500 points:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_22468868.html
0
Comment
Question by:jgerbitz
  • 6
  • 5
12 Comments
 
LVL 27

Expert Comment

by:planocz
Comment Utility
You need something like this.....

    Public myReport As New ReportDocument        'Gobal Report Doc

  Private Sub GetReportConnection(ByVal myReport)
        ' Objects used to set the proper database connection information
        Dim tbCurrent As CrystalDecisions.CrystalReports.Engine.Table
        Dim tliCurrent As CrystalDecisions.Shared.TableLogOnInfo

        Try
            ' Load the report
            myReport.Load(sAppPath & sReportPath & sReportName, CrystalDecisions.[Shared].OpenReportMethod.OpenReportByTempCopy)

            ' Set the connection information for all the tables used in the report
            For Each tbCurrent In myReport.Database.Tables
                tliCurrent = tbCurrent.LogOnInfo
                With tliCurrent.ConnectionInfo
                    .ServerName = sDBServer
                    .UserID = sUSERid
                    .Password = ""
                    .DatabaseName = sDBName
                End With
                tbCurrent.ApplyLogOnInfo(tliCurrent)
            Next tbCurrent
        Catch Exp As LoadSaveReportException
            MsgBox("Incorrect path for loading report.", _
                    MsgBoxStyle.Critical, "Load Report Error")
        Catch Exp As Exception
            MsgBox(Exp.Message & "   in GetReportConnection Procedure", MsgBoxStyle.Critical, "General Error")

        End Try
    End Sub
0
 

Author Comment

by:jgerbitz
Comment Utility
Thanks for your response.

The code executes without catching, but the logon screen still appears.  Here's my code:

    Public myGlobalReport As New ReportDocument

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        GetReportConnection(Me.myGlobalReport)
    End Sub

    Private Sub GetReportConnection(ByVal myReport As ReportDocument)

        'Objects used to set the proper database connection information
        Dim tbCurrent As CrystalDecisions.CrystalReports.Engine.Table
        Dim tliCurrent As CrystalDecisions.Shared.TableLogOnInfo

        Try
            'load the report
            myReport.Load("C:\Inetpub\wwwroot\ClinicalPathways/PatientSummary.rpt", OpenReportMethod.OpenReportByTempCopy)

            'set the connection information for all the tables used in the report
            For Each tbCurrent In myReport.Database.Tables
                tliCurrent = tbCurrent.LogOnInfo
                With tliCurrent.ConnectionInfo
                    .ServerName = "(local)"
                    .DatabaseName = "dbName"
                    .UserID = "dbUser"
                    .Password = "dbPW"
                End With
                tbCurrent.ApplyLogOnInfo(tliCurrent)
            Next tbCurrent
        Catch ex As LoadSaveReportException
            MsgBox("Incorrect path for loading report.", MsgBoxStyle.Critical, "Load Report Error")
        Catch ex As Exception
            ex.ToString()

        End Try
    End Sub
0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
Check your Server name....

Samples......

windows login:
"Provider=sqloledb;Data Source=mySERVER;Initial Catalog=pubs;Integrated Security=SSPI;"
or
sql login:
"Provider=sqloledb;Data Source=mySERVER;Initial Catalog=dbName;User Id=dbUser;Password=dbPW;"
0
 

Author Comment

by:jgerbitz
Comment Utility
Shouldn't (local) work?  I have it like that in my connection string.  Tried replacing it with the actual computer name, but that didn't work.

FWIW, I'm on a domain... does this change anything?
0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
Yes you will need the  Domain connection string.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jgerbitz
Comment Utility
Ok, thanks.   Apologies if this is a stupid question, but what exactly is a Domain connection string?
I'm a little confused because I don't see the where the Domain comes into this whole thing (obviously it does, or I wouldn't be getting the logon screen).

The application and the database are both on the (local) machine.  The connection string in my ASP.NET app references a user account and password that I set up in SQL Server 2000, which is what I have in the code that I posted.  How / where do I put the Domain info?  What would it look like if the Domain name is, say, BOB?

Thanks again-
0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
Try to change this.....sqloledb in your connection string to  SQLClient.
I think your SQL needs to see the CR side.
0
 

Author Comment

by:jgerbitz
Comment Utility
Sorry for the late response.

>>Try to change this.....sqloledb in your connection string to  SQLClient.
>>I think your SQL needs to see the CR side.

After you wrote this, I realized I wasn't accessing the SQL db at all in the code posted above.  I amended the code by putting it in my data access layer, explicitly opening the connection (within try.... catch), performing the loop for applying logon info to the tables, and then closing the connection.  Nevertheless, I still end up at the logon screen.

I don't think I understand what you meant in your last post.  Could you be more specific?  FYI, my connection string:

    Dim strConnection As String = "data source=(local);uid=Nurse;pwd=critical;initial catalog=ClinicalPathways"
    Dim conClinical As New SqlConnection(strConnection)

Thanks-



0
 
LVL 27

Expert Comment

by:planocz
Comment Utility
It sounds like you still have admin rights problems between the SQL connections and each CR connection.
0
 

Author Comment

by:jgerbitz
Comment Utility
Any idea how to fix it?
0
 

Accepted Solution

by:
jgerbitz earned 0 total points
Comment Utility
FWIW, I was given the following code by Business Objects.  It works:

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

        'Put user code to initialize the page here

        ' Declare an instance of the strongly-typed report
        ' (which is essentially the same as declaring a
        ' ReportDocument object).
        Dim crReportDocument As NotMetSummary_PatArr   '(name of the report created by the developer)
        crReportDocument = New NotMetSummary_PatArr

        ' Declare string variables to hold the connectivity
        ' information that we will retrieve and use later.
        Dim ServerName As String
        Dim UserID As String
        Dim Password As String
        Dim DatabaseName As String

        ' Read in the connectivity values that we stored in
        ' session in WebForm1.
        ServerName = "(local)"  '(I'm developing on my local machine)
        UserID = "****"
        Password = "****"
        DatabaseName = "NorthWind"

        ' Call the Logon function passing in our ReportDocument
        ' and connectivity values.
        Logon(crReportDocument, ServerName, DatabaseName, UserID, Password)

        ' Set the Crystal Report Viewer report source to the
        ' ReportDocument we declared earlier.
        Me.CrystalReportViewer1.ReportSource = crReportDocument


    End Sub

    Private Function ApplyLogon(ByVal cr As CrystalDecisions.CrystalReports.Engine.ReportDocument, ByVal ci As CrystalDecisions.Shared.ConnectionInfo) As Boolean
        ' This function is called by the "Logon" function
        ' It loops through the report tables and applies
        ' the connection information to each table.

        ' Declare the TableLogOnInfo object and a table object for use later.
        Dim li As CrystalDecisions.Shared.TableLogOnInfo
        Dim tbl As CrystalDecisions.CrystalReports.Engine.Table

        ' For each table apply connection info.
        For Each tbl In cr.Database.Tables
            li = tbl.LogOnInfo
            li.ConnectionInfo = ci
            tbl.ApplyLogOnInfo(li)

            ' Verify that the logon was successful.
            ' If TestConnectivity returns false, correct table locations.
            If (tbl.TestConnectivity()) Then
                ' If there is a "." in the location then remove the
                ' beginning of the fully qualified location.
                ' Example "dbo.northwind.customers" would become
                ' "customers".
                If (tbl.Location.IndexOf(".") > 0) Then
                    tbl.Location = tbl.Location.Substring(tbl.Location.LastIndexOf(".") + 1)
                Else
                    ' If the location is not returning as a fully
                    ' qualified location we still set it to tbl.location
                    ' because Crystal Reports 9 installed on top of .NET
                    ' can *store* fully qualified names but will only *return*
                    ' the table name itself.
                    tbl.Location = tbl.Location
                End If
            Else
                Return False
            End If
        Next

        Return True
    End Function

    ' The Logon method iterates through all tables.
    Private Function Logon(ByVal cr As CrystalDecisions.CrystalReports.Engine.ReportDocument, _
        ByVal server As String, _
        ByVal database As String, _
        ByVal user_id As String, _
        ByVal password As String) As Boolean

        ' Declare and populate a new connection info object.
        Dim ci As New CrystalDecisions.Shared.ConnectionInfo
        With ci
            .ServerName = "(local)"
            .DatabaseName = "****"
            .UserID = "****"
            .Password = "****"
        End With

        ' If the ApplyLogon function fails then return a false for this function.
        ' We are applying logon information to the main report at this stage.
        If (Not ApplyLogon(cr, ci)) Then
            Return False
        End If

        ' Declare both a report object as well as a subreport object.
        Dim obj As CrystalDecisions.CrystalReports.Engine.ReportObject
        Dim subobj As CrystalDecisions.CrystalReports.Engine.SubreportObject

        ' Loop through all the report objects and locate subreports.
        ' If a subreport is found then apply logon information to
        ' the subreport.
        For Each obj In cr.ReportDefinition.ReportObjects
            If (obj.Kind = CrystalDecisions.[Shared].ReportObjectKind.SubreportObject) Then
                subobj = CType(obj, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                If (Not ApplyLogon(cr.OpenSubreport(subobj.SubreportName), ci)) Then
                    Return False
                End If
            End If
        Next

        ' Return True if the code runs to this stage.
        Return True

    End Function
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now