asked on

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

Surone1

what is on line 294 of the asp script?

Aleks

ASKER

rs_qnrgroupcount_cmd.Parameters.Append rs_qnrgroupcount_cmd.CreateParameter("param2", 5, 1, -1, rs_qnrgroupcount__MMColParam2) ' adDouble

Aleks

ASKER

The second parameter is the one giving the error. I want to select count (a.id) ... when 1713 IN (30324, 1713, 5001, 22278, 30351)

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

Thank you !

Aleks

ASKER

Great !

Aleks

ASKER

You are right BigMonty. I tried this:



<%
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 Monty

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

Aleks

ASKER

ok .. thanks. Ill test that.