Creating Oracle Frindly strings for an IN statement in a Proc

[ajacode]

Working with Oracle 8.17

DotNet Web Application pass parameters to an Oracle stored Procedured which then populates a Crystal 8.5 Report:


Using a proc which has an IN Parameter p_event which is a varchar2

We want to pass it mutiple values using a multi select HTML list box and pass it to the SQL statement:

where eventID IN (p_event)

The proc works when I hard code :

where eventID IN ('124','456','300')

it returns data for all 3 events

BUT wher are populating a WEB FORM drop list for the Multiple Select Capablle Events list box with the following SQL


SELECT DISTINCT
E.TIXEVENTID,
E.TIXEVENTTITLESHORT
FROM TIXEVENTS E
Where E.TixEventType = 0
GROUP BY E.TIXEVENTID,
E.TIXEVENTTITLESHORT
ORDER BY E.TIXEVENTTITLESHORT

The List Box Displays E.TIXEVENTTITLESHORT

It passes the hidden column E.TIXEVENTID,

We thought if we populate the values of E.TIXEVENTID,

Instead of Like this

TIXEVENTID TIXEVENTTITLESHORT
124, Get Up
456 Take Shower
300 Get Dressed

we think if we set the list with the proper ' ' quotes for Oracle to use in the IN staement

TIXEVENTID TIXEVENTTITLESHORT
'124', Get Up
'456', Take Shower
'300', Get Dressed


How would we modify this SQL to pass the 'Value1','Value2','Value3' instead of Value1,Value2,Value3

Thanks Beforehand

AJA

 

[Turkbear]

Hi,
If you want to use JavaScript you can convert the contents
of an array of selected values to a comma-delimited, quote-enclosed string :

<SCRIPT Language=&quot;JavaScript&quot;>
function GetData(form){
var picks = form.nbr     // The name of the select list
choices =  new Array()
var indx = 0
  for (var i = 0; i < picks.length; i++ ){
    if (picks.options[i].selected == true) {
        choices[indx] =   '&quot;' +   picks.options[i].value  + '&quot;'
    indx++
    }
 }

str3 =    choices.join(&quot;,&quot;)
}
</SCRIPT>

When done, str3 will be a delimited list of quoted values which can then be passed to Oracle..

This can be done with asp as well, I believe, but the methods would be different, of course...

hth,