pcareWI
asked on
VB.NET 2008 and Crystal Reports
I'm writing a small app in vb.net 2008 Pro that executes a crystal report that was created in Crystal XI. This report has several subreports all of which use the same stored procedure that is used in the main report. I can run this report without issue through crystal but when trying to execute it through vb.net code, I receive the following error:
This field name is not known.
Error in File C:\..........
Error in formula <Record Selection>.
'({group_plan_renewals;1.P ROD_CD} like '*' & 'Dental' & '*' or
'
This field name is not known
I can tell which subreport this is referring to, so I tried removing that subreport to see if it would then run, but it just goes on to the next report and lists the same error for the first line in the record selection formula. I'm stumped. Any insight is greatly appreciated.
I do not have any code to post because this error isn't triggered until after the the function to load the report is done... if there is code that you need to see that would help, please let me know. This really seems more like an issue with the report/subreport though.
This field name is not known.
Error in File C:\..........
Error in formula <Record Selection>.
'({group_plan_renewals;1.P
'
This field name is not known
I can tell which subreport this is referring to, so I tried removing that subreport to see if it would then run, but it just goes on to the next report and lists the same error for the first line in the record selection formula. I'm stumped. Any insight is greatly appreciated.
I do not have any code to post because this error isn't triggered until after the the function to load the report is done... if there is code that you need to see that would help, please let me know. This really seems more like an issue with the report/subreport though.
ASKER
I'm not sure I understand... I have a separate form in my application with the report viewer component on it. I just use the following code from my main form to load the report:
objForm.ViewReport("\\atlas\d atlas\Dept\IT\Reporting\Crystal\11.0 Copied Designs\Health Plan Renewal.rpt", , "@Begin_dt=" & begin_dt & "&@End_dt=" & end_dt & "&acct_mgr=" & am & "&ben_anal=" & ba & "&Name=" & group)
objForm.Show()
Here is the code behind the report viewer form:
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class frmViewReport
Inherits System.Windows.Forms.Form
'Form overrides dispose to clean up the component list.
<System.Diagnostics.DebuggerNonUserCode()> _
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.rptViewer = New CrystalDecisions.Windows.Forms.CrystalReportViewer
Me.SuspendLayout()
'
'rptViewer
'
Me.rptViewer.ActiveViewIndex = -1
Me.rptViewer.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
Me.rptViewer.Dock = System.Windows.Forms.DockStyle.Fill
Me.rptViewer.Location = New System.Drawing.Point(0, 0)
Me.rptViewer.Name = "rptViewer"
Me.rptViewer.SelectionFormula = ""
Me.rptViewer.Size = New System.Drawing.Size(292, 266)
Me.rptViewer.TabIndex = 0
Me.rptViewer.ViewTimeSelectionFormula = ""
'
'frmViewReport
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.rptViewer)
Me.Name = "frmViewReport"
Me.Text = "frmViewReport"
Me.ResumeLayout(False)
End Sub
Friend WithEvents rptViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer
Friend Function ViewReport(ByVal sReportName As String, _
Optional ByVal sSelectionFormula As String = "", _
Optional ByVal param As String = "") As Boolean
'Declaring variablesables
Dim intCounter As Integer
Dim intCounter1 As Integer
'Crystal Report's report document object
Dim objReport As New _
CrystalDecisions.CrystalReports.Engine.ReportDocument
'object of table Log on info of Crystal report
Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo
'Parameter value object of crystal report
' parameters used for adding the value to parameter.
Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
'Current parameter value object(collection) of crystal report parameters.
Dim currValue As CrystalDecisions.Shared.ParameterValues
'Sub report object of crystal report.
Dim mySubReportObject As _
CrystalDecisions.CrystalReports.Engine.SubreportObject
'Sub report document of crystal report.
Dim mySubRepDoc As New _
CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim strParValPair() As String
Dim strVal() As String
Dim index As Integer
Try
'Load the report
objReport.Load(sReportName)
'Check if there are parameters or not in report.
intCounter = objReport.DataDefinition.ParameterFields.Count
'As parameter fields collection also picks the selection
' formula which is not the parameter
' so if total parameter count is 1 then we check whether
' its a parameter or selection formula.
If intCounter = 1 Then
If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
intCounter = 0
End If
End If
'If there are parameters in report and
'user has passed them then split the
'parameter string and Apply the values
'to their concurrent parameters.
If intCounter > 0 And Trim(param) <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index), "=") > 0 Then
strVal = strParValPair(index).Split("=")
paraValue.Value = strVal(1)
currValue = _
objReport.DataDefinition.ParameterFields(strVal(0)).CurrentValues
currValue.Add(paraValue)
objReport.DataDefinition.ParameterFields(strVal(0)).ApplyCurrentValues(currValue)
End If
Next
End If
'Set the connection information to ConInfo
'object so that we can apply the
'connection information on each table in the report
ConInfo.ConnectionInfo.UserID = "sa"
ConInfo.ConnectionInfo.Password = "rampart29"
ConInfo.ConnectionInfo.ServerName = "Apollo"
ConInfo.ConnectionInfo.DatabaseName = "Labyrinth"
For intCounter = 0 To objReport.Database.Tables.Count - 1
objReport.Database.Tables(intCounter).ApplyLogOnInfo(ConInfo)
Next
' Loop through each section on the report then look
' through each object in the section
' if the object is a subreport, then apply logon info
' on each table of that sub report
For index = 0 To objReport.ReportDefinition.Sections.Count - 1
For intCounter = 0 To _
objReport.ReportDefinition.Sections(index).ReportObjects.Count - 1
With objReport.ReportDefinition.Sections(index)
If .ReportObjects(intCounter).Kind = _
CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
mySubReportObject = CType(.ReportObjects(intCounter), _
CrystalDecisions.CrystalReports.Engine.SubreportObject)
mySubRepDoc = _
mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
mySubRepDoc.Database.Tables(intCounter1).ApplyLogOnInfo(ConInfo)
Next
End If
End With
Next
Next
'If there is a selection formula passed to this function then use that
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionFormula = sSelectionFormula
End If
'Re setting control
rptViewer.ReportSource = Nothing
'Set the current report object to report.
rptViewer.ReportSource = objReport
'Show the report
rptViewer.Show()
Return True
Catch ex As System.Exception
MsgBox(ex.Message)
End Try
End Function
End Class
ASKER
Wait... I see what you mean. Unfortunately that didn't work. Here is the code that I used after your suggestion:
Try
objReport.Load(sReportName , CrystalDecisions.Shared.Op enReportMe thod.OpenR eportByTem pCopy)
intCounter = objReport.DataDefinition.P arameterFi elds.Count
Try
objReport.Load(sReportName
intCounter = objReport.DataDefinition.P
I ran into the same problem several years ago.. try this... it has always worked well for me.
Dim CR As New ReportDocument
Dim strReportPath As String = "\\...."
CR.Load(strReportPath)
'
' Declare the parameter related objects.
'
Dim crParameterDiscreteValue As ParameterDiscreteValue
Dim crParameterFieldDefinitions As ParameterFieldDefinitions
Dim crParameterFieldLocation As ParameterFieldDefinition
Dim crParameterValues As ParameterValues
'
' Get the report's parameters collection.
'
crParameterFieldDefinitions = CR.DataDefinition.ParameterFields
'
' Set the first parameter
' - Get the parameter, tell it to use the current values vs default value.
' - Tell it the parameter contains 1 discrete value vs multiple values.
' - Set the parameter's value.
' - Add it and apply it.
' - Repeat these statements for each parameter.
crParameterFieldLocation = crParameterFieldDefinitions.Item(0)
crParameterValues = crParameterFieldLocation.CurrentValues
crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
crParameterDiscreteValue.Value = txtCpar.Text.Trim
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldLocation.ApplyCurrentValues(crParameterValues)
'CR.PrintToPrinter(1, True, 0, 0)
ASKER
Where would I put the connection info for the report with this code?
ASKER
Please ignore my last comment. I figured it out... however, I'm back to receiving the same error as in my initial post. Any other ideas?
I use integrated security...
see code snippet.
this also works for me.. may spark some new ideas for you
see code snippet.
this also works for me.. may spark some new ideas for you
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myConnectionInfo As New ConnectionInfo()
myConnectionInfo.DatabaseName = "XXXXX"
myConnectionInfo.UserID = "XXXXX"
myConnectionInfo.Password = "XXXXX"
setDBLOGONforREPORT(myConnectionInfo)
End Sub
Private Sub setDBLOGONforREPORT(ByVal myconnectioninfo As ConnectionInfo)
Dim mytableloginfos As New TableLogOnInfos()
mytableloginfos = CrystalReportViewer1.LogOnInfo
For Each myTableLogOnInfo As TableLogOnInfo In mytableloginfos
myTableLogOnInfo.ConnectionInfo = myconnectioninfo
Next
End Sub
ASKER
Thank you. This is actually what I'm already using for the connection info. It doesn't seem like the error I'm receiving has anything to do with the connection. I could be wrong though. I'm open to trying anything at this point.
Ar eyou using the CR XI dlls or the ones that come with VS 2008 for Crystal?
mlmcc
mlmcc
ASKER
I'm using what came with VS 2008. I'm wordering if I maybe add the existing crystal report to the project... if that will make a difference. I'm thinking that it won't but I suppose it's worth a try. It's just odd that it's saying the field doesn't exist and it's referring to just a normal field in the stored procedure in the sub-report... not a parameter or anything.
If you haven't already tried.. I would create a simple test report without any stored procs and test stored proc with the code you are using and see if you get the same error. And build from there until you pinpoint the problem..
ASKER
Thanks... I'll try that. I have a feeling it has to do with the sub reports. I'm going to just try removing all of the sub reports first and see if just the main report runs. I'll post with the results.
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I use the dlls from Crystal XI?
ASKER
Thanks for the help and quick responses!
Did you resolve the second question?
mlmcc
mlmcc
ASKER
I did... I got it all figured out now. Thank you for your assistance.
or loading it in a report document?.. If loading in a report document, try this
report.Load(ReportType, OpenReportMethod.OpenRepor