parameters in stored procedure

[jem1968]

Hello. I have been developing in Access (Jet) for years and am trying to make the leap into sql server. In Jet, I have often used an unbound form that users can select various criteria to generate a report. The queries reference those form fields. I would like to use a similar process in an ADP. I have a series of stored procedures that i want to append data to another table based on criteria selected (or left null) in a form. In the stored procedures i have set up these parameters, each one is nvarchar and can contain a value or be null:

@city
@country
@investortype
@fundcategory

Now, i am not clear on how to send these parameters to the query from the form code or how to reference them and indicate that

@city = Forms!fldgRunCustomReport!cboCity
@country = Forms!fldgRunCustomReport!cboCountry

and so on. Any help would be greatly appreciated!!!

Thanks.

 

[cmmrfrds]

Look under the data tab on the Report.

In the source put the sproc name.
dbo.yoursproc

In the Input Paramters put the parameters separated by comma.

@city = Forms!fldgRunCustomReport!cboCity,@country = Forms!fldgRunCustomReport!cboCountry

Depending on how the parameters are defined in the sproc it may be necessary to include all the parameters. The default is input parameter for output parameter designate as such.

 

[jem1968]

Thanks for the response. I tried this and unfortunately it won't work because the report doesn't come into play until late in the game...the stored procedures are selecting data based on the criteria entered into the form and dumping it into another table, a temporary table. The temporary table, or actually a stored procedure based on that table, is used as the record source for the report. So, what i need is to send the parameters to the 'appending' stored procedures from the form code.

 

[cmmrfrds]

Use the ADO Command object to call stored procedures with parameters.

See this thread958-1005576 for an example.

 

[jem1968]

Thanks! I figured it out and it works great now.