Running Crystal report with a stored procedure in VB.NET

[Bill Nguyen]

What's the VB syntax to run the CR report using the following SP?
I use CrystalreportViewer and ReportDocument.
Thanks
Bill
Here's the SP in SQLserver 2K:

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

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

else

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

GO

[Bernie Yaeger]

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

[Bill Nguyen]

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

[Bernie Yaeger]

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

[Bill Nguyen]

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

[Bernie Yaeger]

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

[Bill Nguyen]

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

[Bernie Yaeger]

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

[Bill Nguyen]

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

[Bernie Yaeger]

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

[Bill Nguyen]

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

[Bernie Yaeger]

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

[Bill Nguyen]

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