How to set the Recordsource of a report to a stored procedure with a parameter

[subir.kumar]

Hi,

I just migrated my database from Access to SQL server. I
need to know how to set the recordsource of a report to be
equal to a stored procedure with a parameter.

I have tried all of the following

1.
a. Create a ADODB.Command
b. Cmd1.CommandText = "MonthToDateReport"
c. Cmd1.CommandType = adCmdStoredProc
d. Cmd1.Parameters(1).Value = "Test"
e. rptReport.RecordSource = cmd1. execute

2. rptReport.recordsource = "Exec MonthtoDateReport "
& "Test"

They both give me error, please advice me as to how set
the recorsource so that it executes the report using the
stored procedure.

Thanks,

Regards,

Subir

 

[Jacek Segit]

Witaj subir,
W Twoim liœcie datowanym 13 maja 2004 (20:20:00) mo¿na przeczytaæ:

skcc> Hi,
skcc> I just migrated my database from Access to SQL server. I
skcc> need to know how to set the recordsource of a report to be
skcc> equal to a stored procedure with a parameter.
skcc> 2. rptReport.recordsource = "Exec MonthtoDateReport "
skcc> & "Test"

report.recordsource: dbo.NameOfMyStoredProcedure
report.InputParameters: @myFirstParameter
datatypeofparameter=value, @mysecondparameter int=FuctionName(),
@mythirdparameter varchar(50)=forms!myform!mycontrol

Regards
Jacek Segit

 

[Subir.Kumar]

Hey Jacek,

Thank you for taking the time out to reply to my query. I
am unable to comprehend your suggestion. I would be really
grateful if you could explain it to me, as in what report
properties should be set to what.

Thanks,

Regards,

Subir

 

[Jacek Segit]

Witaj Subir,
W Twoim liœcie datowanym 13 maja 2004 (23:04:08) mo¿na przeczytaæ:

SKcc> Hey Jacek,
SKcc> Thank you for taking the time out to reply to my query. I
SKcc> am unable to comprehend your suggestion. I would be really
SKcc> grateful if you could explain it to me, as in what report
SKcc> properties should be set to what.

OK,
in recordsource field put name of your storedprocedure,
for example:
dbo.MonthtoDateReport

(If you use user other then 'sa' you HAVE to add "word" dbo. or
"word" other owner of object/storedprocedure)

in inputparameters field write your parameters:
according to schema:
@PARAMETERNAME DATATYPEOFPARAMETER=VALUEOFPARAMETR
or
@PARAMETERNAME DATATYPEOFPARAMETER=FORMS!FORM_NAME!CONTROL_NAME
OR
@PARAMETERNAME DATATYPEOFPARAMETER=FUNCTION_NAME() (Your own/System VB based function)
for example:

@Test int=forms![myform]!mycontrol
or
@Test varchar(50)=my_function()

Regards
Jacek Segit