Running Crystal report with a stored procedure in VB.NET

  • Thread starter Thread starter Bill Nguyen
  • Start date Start date
B

Bill Nguyen

What's the VB syntax to run the CR report using the following SP?
I use CrystalreportViewer and ReportDocument.
Thanks
Bill


Here's the SP in SQLserver 2K:

CREATE proc mysp_ReportSubmission
@salesdate as varchar(20),
@inflag as bit
AS
if @inflag = 0

select * from station where inactive = 0
and stationid not in
(select stationid from transactions
where transdate = @salesdate)

else

select * from station where inactive = 0
and stationid in
(select stationid from transactions
where transdate = @salesdate)

GO
 
Hi Bill,

Simply build the report with the datasource being your slq server and the
sp - it will be available as a datasource once you connect the report to
your server.

HTH,

Bernie Yaeger
 
Thanks Bernie;
The question is how to provide the SP's parameters at run time. The
parameters are of different formats for CR and for SQLserver. Which one
should I provide? A sample of the codes would be greatly appreciated.
Thanks
Bill
 
Hi Bill,

I have placed below a sample of code I use to pass parameters to the cr
reportviewer. I use a case statement and run this all within a .vb file
called 'reportprinter_param.vb'. It shows you how to pass multiple
parameters to the report (in, essentially, a 4 step process). Inside the
report are the parameters, the names of which match the names in the
'paramfield.parameterfieldname' variable listed below.

Let me know if you have any questions.
Case "f:\imcapps\statesummary.rpt", "f:\imcapps\zipsummary.rpt"

paramField.ParameterFieldName = "mtitle"

paramField2.ParameterFieldName = "mbipad"

paramField3.ParameterFieldName = "missue"

discreteVal.Value = mglobals.gl_magvar

discreteVal2.Value = mglobals.gl_bipvar

discreteVal3.Value = mglobals.gl_issuevar

paramField.CurrentValues.Add(discreteVal)

paramField2.CurrentValues.Add(discreteVal2)

paramField3.CurrentValues.Add(discreteVal3)

paramFields.Add(paramField)

paramFields.Add(paramField2)

paramFields.Add(paramField3)

CrystalReportViewer1.ParameterFieldInfo = paramFields

CrystalReportViewer1.ReportSource = gl_browseprintvar

HTH,

Bernie
 
Dear Bernie;

Thanks for the tip. Please take a look at the codes below.

The problem is that CR still prompted for the parameters' values. In VB6,
you put a "True" at the end of the statement.

Also, if you review the SP, the @salesdate datatype is string. DO I need to
convert it into date before submit it to CR?

@salesdate as varchar(20),
@inflag as bit


Thanks

Bill





Dim ParameterFields As CrystalDecisions.Shared.ParameterFields

Dim ParameterField0 As CrystalDecisions.Shared.ParameterField

Dim ParameterField1 As CrystalDecisions.Shared.ParameterField

Dim ParameterDiscreteValue0 As
CrystalDecisions.Shared.ParameterDiscreteValue

Dim ParameterDiscreteValue1 As
CrystalDecisions.Shared.ParameterDiscreteValue

ParameterFields = New CrystalDecisions.Shared.ParameterFields

ParameterField0 = New CrystalDecisions.Shared.ParameterField

ParameterField0.ParameterFieldName = "@salesdate"

ParameterField1 = New CrystalDecisions.Shared.ParameterField

ParameterField1.ParameterFieldName = "@inflag"

ParameterDiscreteValue0 = New CrystalDecisions.Shared.ParameterDiscreteValue

ParameterDiscreteValue0.Value = rptParam0

ParameterDiscreteValue1 = New CrystalDecisions.Shared.ParameterDiscreteValue

ParameterDiscreteValue1.Value = rptParam1

''MsgBox(rptSum)

ParameterField0.CurrentValues.Add(ParameterDiscreteValue0)

ParameterField1.CurrentValues.Add(ParameterDiscreteValue1)

ParameterFields.Add(ParameterField0)

ParameterFields.Add(ParameterField1)



lblPrintWait.Text = "Loading report. Please wait..."

lblPrintWait.Visible = True

Me.Cursor = Cursors.WaitCursor

With myCrystal.CrystalReportViewer1



..ReportSource() = reportDocument1

' .ReportSource() =

..SelectionFormula = mSelection

..ParameterFieldInfo = ParameterFields

'.Refresh()

..RefreshReport()
 
Hi Bill,

The first problem I see is that the parameterfieldname should not have the
@ - sb the name of the parameter inside cr without any prefix - eg,
salesdate, inflag.

Re datatype - is should be the same as the datatype you've assigned it
inside cr.

HTH,

Bernie
 
Thanks Bernie;
I still ran into 2 problems:
1. ODBC error : Syntax error converting datetime from character string.
Remember the @salesdate parameter datatype is varchar(20) in the SP.
2. CR keeps prompting for @salesdate and @inflag values even though already
submitted in the VB app.
Thanks
Bill
 
Hi Bill,

Let's take it one param at a time. Temporarily change your code to pass
only one param and to have only one param inside the cr report. If the
salesdate is in the report only for display, then pass it as a string and
change the report param to string datatype; if it is required for some date
calculation, change it in your code to a date variable - saledatedate =
cdate(saledate) and let saledatedate (a datetime data type) be passed
instead.

Then you should no longer be prompted for the value of saledate.

Once we get that solved, we'll move on to the second param.

Bernie
 
Dear Bernie;
In fact, I don't have much choice. The parameters are from an SQLserver
store procedure. CR automatically recognizes them when you select the SP as
the datasource. In my VB 6 app, I use the following syntax and the report
works fine. You can see that the '@' sign is required to pass the value to
the stored procedure. I don't have problem with CR reports that contain
parameters within itself.
Again, I appreciated your persistence in helping me.
Bill
--------------------
Select Case iReportID
.ParameterFields(0) = "@SalesDate;" &
txtValidationDate.Text & ";true"
.ParameterFields(1) = "@flag;" & m_bReportFlag & ";true"
Case Else
End Select
.Action = 1
 
Hi Bill,

I'm running out of ideas. The problem is I don't pass parameterized sp's to
crystal. My parameters are created directly inside sql. Why not pass the
sp without the params; then create them inside cr and pass the values as you
and I have been discussing, and without the @ sign?

HTH,

Bernie
 
Bernie;
I made some progresses.
The parameters @salesdate and @inflag were passed to CR properly in my .NET
app. The only remaining problem is that CR still prompted for the
parameters' values. I had to click on "CANCEL" then the report displayed
corrrectly!
So the problem now is how to suppress thhe parameter prompting in runtime.
Again, thanks for all the support you've given me.
Bill
 
Hi Bill,

Sounds very good - think I have the answer: you don't need the params to be
made inside CR - they are part of the sp! So simply delete the params
inside crystal and you'll probably be ok.

Let now know.

Regards,

Bernie
 
Dear Bernie;
You can't delete the parameters. They're recognized as part of the stored
procedure by CR.
I'll check CR website on this.
Thanks
Bill
 
Back
Top