Problem creating recordset

I am using Classic ASP. I created a recordset using DW. But I am getting an error.

-- recordset code --

<!--#Inbox group-->
<%
Dim rs_inboxgroup__MMColParam
rs_inboxgroup__MMColParam = "0"
If (Session("firmid") <> "") Then
  rs_inboxgroup__MMColParam = Session("firmid")
End If
%>
<%
Dim rs_inboxgroup__MMColParam2
rs_inboxgroup__MMColParam2 = "0"
If (rs_qnrgroup("GuestIds") <> "") Then
  rs_inboxgroup__MMColParam2 = rs_qnrgroup("GuestIds")
End If
%>
<%
Dim rs_inboxgroup
Dim rs_inboxgroup_cmd
Dim rs_inboxgroup_numRows

Set rs_inboxgroup_cmd = Server.CreateObject ("ADODB.Command")
rs_inboxgroup_cmd.ActiveConnection = MM_bluedot_STRING
rs_inboxgroup_cmd.CommandText = "SELECT a.Id ,         a.QnreId ,         a.FirmId ,         a.Subj ,         a.BodyText ,         a.SentBy ,         a.SentTo ,         a.SentOn ,         a.QnreData ,         a.IsAccepted ,         a.AcceptedOn ,         a.SenderEmail ,         b.UserId ,         b.LastNm ,         b.FirstNm ,         b.Archivedcont ,         b.ContactStatus FROM QnrsMailed a         INNER JOIN Users AS b ON a.SentBy = b.UserId WHERE a.FirmId = ?         AND a.Originatedby IN (?)         AND a.IsAccepted = 2 ORDER BY a.SentOn DESC;"
rs_inboxgroup_cmd.Prepared = true
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param1", 5, 1, -1, rs_inboxgroup__MMColParam) ' adDouble
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param2", 5, 1, -1, rs_inboxgroup__MMColParam2) ' adDouble

Set rs_inboxgroup = rs_inboxgroup_cmd.Execute
rs_inboxgroup_numRows = 0
%>

----

Session("Firmid")   =  2
rs_qnrgroup("GuestIds")  = 1713, 20323,233,11

Seems like it is having an issue with filtering out the  a.Originatedby IN (?)  

Any suggestions ?
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.

AleksAuthor Commented:
The error is:  ADODB.Command error '800a0d5d'

Application uses a value of the wrong type for the current operation.

/BlueDot/Intranet/inbox/index.asp, line 91
0
AleksAuthor Commented:
I also tried setting it to be  a 'text' instead of an int. But I get another error:

But I get this error:  

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value '1713, 31176, 30324, 5001' to data type int.

/BlueDot/Intranet/inbox/index.asp, line 93


In this scenario my code is:

<%
Dim rs_inboxgroup__MMColParam
rs_inboxgroup__MMColParam = "0"
If (Session("firmid") <> "") Then
  rs_inboxgroup__MMColParam = Session("firmid")
End If
%>
<%
Dim rs_inboxgroup__MMColParam2
rs_inboxgroup__MMColParam2 = "0"
If (rs_qnrgroup("GuestIds") <> "") Then
  rs_inboxgroup__MMColParam2 = rs_qnrgroup("GuestIds")
End If
%>
<%
Dim rs_inboxgroup
Dim rs_inboxgroup_cmd
Dim rs_inboxgroup_numRows

Set rs_inboxgroup_cmd = Server.CreateObject ("ADODB.Command")
rs_inboxgroup_cmd.ActiveConnection = MM_bluedot_STRING
rs_inboxgroup_cmd.CommandText = "SELECT a.Id ,         a.QnreId ,         a.FirmId ,         a.Subj ,         a.BodyText ,         a.SentBy ,         a.SentTo ,         a.SentOn ,         a.QnreData ,         a.IsAccepted ,         a.AcceptedOn ,         a.SenderEmail ,         b.UserId ,         b.LastNm ,         b.FirstNm ,         b.Archivedcont ,         b.ContactStatus FROM QnrsMailed a         INNER JOIN Users AS b ON a.SentBy = b.UserId WHERE a.FirmId = ?         AND a.Originatedby IN (?)         AND a.IsAccepted = 2 ORDER BY a.SentOn DESC;"
rs_inboxgroup_cmd.Prepared = true
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param1", 5, 1, -1, rs_inboxgroup__MMColParam) ' adDouble
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param2", 200, 1, 255, rs_inboxgroup__MMColParam2) ' adVarChar

Set rs_inboxgroup = rs_inboxgroup_cmd.Execute
rs_inboxgroup_numRows = 0
%>

Either way is not working and I am stuck here  :(
0
hieloCommented:
>>  AND a.Originatedby IN (?)
If you have multiple values, you need one question mark per value.  Since you have:
rs_qnrgroup("GuestIds")  = 1713, 20323, 233, 11

Then you should be using:
 AND a.Originatedby IN (?,?,?,?)

and do a CreateParameter for each of those values.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
this is the same problem we had in this question:

http://www.experts-exchange.com/questions/28695340/Problem-with-recordset.html

you cannot use a comma separated list of values as a single parameter, you would need to either break up the list into individual parameters or just concatenate the list directly into the sql statement
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:
That won't work because we never know how many values there will be.  It is checking to see if 1713 its part of those comma delimited values.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
in your sql statement, instead of doing

AND a.Originatedby IN (?)  

do

listOfIds = "1713, 20323, 233, 11"
AND a.Originatedby IN (" & listOfIds & ")
0
hieloCommented:
>> That won't work because we never know how many values there will be
split the values at the comma.  You should end up with an array with X number of elements.  Then "build" a string the contains X number of question marks separated by a comma, and use it in your IN() clause.

You will also need to iterate X number of times to do CreateParameter for each of those values.  The code below is not tested, but should be very close to what you need:

<%
Dim rs_inboxgroup__MMColParam
rs_inboxgroup__MMColParam = "0"
If (Session("firmid") <> "") Then 
  rs_inboxgroup__MMColParam = Session("firmid")
End If
%>
<%
Dim rs_inboxgroup__MMColParam2
rs_inboxgroup__MMColParam2 = "0"
If (rs_qnrgroup("GuestIds") <> "") Then 
  rs_inboxgroup__MMColParam2 = rs_qnrgroup("GuestIds")
End If
%>
<%
Dim rs_inboxgroup
Dim rs_inboxgroup_cmd
Dim rs_inboxgroup_numRows

Dim temp
temp =  Split(rs_inboxgroup__MMColParam2, ",")


' dinamically build the "?" for the IN() clause
Dim dynamicParams

For i=0 To UBound(Temp)
	dynamicParams = dynamicParams & ",?"
Next

dynamicParams = Mid(dynamicParams,2)

Set rs_inboxgroup_cmd = Server.CreateObject ("ADODB.Command")
rs_inboxgroup_cmd.ActiveConnection = MM_bluedot_STRING
rs_inboxgroup_cmd.CommandText = "SELECT a.Id ,         a.QnreId ,         a.FirmId ,         a.Subj ,         a.BodyText ,         a.SentBy ,         a.SentTo ,         a.SentOn ,         a.QnreData ,         a.IsAccepted ,         a.AcceptedOn ,         a.SenderEmail ,         b.UserId ,         b.LastNm ,         b.FirstNm ,         b.Archivedcont ,         b.ContactStatus FROM QnrsMailed a         INNER JOIN Users AS b ON a.SentBy = b.UserId WHERE a.FirmId = ?         AND a.Originatedby IN (" & dynamicParams & ")         AND a.IsAccepted = 2 ORDER BY a.SentOn DESC;" 
rs_inboxgroup_cmd.Prepared = true
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param1", 5, 1, -1, rs_inboxgroup__MMColParam) ' adDouble

' Here you iterate over the array to append a Parameter value
For i=0 To UBound(temp)
	rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param2", 200, 1, 255, temp(i) ) ' adVarChar
Next

Set rs_inboxgroup = rs_inboxgroup_cmd.Execute
rs_inboxgroup_numRows = 0
%>

Open in new window

0
hieloCommented:
>> AND a.Originatedby IN (" & listOfIds & ")
I strongly advise against that.  The whole point of CreateParameter is to avoid SQL injections.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
normally i would agree with you, but since this is a continuation of the other question I posted, the data is safe as its coming from the database
0
hieloCommented:
>> normally i would agree with you, but since this is a continuation of the other question I posted, the data is safe as its coming from the database
As would I (normally agree with you that is), but:
A. The poster is just learning and he might as well learn how to do things the right way.
B. Just because it is coming from a db does not necessarily imply that it is always safe.  I am not saying/suggesting that you are saying/suggesting that, but the poster may be walking away with the impression that it is the case.

Regards,
Hielo
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.