I have an MS Access database that I am upsizing to an Access XP project using SQL Server 2000. In my Access database I have a lot of reports and forms that are based on list boxes where users can make multiple selections for criteria. When the report is opened I pass the users selections as a filter for the report. EXAMPLE of a Filter I'm sending: "City in('Dallas','Atlanta','St Paul')".
In my Access project I can pretty much do the same thing - just open the report and apply a filter when the report is based on a table or a view. But if the report or form is based on a stored procedure it doesn't work because you can't apply a filter to a report based on a stored procedure - you have to pass parameters to the report.
To get around this I created parameters in my stored procedures for the reports or forms that required stored procedures. The only problem is I can't figure you how to pass multiple values to my stored procedure's parameters i.e. "@City in('Dallas','Atlanta','St Paul')". All I can pass is one value at a time.
I tired creating a parameter using in "Location in (@City" and "Location = @City" both of which work with just one value.
I've tried creating multiple parameters for city like "city in (@city1,@city2)". This works but is not feasible because a lot of cities could be selected and I would have parameters on several other fields using "In" so the number of parameters could be enormous.
I tried several ways of trying to pass multiple values for my parameters to the stored procedure none of them work. I was looking through ADO and found something called adArray which apparently can be used when creating parameters but I couldn't figure out how to make that work.
//
Set Parm1 = Cmd.CreateParameter("City", adChar + adArray, adParamInput, 50)
I tried City = array("Dallas","StPaul","Atlanta") and then pass city as the value but that didn't work either -Invalid data type
//
There has to be a way to do this. This has to be done all the times on web sites with list boxes that allow multiple selections that are passed back to a database - probably through a stored procedure. In fact this web site probably has quite a few.
I guess my questions is how do how do I pass multiple values for one field or parameter to my procedure ---. Is there a better way to accomplish this than using parameters? I have to use a stored procedure because a view won't work because I have case and if statements in some of them.
Am I going about this the wrong way and if so what is the right way????
Thanks
In my Access project I can pretty much do the same thing - just open the report and apply a filter when the report is based on a table or a view. But if the report or form is based on a stored procedure it doesn't work because you can't apply a filter to a report based on a stored procedure - you have to pass parameters to the report.
To get around this I created parameters in my stored procedures for the reports or forms that required stored procedures. The only problem is I can't figure you how to pass multiple values to my stored procedure's parameters i.e. "@City in('Dallas','Atlanta','St Paul')". All I can pass is one value at a time.
I tired creating a parameter using in "Location in (@City" and "Location = @City" both of which work with just one value.
I've tried creating multiple parameters for city like "city in (@city1,@city2)". This works but is not feasible because a lot of cities could be selected and I would have parameters on several other fields using "In" so the number of parameters could be enormous.
I tried several ways of trying to pass multiple values for my parameters to the stored procedure none of them work. I was looking through ADO and found something called adArray which apparently can be used when creating parameters but I couldn't figure out how to make that work.
//
Set Parm1 = Cmd.CreateParameter("City", adChar + adArray, adParamInput, 50)
I tried City = array("Dallas","StPaul","Atlanta") and then pass city as the value but that didn't work either -Invalid data type
//
There has to be a way to do this. This has to be done all the times on web sites with list boxes that allow multiple selections that are passed back to a database - probably through a stored procedure. In fact this web site probably has quite a few.
I guess my questions is how do how do I pass multiple values for one field or parameter to my procedure ---. Is there a better way to accomplish this than using parameters? I have to use a stored procedure because a view won't work because I have case and if statements in some of them.
Am I going about this the wrong way and if so what is the right way????
Thanks
