asked on

Recordset to select id IN a comma separated list

I am passing a value from a list to another page. This page takes a comma separated value (box) and uses the numbers to filter out a recordset (parties).

If I pass only ONE id (int), then it works fine, but the moment I pass more than one I get an error.

The list could be one or more values and look something like: 2323,2355,25422,11,355

The recordset should show all the records for which the 'userid' equals each one of the above, so it should return 5 recordsets.

This is my current code which is not working:

<%
Dim Parties__MMColParam
Parties__MMColParam = "0"
If (Request("box") <> "") Then 
  Parties__MMColParam = Request("box")
End If
%>
<%
Dim Parties
Dim Parties_cmd
Dim Parties_numRows

Set Parties_cmd = Server.CreateObject ("ADODB.Command")
Parties_cmd.ActiveConnection = MM_bluedot_STRING
Parties_cmd.CommandText = "SELECT Userid, Firstnm, Lastnm,DOB  FROM users  where userid IN (?)" 
Parties_cmd.Prepared = true
Parties_cmd.Parameters.Append Parties_cmd.CreateParameter("param1", 5, 1, -1, Parties__MMColParam) ' adDouble

Set Parties = Parties_cmd.Execute
Parties_numRows = 0
%>

Open in new window

I am not sure about the ASP code although the query on SQL works fine:

SELECT Userid, Firstnm, Lastnm,DOB  FROM users 
where UserId IN (30638,32121,3123)

Open in new window

I am using classic ASP and SQL 2008

I am in kind of a rush for this before the end of the day, hopefully someone can help.

Thanks in advance.

lcohan

I think you would need to use something like
...
string[] values = new string[]{"a", "b", "c"};
...
in order to do that or build an array like below to pass a multi value parameter:

ArrayList<ParameterValue> parmValues;

      for(Map.Entry<String,String> entry:reportParams.entrySet()) {
            //is it multi-value?
            if(entry.getValue().contains(",")) {
                  //you can add multiple ParameterValues under the same name with different values //
                  for(String mval:entry.getValue().split(",")) {
                        ParameterValue pv = new ParameterValue();
                        pv.setName(entry.getKey());
                        pv.setValue(mval.trim());
                        parmValues.add(pv);
                  }
            } else {
                  //no, just a single value
                  ParameterValue pv = new ParameterValue();
                  pv.setName(entry.getKey());
                  pv.setValue(entry.getValue());
                  parmValues.add(pv);
            }
      }

Aleks

ASKER

I have no clue what that means. Sorry. I am trying to find a way to modify my recordset so that it returns the values I passed in a comma delimited value.

lcohan

Well unfortunately there's no magic function or code control that can do it for you therefore you need to generate this string manually in your code. Collect all selected IDs into an array or something, then loop it to append it one by one and finally pass this string to the parameter.

Aleks

ASKER

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

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

Start Free Trial

Aleks

ASKER

Checking .. this might work.

Aleks

ASKER

Excellent !!