• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1307
  • Last Modified:

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





   
0
kshireesh
Asked:
kshireesh
  • 4
  • 3
1 Solution
 
frodomanCommented:
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.

0
 
kshireeshAuthor Commented:
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 .

0
 
frodomanCommented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kshireeshAuthor Commented:
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.
0
 
frodomanCommented:
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
0
 
kshireeshAuthor Commented:
Can you please send me the code exmaple for this ?
0
 
frodomanCommented:
A code sample for what - splitting in ASP?  I gave a link previously to how the split function works.  I haven't written asp in a while so syntax may be off, but it would be something like this:

Replace this line of your code:
    SecondField = Request.Form("Component")

With all of this code:
   MyString = Request.Form("Component")
   MyArray = Split(MyString,",")
   For i = 0 to Ubound(MyArray) -1
     SecondField = SecondField & "'" & MyArray(i) & "',"
   Next
   SecondField = Left(SecondField,Len(SecondField)-1)
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now