By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

Running Crystal report with a stored procedure in VB.NET

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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

Nov 20 '05 #2

P: n/a
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eZ**************@tk2msftngp13.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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


Nov 20 '05 #3

P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eZ**************@tk2msftngp13.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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



Nov 20 '05 #4

P: n/a
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(ParameterDiscret eValue0)

ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)

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()

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eZ**************@tk2msftngp13.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
> 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
>
>



Nov 20 '05 #5

P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eF***************@TK2MSFTNGP12.phx.gbl...
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(ParameterDiscret eValue0)

ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)

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()

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eZ**************@tk2msftngp13.phx.gbl...
> 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
>
> "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> news:%2****************@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
>
>



Nov 20 '05 #6

P: n/a
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ap**********************@news4.srv.hcvlny.cv. net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eF***************@TK2MSFTNGP12.phx.gbl...
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(ParameterDiscret eValue0)

ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)

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()

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
> 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
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:eZ**************@tk2msftngp13.phx.gbl...
> > 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
> >
> > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>



Nov 20 '05 #7

P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:u8**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ap**********************@news4.srv.hcvlny.cv. net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eF***************@TK2MSFTNGP12.phx.gbl...
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(ParameterDiscret eValue0)

ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)

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()

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
> "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> news:eE**************@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > news:eZ**************@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #8

P: n/a
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
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:8p****************@news4.srv.hcvlny.cv.net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:u8**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ap**********************@news4.srv.hcvlny.cv. net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:eF***************@TK2MSFTNGP12.phx.gbl...
> 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(ParameterDiscret eValue0)
>
> ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)
>
> 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()
>
>
>
>
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:%2****************@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
> >
> > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > news:eE**************@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > news:eZ**************@tk2msftngp13.phx.gbl...
> > > > 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
> > > >
> > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #9

P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:8p****************@news4.srv.hcvlny.cv.net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:u8**************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Ap**********************@news4.srv.hcvlny.cv. net...
> 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
>
> "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> news:eF***************@TK2MSFTNGP12.phx.gbl...
> > 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(ParameterDiscret eValue0)
> >
> > ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)
> >
> > 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()
> >
> >
> >
> >
> >
> > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > news:%2****************@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> > >
> > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > news:eE**************@TK2MSFTNGP10.phx.gbl...
> > > > 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
> > > >
> > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > > news:eZ**************@tk2msftngp13.phx.gbl...
> > > > > 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
> > > > >
> > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message > > > > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #10

P: n/a
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:O7*************@TK2MSFTNGP11.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:8p****************@news4.srv.hcvlny.cv.net...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:u8**************@TK2MSFTNGP10.phx.gbl...
> 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
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:Ap**********************@news4.srv.hcvlny.cv. net...
> > 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
> >
> > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > news:eF***************@TK2MSFTNGP12.phx.gbl...
> > > 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(ParameterDiscret eValue0)
> > >
> > > ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)
> > >
> > > 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()
> > >
> > >
> > >
> > >
> > >
> > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > news:%2****************@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > > news:eE**************@TK2MSFTNGP10.phx.gbl...
> > > > > 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
> > > > >
> > > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > > > news:eZ**************@tk2msftngp13.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message > > > > > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #11

P: n/a
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:O7*************@TK2MSFTNGP11.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
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
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:8p****************@news4.srv.hcvlny.cv.net...
> 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
>
> "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> news:u8**************@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > news:Ap**********************@news4.srv.hcvlny.cv. net...
> > > 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
> > >
> > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > news:eF***************@TK2MSFTNGP12.phx.gbl...
> > > > 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(ParameterDiscret eValue0)
> > > >
> > > > ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)
> > > >
> > > > 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()
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > > news:%2****************@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in
message > > > > > news:eE**************@TK2MSFTNGP10.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > > > > news:eZ**************@tk2msftngp13.phx.gbl...
> > > > > > > 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
> > > > > > >
> > > > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in

message
> > > > > > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #12

P: n/a
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
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:Tz*******************@news4.srv.hcvlny.cv.net ...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:O7*************@TK2MSFTNGP11.phx.gbl...
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

"Bill Nguyen" <bi*****************@jaco.com> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:8p****************@news4.srv.hcvlny.cv.net...
> > 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
> >
> > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > news:u8**************@TK2MSFTNGP10.phx.gbl...
> > > 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
> > >
> > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > news:Ap**********************@news4.srv.hcvlny.cv. net...
> > > > 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
> > > >
> > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in message
> > > > news:eF***************@TK2MSFTNGP12.phx.gbl...
> > > > > 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(ParameterDiscret eValue0)
> > > > >
> > > > > ParameterField1.CurrentValues.Add(ParameterDiscret eValue1)
> > > > >
> > > > > 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()
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> > > > > news:%2****************@TK2MSFTNGP09.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in

message > > > > > > news:eE**************@TK2MSFTNGP10.phx.gbl...
> > > > > > > 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
> > > > > > >
> > > > > > > "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message > > > > > > > news:eZ**************@tk2msftngp13.phx.gbl...
> > > > > > > > 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
> > > > > > > >
> > > > > > > > "Bill Nguyen" <bi*****************@jaco.com> wrote in
message
> > > > > > > > news:%2****************@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 20 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.