Skip to Content
0
Former Member
Apr 23, 2014 at 05:25 PM

Crystal Report Source is MS SQL Stored Procedure Causes Login Popup

254 Views

My environment is this: Visual Studio 2010 with CRforVS_13_0_9 installed. MS SQL Server is source for all report data. I use three different databases for all my reports.

I have a number of working CR reports that work fine in IDE and at runtime. However, one report works in IDE report designer Main Report Preview but does not work at runtime. Instead, it pops up the Database Login dialog box. This particular report is the only one that I have that uses a Stored Procedure for report source data. The following code is used for all reports. Can someone please help me to identify what is causing this anomaly?

' _Options contains parameter name and value pairs, if any

Dim oReport As New ReportDocument()

Dim FullReportName = "MA_CustomerUsage"

oReport.Load(FullReportName, OpenReportMethod.OpenReportByDefault)

DoCRLogin(oReport)

If _Options.ParameterList IsNot Nothing Then ImplementCRParameters(oReport)

With CrystalReportViewer

.SelectionFormula = _SelectionFormula

.ReportSource = oReport

.Zoom(zoomPageWidth)

.ShowParameterPanelButton = False

.ToolPanelView = ToolPanelViewType.None

End With

Private Sub DoCRLogin(ByVal oRpt As ReportDocument)

Dim oCRDb As Database = oRpt.Database

Dim oCRTables As Tables = oCRDb.Tables

Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

Dim DatabaseName = oCRTables(0).LogOnInfo.ConnectionInfo.DatabaseName

Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo() _

With {.DatabaseName = ScanInvenConStrSetting("Initial Catalog=", DatabaseName),

.ServerName = ScanInvenConStrSetting("Data Source=", DatabaseName),

.IntegratedSecurity = True}

For Each oCRTable As Table In oCRTables

oCRConnectionInfo.DatabaseName = oCRTable.LogOnInfo.ConnectionInfo.DatabaseName

oCRTableLogonInfo = oCRTable.LogOnInfo

oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo

oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)

Next

End Sub

Private Sub ImplementCRParameters(ByRef oReport As ReportDocument)

Dim oparamFields = New ParameterValues

Dim oFieldDefs = oReport.DataDefinition.ParameterFields

For Each CRParameter In _Options.ParameterList

Dim oFieldDef = oFieldDefs("@" & CRParameter.Name)

Dim oDiscrete As New ParameterDiscreteValue()

oDiscrete.Value = CRParameter.Value.ToString

oparamFields.Add(oDiscrete)

oFieldDef.ApplyCurrentValues(oparamFields)

Next

End Sub

Private Function ScanInvenConStrSetting(ByVal Src As String, ByVal DBName As String) As String

Dim MyDB As String = String.Empty

Select Case DBName.ToUpper

Case "Database1".ToUpper

MyDB = Global.My.Settings.Database1ConnectionString

Case "Database2".ToUpper

MyDB = Global.My.Settings.DatabaseConnectionString

Case "Database3".ToUpper

MyDB = Global.My.Settings.Database3ConnectionString

End Select

Dim ndx1 As Integer = InStr(MyDB, Src, CompareMethod.Text) + Src.Length

Dim ndx2 As Integer = InStr(ndx1, MyDB, ";", CompareMethod.Text)

If ndx2 = 0 Then ndx2 = MyDB.Length

ScanInvenConStrSetting = Mid(MyDB, ndx1, ndx2 - ndx1)

End Function