Problem creating recordset

I have the following query (MS SQL 2008)

SELECT userid, TransferID, Type, Transvalue
FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType
WHERE FirmID = 2  and (UserType ='attorney' OR UserType ='paralegal')
AND userid IN (30836,30351,1713)

It runs fine and brings me back the 3 values above.

Then, using DW I create a recordset and simply replace the numbers with a parameter. The value of the parameter is the numbers above separated by a comma.

The problem is that I get an error when I try to run the page and display the values of the resulting recordset.

ADODB.Command error '800a0d5d'

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

/bluedot/Intranet/Cases/links_SPaddusers.asp, line 57

and if I change the type to 'text' then 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 '31176, 22278, 5001' to data type int.

/bluedot/Intranet/Cases/links_SPaddusers.asp, line 59


So either way I can't seem to get what I need.


The result is a series of numbers separated by a comma. but it is not comparing the 'userid' against each number, instead it seems to be comparing it to the whole string.  :(  
Is there a way to change the recordset so it compares against each comma separated value and returns the correct records ?

This is the recordset using text type:

<%
Dim rs_users__MMColParam
rs_users__MMColParam = "0"
If (Session("FirmId")  <> "") Then 
  rs_users__MMColParam = Session("FirmId") 
End If
%>
<%
Dim rs_users__MMColParam2
rs_users__MMColParam2 = "0"
If (Request("box") <> "") Then 
  rs_users__MMColParam2 = Request("box")
End If
%>
<%
Dim rs_users
Dim rs_users_cmd
Dim rs_users_numRows

Set rs_users_cmd = Server.CreateObject ("ADODB.Command")
rs_users_cmd.ActiveConnection = MM_bluedot_STRING
rs_users_cmd.CommandText = "SELECT userid, TransferID, Type, Firmid, Transvalue,OrderNum FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType WHERE FirmID = ?  and (UserType ='attorney' OR UserType ='paralegal')  AND userid IN (?)" 
rs_users_cmd.Prepared = true
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param1", 5, 1, -1, rs_users__MMColParam) ' adDouble
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param2", 200, 1, 255, rs_users__MMColParam2) ' adVarChar

Set rs_users = rs_users_cmd.Execute
rs_users_numRows = 0
%>

Open in new window


This is the recordset using a number type for parameter 2 which is as I mentioned a series of numbers delimited by a comma.

<%
Dim rs_users__MMColParam
rs_users__MMColParam = "0"
If (Session("FirmId")  <> "") Then 
  rs_users__MMColParam = Session("FirmId") 
End If
%>
<%
Dim rs_users__MMColParam2
rs_users__MMColParam2 = "0"
If (Request("box") <> "") Then 
  rs_users__MMColParam2 = Request("box")
End If
%>
<%
Dim rs_users
Dim rs_users_cmd
Dim rs_users_numRows

Set rs_users_cmd = Server.CreateObject ("ADODB.Command")
rs_users_cmd.ActiveConnection = MM_bluedot_STRING
rs_users_cmd.CommandText = "SELECT userid, TransferID, Type, Firmid, Transvalue,OrderNum FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType WHERE FirmID = ?  and (UserType ='attorney' OR UserType ='paralegal')  AND userid IN (?)" 
rs_users_cmd.Prepared = true
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param1", 5, 1, -1, rs_users__MMColParam) ' adDouble
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param2", 5, 1, -1, rs_users__MMColParam2) ' adDouble

Set rs_users = rs_users_cmd.Execute
rs_users_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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
i will go with the number approach.... but will not use the IN statement, but split the ID Numbers into different parameters... like:
<%
Dim rs_users__MMColParam
rs_users__MMColParam = "0"
If (Session("FirmId")  <> "") Then 
  rs_users__MMColParam = Session("FirmId") 
End If
%>
<%
Dim rs_users__MMColParam2
rs_users__MMColParam2 = "0"
If (Request("box") <> "") Then 
  rs_users__MMColParam2 = Request("box")
End If
ArrParam2 = Split(rs_users__MMColParam2, ",")

%>
<%
Dim rs_users
Dim rs_users_cmd
Dim rs_users_numRows

Set rs_users_cmd = Server.CreateObject ("ADODB.Command")
rs_users_cmd.ActiveConnection = MM_bluedot_STRING
rs_users_cmd.CommandText = "SELECT userid, TransferID, Type, Firmid, Transvalue,OrderNum FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType WHERE FirmID = ?  and (UserType ='attorney' OR UserType ='paralegal')  AND (userid = (?) OR userid = (?) OR userid = (?)) " 
rs_users_cmd.Prepared = true
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param1", 5, 1, -1, rs_users__MMColParam) ' adDouble
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param2", 5, 1, -1, ArrParam2(0)) ' adDouble
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param3", 5, 1, -1, ArrParam2(1)) ' adDouble
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param4", 5, 1, -1, ArrParam2(2)) ' adDouble
Set rs_users = rs_users_cmd.Execute
rs_users_numRows = 0
%>

Open in new window

OR a quick fix to modify further...
<%
Dim rs_users__MMColParam
rs_users__MMColParam = "0"
If (Session("FirmId")  <> "") Then 
  rs_users__MMColParam = Session("FirmId") 
End If
%>
<%
Dim rs_users__MMColParam2
rs_users__MMColParam2 = "0"
If (Request("box") <> "") Then 
  rs_users__MMColParam2 = Request("box")
End If
%>
<%
Dim rs_users
Dim rs_users_cmd
Dim rs_users_numRows

Set rs_users_cmd = Server.CreateObject ("ADODB.Command")
rs_users_cmd.ActiveConnection = MM_bluedot_STRING
rs_users_cmd.CommandText = "SELECT userid, TransferID, Type, Firmid, Transvalue,OrderNum FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType WHERE FirmID = ?  and (UserType ='attorney' OR UserType ='paralegal')  AND userid IN (" & rs_users__MMColParam2 & ")" 
rs_users_cmd.Prepared = true
rs_users_cmd.Parameters.Append rs_users_cmd.CreateParameter("param1", 5, 1, -1, rs_users__MMColParam) ' adDouble

Set rs_users = rs_users_cmd.Execute
rs_users_numRows = 0
%>

Open in new window

AleksAuthor Commented:
That would not work because I don't know how many numbers there will be, could be one, could be 20  :#
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>That would not work because I don't know how many numbers there will be, could be one, could be 20  :#

The second suggestion in my comment: ID: 40982900 should fixed this issue, IF what user entered in "box" field is in format of  "  1, 2, 3, 4, N... " as we directly read the posted element from the form

>>rs_users_cmd.CommandText = "SELECT userid, TransferID, Type, Firmid, Transvalue,OrderNum FROM Users  Inner Join TransfertoForms ON  TransfertoForms.Type = Users.UserType WHERE FirmID = ?  and (UserType ='attorney' OR UserType ='paralegal')  AND userid IN (" & rs_users__MMColParam2 & ")"

hope that clarifies

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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

AleksAuthor Commented:
Ill try it tomorrow, thanks !
chaauCommented:
The only problem with the second Ryan's approach is the SQL Injections. If you enter this in the "box":
1,2); DELETE FROM  Users  --

Open in new window

(do not do this)
Then your Users table will be cleared
AleksAuthor Commented:
Then what are my options ?
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.