Crystal Reports Custom SQL Query Parameter Passing

I'm often using custom SQL to get the data into my crystal reports, using the Add Command to write the SQL query. But I am unable to use any parameters when using this. I see there is a Parameter List, and I can create them and all, but I am unable to get them to work like regular parameters. Anyone succeeded in using these?

yep, you can do it,,,

Following is a code extract:

Have a look, u will be able to understand it..

PrivateSub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim frmReport AsNew Form3

frmReport.ReportViewer.ReportSource = "C:\Documents and Settings\dtdtrd\Desktop\University of Greenwich\Project\FINALPROJECT\OffenceInput\Traffi cOffence.rpt"

'The Parameters in the report

Dim ParamterField AsNew CrystalDecisions.Shared.ParameterField

Dim ParameterField1 AsNew CrystalDecisions.Shared.ParameterField

Dim ParameterField2 AsNew CrystalDecisions.Shared.ParameterField

Dim ParameterField3 AsNew CrystalDecisions.Shared.ParameterField

'A collection of parameters is a parameter field

Dim ParamterFields AsNew CrystalDecisions.Shared.ParameterFields

'Every parameter has a descrete value

Dim ParamterDescreteValue AsNew CrystalDecisions.Shared.ParameterDiscreteValue

Dim ParamterDescreteValue1 AsNew CrystalDecisions.Shared.ParameterDiscreteValue

Dim ParamterDescreteValue2 AsNew CrystalDecisions.Shared.ParameterDiscreteValue

Dim ParamterDescreteValue3 AsNew CrystalDecisions.Shared.ParameterDiscreteValue

'Now each of these parameters should be added a meaning

ParamterField.ParameterFieldName = "OffenceType"

ParameterField1.ParameterFieldName = "Reference"

ParameterField2.ParameterFieldName = "OffenceTitle"

ParameterField3.ParameterFieldName = "Description"

'Each of these parameters should be given a starting value

ParamterDescreteValue.Value = lblOffenceType.Text

ParamterDescreteValue1.Value = lblRefNumber.Text

ParamterDescreteValue2.Value = lblTitle.Text

ParamterDescreteValue3.Value = txtDescription.Text

'All these values should be added to parameter field

ParamterField.CurrentValues.Add(ParamterDescreteVa lue)

ParameterField1.CurrentValues.Add(ParamterDescrete Value1)

ParameterField2.CurrentValues.Add(ParamterDescrete Value2)

ParameterField3.CurrentValues.Add(ParamterDescrete Value3)

'Finally all these parameters should be added to the report

ParamterFields.Add(ParamterField)

ParamterFields.Add(ParameterField1)

ParamterFields.Add(ParameterField2)

ParamterFields.Add(ParameterField3)

'ParameterInfo has been given all the information needed

frmReport.ReportViewer.ParameterFieldInfo = ParamterFields

frmReport.MdiParent = Me.Parent.FindForm()

frmReport.Show()

EndSub

thanks,

I am facing problem with passing parameters to stored procedure from crystal reports. I have added parameter @reportpassingdate in crystal report which is the date given in list box. Here I have taken add command, I wrote

Exec sp_example @date=’{?@reportpassingdate}’

But it is saying cannot convert character string to datetime.

@date datatype in stored procedure is varchar.

@reportpassingdate is of type string.



I stucked here from one week . I am trying to pass this report date to stored procedure. But I didn’t find any solution yet.



If you have any idea please help me out.



Thanks & Regards

Request.

hi...my name is izura.i having a problem like u did...have u get the solution? if u had can u help me to solve it?
tq

Well, I'll try using ur code it doesn't work...
In Crystal Report, I use query for example :
SELECT * FROM Employee E
WHERE E.employee_ID = {?ID}

That's it
and then
I write ur code in form.
It doesn't work
Doesn't result anything...
Why? What happened?

Should I add parameter in Parameter Fields on Crystal Report?

Or how do i do to make this works?

Thanks

Regards,


Andy