473,395 Members | 1,999 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Running Crystal report with a stored procedure in VB.NET

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
12 10342
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: kristoff plasun | last post by:
I have a problem with a C++ DCOM application that prints Crystal Reports with data from Oracle. The SQL query is relatively complex but when the report is printed from the Crystal Reports...
1
by: Scott | last post by:
Hello: I have a stored procedure for generating our invoices in Crystal Reports. I have added a new field to the SP, but when I try to add the field to my Crystal Report invoice, the field isn't...
1
by: Joe | last post by:
Hi friends, I need dynamically generate the crystal line graph from the c# application. i used stored procedure.but i don't know how to connect the C#, stored procedure and crystal report. 1....
2
by: Craig in Boulder | last post by:
I have been trying for over a week to get data from my stored procedure into a Crystal Report in VS.NET 2003 with no luck. I have all sorts of code parts but nothing that tell me how then go...
0
by: Craig Faulkner | last post by:
I have been fighting through my first crystal report in VS2003.NET and have made some headway. Here is what I've done: 1. Created a crystal report in VS2003 from a SQL stored procedure with...
4
by: Andrew | last post by:
Hey all, Been working with the Crystal Report Viewer, and have run into a situation I am hoping someone can help me get past. This may be more of a CR question, but hoping for some CR gurus to...
3
by: Diggler | last post by:
I was working on a report that is populated with three different tables in a strongly-typed dataset. The tables are populated from custom objects rather than directly from SQL Server. I loop...
7
by: Jlo | last post by:
Hi, I have a c# winforms application. When I call the report file, it shows me all the records in the table. How can I make it to call only a particular range. i have the following code...
1
by: Brad Pears | last post by:
I have a vb.net 2005 project and have a Crystal report I have designed (using the built-in Crystal reports that came with .net 2005). The fields in my report are based on a stored procedure. I have...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.