Problem with recordset

I am using DW CC2014 to create a recordset. Yet, I am getting an error. When I run the query in MS SQL Manager it returns the correct values.

-- error displayed --

ADODB.Command error '800a0d5d'

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

/bluedot/includes/bdot/recordsets.asp, line 294

-- recordset code --


<%
Dim rs_qnrgroupcount__MMColParam
rs_qnrgroupcount__MMColParam = "0"
If (Session("FirmId")  <> "") Then
  rs_qnrgroupcount__MMColParam = Session("FirmId")
End If
%>
<%
Dim rs_qnrgroupcount__MMColParam2
rs_qnrgroupcount__MMColParam2 = "0"
If (rs_qnrgroup("GuestIds") <> "") Then
  rs_qnrgroupcount__MMColParam2 = rs_qnrgroup("GuestIds")
End If
%>

<%
Dim rs_qnrgroupcount
Dim rs_qnrgroupcount_cmd
Dim rs_qnrgroupcount_numRows

Set rs_qnrgroupcount_cmd = Server.CreateObject ("ADODB.Command")
rs_qnrgroupcount_cmd.ActiveConnection = MM_bluedot_STRING
rs_qnrgroupcount_cmd.CommandText = "SELECT COUNT (a.Id) AS total FROM QnrsMailed a inner join users as b on a.sentby = b.userid WHERE a.Firmid = ? and a.Originatedby IN (?) AND a.IsAccepted = 2"
rs_qnrgroupcount_cmd.Prepared = true
rs_qnrgroupcount_cmd.Parameters.Append rs_qnrgroupcount_cmd.CreateParameter("param1", 5, 1, -1, rs_qnrgroupcount__MMColParam) ' adDouble
rs_qnrgroupcount_cmd.Parameters.Append rs_qnrgroupcount_cmd.CreateParameter("param2", 5, 1, -1, rs_qnrgroupcount__MMColParam2) ' adDouble

Set rs_qnrgroupcount = rs_qnrgroupcount_cmd.Execute
rs_qnrgroupcount_numRows = 0
%>

---

the values are replaced with the following:

MMColParam  = 2
MMColParam2 = 1713
rs_qnrgroup("GuestIds")  = 30324, 1713, 5001, 22278, 30351

-- the query when ran replacing values to test on SQL Manager goes like --

SELECT COUNT (a.Id) AS total
FROM QnrsMailed a  INNER JOIN users as b on a.sentby = b.userid
WHERE a.Firmid = 2 and a.Originatedby IN (30324, 1713, 5001, 22278, 30351) AND a.IsAccepted = 2

-- the returned value is:  1

So it works well there. So .. there is something wrong with my ASP code.

Help is appreciated !  I am stuck !

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

Surone1Commented:
what is on line 294 of the asp script?
AleksAuthor Commented:
rs_qnrgroupcount_cmd.Parameters.Append rs_qnrgroupcount_cmd.CreateParameter("param2", 5, 1, -1, rs_qnrgroupcount__MMColParam2) ' adDouble
AleksAuthor Commented:
The second parameter is the one giving the error. I want to select count (a.id) ... when 1713 IN (30324, 1713, 5001, 22278, 30351)
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Big MontyWeb Ninja at largeCommented:
instead of making the list of guestIDs a parameter, just concatenate it into the sql. since it's already coming from your database via a recordset, you know it's safe.

<%
Dim rs_qnrgroupcount__MMColParam
rs_qnrgroupcount__MMColParam = "0"
If (Session("FirmId")  <> "") Then 
  rs_qnrgroupcount__MMColParam = Session("FirmId") 
End If
%>
<%
Dim rs_qnrgroupcount__MMColParam2
rs_qnrgroupcount__MMColParam2 = "0"
If (rs_qnrgroup("GuestIds") <> "") Then 
  rs_qnrgroupcount__MMColParam2 = rs_qnrgroup("GuestIds")
End If
%>

<%
Dim rs_qnrgroupcount
Dim rs_qnrgroupcount_cmd
Dim rs_qnrgroupcount_numRows

Set rs_qnrgroupcount_cmd = Server.CreateObject ("ADODB.Command")
rs_qnrgroupcount_cmd.ActiveConnection = MM_bluedot_STRING
rs_qnrgroupcount_cmd.CommandText = "SELECT COUNT (a.Id) AS total FROM QnrsMailed a inner join users as b on a.sentby = b.userid WHERE a.Firmid = ? and a.Originatedby IN (" & rs_qnrgroupcount__MMColParam2 & " ) AND a.IsAccepted = 2" 
rs_qnrgroupcount_cmd.Prepared = true
rs_qnrgroupcount_cmd.Parameters.Append rs_qnrgroupcount_cmd.CreateParameter("param1", 5, 1, -1, rs_qnrgroupcount__MMColParam) ' adDouble


Set rs_qnrgroupcount = rs_qnrgroupcount_cmd.Execute
rs_qnrgroupcount_numRows = 0
%>

Open in new window


the error is because the IN statement in your sql is selecting a list of numbers, and you're defining the parameter as a varchar (a string of numbers). in order to use parameters, you would need to parse out each number from your list and add each number as its own parameter.

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:
Thank you !
AleksAuthor Commented:
Great !
AleksAuthor Commented:
You are right BigMonty.  I tried this:

<!--#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 (" & rs_qnrgroup__MMColParam2 & " )        AND a.IsAccepted = 2 ORDER BY a.SentOn DESC;"
rs_inboxgroup_cmd.Prepared = true
rs_inboxgroup_cmd.Prepared = true
rs_inboxgroup_cmd.Parameters.Append rs_inboxgroup_cmd.CreateParameter("param1", 5, 1, -1, rs_inboxgroup__MMColParam) ' adDouble

Set rs_inboxgroup = rs_inboxgroup_cmd.Execute
rs_inboxgroup_numRows = 0
%>

and it seems to work. Do you see anything wrong in this code ?
Big MontyWeb Ninja at largeCommented:
as long as your variable rs_qnrgroup__MMColParam2  has some kind of value defined, it'll work. if there are scenarios where it will have NO value, it'll throw a sql syntax error
AleksAuthor Commented:
ok .. thanks. Ill test that.
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.