asked on

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

Ryan Chong

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

Aleks

ASKER

That would not work because I don't know how many numbers there will be, could be one, could be 20 :#

ASKER CERTIFIED SOLUTION

Ryan Chong

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