Crystal Report ignoring SQL Stored proc parm - can't work out why...

[nzmike]

I have a Crystal Report that uses data sourced from 3 stored procedures in SQL Server. When I run the report via a WinForm it asks me for the parameters, even though I'm passing them through in the VB.Net code. Here's what I've got:

 

 

'Logon to the server

Dim crReport As New ContactReport()

Dim crTable As CrystalDecisions.CrystalReports.Engine.Table

Dim crLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()

ReportViewer.LogOnInfo = New CrystalDecisions.Shared.TableLogOnInfos()

crLogonInfo.TableName = "sp_CM_SelectContactForReport;1"

With crLogonInfo.ConnectionInfo

.ServerName = "(local)"

.DatabaseName = "ContactsManager"

.UserID = "xx" 'UserId

.Password = "xx" 'Password

End With

ReportViewer.LogOnInfo.Add(crLogonInfo)

 

'Create the parameter objects

Dim ParameterFields As New CrystalDecisions.Shared.ParameterFields()

Dim ParameterField As New CrystalDecisions.Shared.ParameterField()

Dim spValue As New CrystalDecisions.Shared.ParameterDiscreteValue()

 

'Add contact ID parameter

ParameterField.ParameterFieldName = "@cnt_ID"

ParameterField.ParameterType = CrystalDecisions.[shared].ParameterType.StoreProcedureParameter

spValue.Value = intRowID

ParameterField.CurrentValues.Add(spValue)

ParameterFields.Add(ParameterField)

 

'Add current user ID parameter

ParameterField.ParameterFieldName = "@cnt_UserID"

ParameterField.ParameterType = CrystalDecisions.[shared].ParameterType.StoreProcedureParameter

spValue.Value = strUserID

ParameterField.CurrentValues.Add(spValue)

ParameterFields.Add(ParameterField)

 

ReportViewer.ParameterFieldInfo = ParameterFields

 

'Show the report

ReportViewer.ReportSource = crReport

 

 

 

The code runs all the way through, appears to add the parameters, but when the report runs it comes up with the parameter entry dialog for both parameters... anyone know what I'm missing here?

 

TIA...

 

Mike.

[pcf108]

Try using parameter field definitions since the parameters already exit in the report..

 

Also, I don't think you need the "@" symbol in front of the crystal reports parameters.

 

try..

 

Dim crParameterFieldDefinitions As ParameterFieldDefinitions = ReportViewer.DataDefinition.ParameterFields

Dim ParameterField As ParameterFieldDefinition = crParameterFieldDefinitions.Item("cnt_ID")

Dim crParameterValues As ParameterValues = crParameterFieldDefinition.CurrentValues

Dim spValue As New ParameterDiscreteValue()
spValue.Value = intRowID

crParameterValues.Add(spValue)
ParameterField.ApplyCurrentValues(crParameterValues)

 

for each parameter and see how that suits your fancy..

Edited September 26, 2003 by pcf108

[nzmike]

Thanks for that - I did eventually work out that I needed to add FieldDefinition objects and cycle through each parameter setting them up.... just in case anyway else runs into this problem here's how I did it.

 

'Create the parameter objects

Dim crParamFieldDef As ParameterFieldDefinition

Dim crParamFieldDefs As ParameterFieldDefinitions

Dim crParamValues As New ParameterValues()

Dim crParamDiscreteValue As New ParameterDiscreteValue()

 

'Fetch the current report paramter definitions.

crParamFieldDefs = crReportDocument.DataDefinition.ParameterFields

 

For Each crParamFieldDef In crParamFieldDefs

If crParamFieldDef.ParameterType = ParameterType.StoreProcedureParameter And _

crParamFieldDef.IsLinked = False Then

 

Select Case crParamFieldDef.ValueType

Case FieldValueType.NumberField

crParamDiscreteValue.Value = Convert.ToInt32(intRowID)

crParamValues = crParamFieldDef.CurrentValues

crParamValues.Add(crParamDiscreteValue)

crParamFieldDef.ApplyCurrentValues(crParamValues)

 

Case Else

crParamDiscreteValue.Value = CStr(strUserID)

crParamValues = crParamFieldDef.CurrentValues

crParamValues.Add(crParamDiscreteValue)

crParamFieldDef.ApplyCurrentValues(crParamValues)

End Select

Else

'Item.SetCurrentValue Item.DefaultValue,Item.ValueType

End If

Next

 

Maybe this is the long way around the problem but it seems to work!

 

Cheers,

 

Mike.