asked on

How to use Multiple Value List parameter to Stored procedure to publish crosstab crystal report via ASP and stored procedure?

I have a HTML FORM where i do select Multiple Value in the list as given in Example 1.
the i pass that value to ASP file (example 2 Below) where i am invoking crytal report obj and sending the parameters through stored procedure(example 3) to publish crosstab crystal report . How to send the Multiple Value parameter thought stored procedure so that i can get all the expected value back via crystal report.

Example 1
<td bordercolor="#663333"> <select name="Component" size="5" multiple id="component">
<option>SLO</option>
<option>PLD</option>
<option>US</option>
</select> </td>
</tr>
<tr>
<td><strong>Status:</strong></td>
<td> <select name="Status" size="6" multiple id="status">
<option>Passed</option>
<option>Failed</option>
<option>Not Run</option>
</select></td>
</tr>
Example 2( Asp page )
Not working for multiple list values.

SecondField = Request.Form("Component")
ThirdField = Request.Form("Status")

Session("oRpt").ParameterFields.GetItemByName("@Component").AddCurrentValue(CStr(SecondField))
Session("oRpt").ParameterFields.GetItemByName("@Status").AddCurrentValue(CStr(ThirdField))

Example 3 stored procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure ReqSummaryIN
@TestCycle varchar (50)= null ,
@Component varchar (50)= null ,
@Status varchar (15)= null as
Begin
Select RQ_REQ_STATUS,RQ_USER_01 from results where CY_CYCLE = @TestCycle and RQ_USER_01 IN (@Component)
and RQ_REQ_STATUS IN (@Status)

Select RQ_REQ_STATUS, RQ_USER_01 from Req where
RQ_REQ_ID NOT IN (Select cast(RQ_REQ_ID as varchar)
from results where CY_CYCLE = @TestCycle)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

frodoman

If I remember my ASP correctly, what you get from a form when multiple values are selected is a comma delimited list. So for example this line of code: SecondField = Request.Form("Component")

May be giving you a value of: ComponentA, ComponentB

In your stored procedure you're sending this value in so your select statement will read: ...RQ_USER_01 IN ('ComponentA, ComponentB') but you need it to read: ...RQ_USER_01 IN ('ComponentA', 'ComponentB')

To resolve this you're going to need to parse out the selected items into a string like: " 'ComponentA', 'ComponentB' " You can do this in your asp page before sending it through or (preferably) do it insider your stored procedure.

kshireesh

ASKER

you are right that i am getting the values like ComponentA, ComponentB to send them like RQ_USER_01 IN ('ComponentA', 'ComponentB')
how do i do that in ASP?
Please let me know .

frodoman

You just need to use the SPLIT function with a comma delimiter to split the string into an array. Then loop through the array and create a new string with quotes around each element.

The syntax and usage of the SPLIT function can be shown here: http://www.devguru.com/Technologies/vbscript/quickref/split.html

frodoman

kshireesh

ASKER

No split function is not working working in Crystal 8.5 when i add the stored procedure in crystal.

what i have found is add the records in temp table and them run them through the query.

frodoman

Okay - you can make it work using the split function (in your ASP not in the report) but if you prefer to use a temp table that will work also. As long as you get the format to match what I put in my first response it will work - however you get to that format doesn't matter.

frodoman

kshireesh

ASKER

Can you please send me the code exmaple for this ?

ASKER CERTIFIED SOLUTION

frodoman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Computer101

Forced accept.

Computer101
EE Admin