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.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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);
            }
      }
0
AleksAuthor Commented:
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.
0
lcohanDatabase AnalystCommented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

AleksAuthor Commented:
:(
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Icohan - this is for classic asp, not dot net which is what your code is.

You cannot use parameterized queries to set up a IN statement, you'll need to either create an array, then loop through each number and build your parameter definition one by one, or if you're in a rush, just concatenate the list of id's directly in the sql:


<%
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__MMColParam & ")" 

Parties_cmd.Prepared = true

Set Parties = Parties_cmd.Execute
Parties_numRows = 0
%>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Checking .. this might work.
0
AleksAuthor Commented:
Excellent !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.