Filter results of recordset where value is IN ()

I have done this type of recordset before but I can't seem to find what is wrong with the code.
I am filtering a table based on the results that I get from a list.

When I run my SQL in the SQL Manager I get 3 results, this is the code I run in the SQL console.

SELECT * FROM BillingLines WHERE Id IN  (4603, 4604, 4605)

Open in new window


Then I try to use code someone here help me build. And actually works on a different page for a different recordset. I just can't find whats wrong with this one:

<%
Dim BillingItems__MMColParam2
BillingItems__MMColParam2 = "0"
If (Session("DetailsList")   <> "") Then 
  BillingItems__MMColParam2 = Session("DetailsList") 
End If
%>

<%
Dim BillingItems
Dim BillingItems_cmd
Dim BillingItems_numRows

Set BillingItems_cmd = Server.CreateObject ("ADODB.Command")
BillingItems_cmd.ActiveConnection = MM_bluedot_STRING
BillingItems_cmd.CommandText = "SELECT * FROM BillingLines WHERE Id IN (" & BillingItems__MMColParam2 & " ) " 
BillingItems_cmd.Prepared = true
BillingItems_cmd.Parameters.Append BillingItems_cmd.CreateParameter("param1", 5, 1, -1, BillingItems__MMColParam) ' adDouble


Set BillingItems = BillingItems_cmd.Execute
BillingItems_numRows = 0
%>

Open in new window

LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

AleksAuthor Commented:
I tried the code blow as well but its not working either  :$  

<%
Dim BillingItems__MMColParam
BillingItems__MMColParam = "0"
If (Session("DetailsList") <> "") Then 
  BillingItems__MMColParam = Session("DetailsList")
End If
%>
<%
Dim BillingItems
Dim BillingItems_cmd
Dim BillingItems_numRows

Set BillingItems_cmd = Server.CreateObject ("ADODB.Command")
BillingItems_cmd.ActiveConnection = MM_bluedot_STRING
BillingItems_cmd.CommandText = "SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")" 

BillingItems_cmd.Prepared = true

Set BillingItems = BillingItems_cmd.Execute
BillingItems_numRows = 0
%>

Open in new window


In this test the value of <%=Session("DetailsList")%>  =   4607, 4608, 4609
Big MontyWeb Ninja at largeCommented:
what is "not working"?
AleksAuthor Commented:
Its not returning any results, when it should. the session has those 3 values which should match the ID .. Ill double check but pretty sure  they should.
(Second code doesn't return errors)
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

AleksAuthor Commented:
I am correct, when I run the query in SQL Enterprise it returns results, while the recordsets don't.
Big MontyWeb Ninja at largeCommented:
try changing

Set BillingItems = BillingItems_cmd.Execute

to

Set BillingItems = BillingItems_cmd.Open
AleksAuthor Commented:
Microsoft VBScript runtime  error '800a01b6'

Object doesn't support this property or method: 'Open'

/bluedot/Intranet/billing/invoice_SPsaveitems.asp, line 52
Big MontyWeb Ninja at largeCommented:
sorry, over tired today, try:

Set BillingItems = Server.CreateObject("ADODB.RecordSet")
BillingItems.Open BillingItems_cmd
AleksAuthor Commented:
keeps coming back as no records  :(
Big MontyWeb Ninja at largeCommented:
if you do a

Response.Write ""SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")"

what gets written out?
AleksAuthor Commented:
Microsoft VBScript compilation  error '800a03ee'

Expected ')'

/bluedot/Intranet/billing/invoice_SPsaveitems.asp, line 272
Response.Write(Response.Write "SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")")
Big MontyWeb Ninja at largeCommented:
you have Response.Write(Response.Write twice :)
AleksAuthor Commented:
Nop. I just copied your code and place it in the body of the form:

 <%=Response.Write ""SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")"%>

Open in new window

Big MontyWeb Ninja at largeCommented:
remember, <%= is shorthand for Response.Write, so you have a double there. remove the equals char and it'll work
AleksAuthor Commented:
I did this:

<% Response.Write ""SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")"%>

Open in new window


I get this error:

Microsoft VBScript compilation  error '800a0401' 

Expected end of statement 

/bluedot/Intranet/billing/invoice_SPsaveitems.asp, line 272 
Response.Write ""SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")"

Open in new window

Big MontyWeb Ninja at largeCommented:
do

Response.Write "SELECT *  FROM BillingLines  WHERE Id IN(" & BillingItems__MMColParam & ")"
AleksAuthor Commented:
It shows this:

SELECT * FROM BillingLines WHERE Id IN(0)
Big MontyWeb Ninja at largeCommented:
ok that tells us your session variable

Session("DetailsList")

is empty. where do you set it?

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:
OK. This is weird, but once I changed the validation for the checkboxes. This is what I got on the page now:

This is the session values: 4607, 4608, 4609
This is your code: SELECT * FROM BillingLines WHERE Id IN(4607, 4608, 4609)

Seems to be working now. Ill continue and if something comes up Ill open a different ticket. Thanks !
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.