How to pass parameter array to Crystal Report stored procedure from VB.NET?
Hi,
I have a stored procedure e.g
Create StoredProcedure ABC
@ID INT,
@myDate DATE
AS
Begin
Select FirstName,LastName,(Select
END
==========================
Now my question is how to set stored procedure as crystal report data source and then how to pass parameter array to crystal report or stored procedure?
Thanks,
I have a stored procedure e.g
Create StoredProcedure ABC
@ID INT,
@myDate DATE
AS
Begin
Select FirstName,LastName,(Select
END
==========================
Now my question is how to set stored procedure as crystal report data source and then how to pass parameter array to crystal report or stored procedure?
Thanks,
ASKER
i can pass parameter to CR programatically and also i can pass parameter array, but i am concern with Stored Procedure that how to set it as CR data source. And then pass parameter to CR parameter or stored procedure?
The SP as is wont work with array or string of values as it expects parameter of a specific type (in your case int and date).
In the SP you need to accept a string where you expect to pass an array. Normally the string would be "," saperated.
Create a #Temptable and write a code to put every single vale in that table.
later join that #TempTable to the query.
to setup your report with SP please see this
http://www.pdssoftware.com/newsletter/mar07/page6.htm
In the SP you need to accept a string where you expect to pass an array. Normally the string would be "," saperated.
Create a #Temptable and write a code to put every single vale in that table.
later join that #TempTable to the query.
to setup your report with SP please see this
http://www.pdssoftware.com/newsletter/mar07/page6.htm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now how to pass value to this i mean how we can call this procedure
Normally the Reporting Tools (Crystal, SSRS etc) pass the multiple value parameters as a "," saperated string. you need is to setup your report to call that SP passing the multiple value parameters to it.
Here is the little exract from the link I posted before to setup your report with SP:
When creating a Crystal Report, make it like any other report, except for the following.
1. When specifying a data source, instead of selecting Tables or Views, select Procedures, and then select the newly created procedure.
2. Crystal will immediately prompt you to enter values for the procedure’s input parameters. Do NOT do so; rather, check “Set as Null” and click OK so Crystal will know to prompt for these as input parameters each time the report is run.
3. If you are using SQLServer, click on the Field Explorer and rename the fields to remove the “@” prefix that Crystal automatically adds to conform to SQLServer parameter/variable naming conventions
To call it in SQL Server, go:
execute GetOrderList2("1, 3, 8 , 9, 30, 22, 89 ")
assuming we wnated results for order ids 1, 3, 8 , 9, 30, 22 and 89
Hope it will help
Here is the little exract from the link I posted before to setup your report with SP:
When creating a Crystal Report, make it like any other report, except for the following.
1. When specifying a data source, instead of selecting Tables or Views, select Procedures, and then select the newly created procedure.
2. Crystal will immediately prompt you to enter values for the procedure’s input parameters. Do NOT do so; rather, check “Set as Null” and click OK so Crystal will know to prompt for these as input parameters each time the report is run.
3. If you are using SQLServer, click on the Field Explorer and rename the fields to remove the “@” prefix that Crystal automatically adds to conform to SQLServer parameter/variable naming conventions
To call it in SQL Server, go:
execute GetOrderList2("1, 3, 8 , 9, 30, 22, 89 ")
assuming we wnated results for order ids 1, 3, 8 , 9, 30, 22 and 89
Hope it will help
http://www.c-sharpcorner.com/UploadFile/mahesh/paramInCR11012006132839PM/paramInCR.aspx
Cheers!