Problem filtering recordset by values held in array

T

Tim Pollard

Hi

I am trying to filter a table of users to select only those records
whose roleID matches a value in an array. There could be any number of
IDs held in the array from one to a few hundred. The array is
generated by splitting a comma delimited memo field from a second
table in an Access DB. I can split the memo field OK, I can
response.write its values, but what I now want to do is add a value
from another table to my reponse write loop.

ie. memo field might contain IDs 1, 2, 4

This splits into array
1
2
4

And I want to match up with contents in the non-autonumber field of a
table wchich might look something like:
tblRoles
RoleID(autonumber field), emailaddress
1, (e-mail address removed)
2, (e-mail address removed)
3, (e-mail address removed)
4, (e-mail address removed)

Such that what appears on my output page is:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

i.e. no c because 3 is not in the array.


Full code of page so far:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connMomentaDB.asp" -->
<%
Dim rsLastList__MMColParam
rsLastList__MMColParam = "1"
If (Session("LastList") <> "") Then
rsLastList__MMColParam = Session("LastList")
End If
%>
<%
Dim rsLastList
Dim rsLastList_numRows

Set rsLastList = Server.CreateObject("ADODB.Recordset")
rsLastList.ActiveConnection = MM_connMomentaDB_STRING
rsLastList.Source = "SELECT ListID, MailingList FROM tblxCampPers
WHERE ListID = " + Replace(rsLastList__MMColParam, "'", "''") + ""
rsLastList.CursorType = 0
rsLastList.CursorLocation = 2
rsLastList.LockType = 1
rsLastList.Open()

rsLastList_numRows = 0
%>
<% varMailingList = split(rsLastList("MailingList"), ", ", -1, 1 ) %>
<%
Dim rsRoles
Dim rsRoles_numRows

Set rsRoles = Server.CreateObject("ADODB.Recordset")
rsRoles.ActiveConnection = MM_connMomentaDB_STRING
rsRoles.Source = "SELECT RoleID, emailadd FROM tblRoles"
rsRoles.CursorType = 0
rsRoles.CursorLocation = 2
rsRoles.LockType = 1
rsRoles.Open()

rsRoles_numRows = 0
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<p>Your mailing list has been created and is list number
<%=(rsLastList.Fields.Item("ListID").Value)%>
</p>
<p>
' The section below currently writes the roleID, but I want it to
write the email address instead
<% for myCounter=0 to ubound(varMailingList)
response.write varMailingList(myCounter) & "<BR>" & vbcrlf
next
%>

</p>
<p>&nbsp;</p>
</body>
</html>
<%
rsLastList.Close()
Set rsLastList = Nothing
%>
<%
rsRoles.Close()
Set rsRoles = Nothing
%>

I presume I need a second loop within the varMailingList loop which
returns the matching record in rsRoles?

Any help gratefully received!

Thanks in advance

TimP
 
R

Ray Costanzo [MVP]

If you have a string that starts out as 1, 2, 4, instead of splitting it
into an array, keep it as is and use it in your WHERE clause.

Example:

strNumbers = "1, 2, 4"
sSQL = "SELECT RoleID, emailadd FROM tblRoles WHERE RoleID IN (" &
strNumbers & ")"
'which would result in a query as:
'SELECT RoleID, emailadd FROM tblRoles WHERE RoleID IN (1, 2, 4)

Ray at work
 
T

Tim Pollard

Hi Ray

Thanks for the reply and your suggestion.

What you say makes sense, but when I tried it I got a data type
mismatch error - because the RoleID field is a number (access
autonumber) whilst the string of numbers is text, having been held in
a memo field.

Do you know if it is possible to turn the text field back in to a
string of numbers when you define it as a string?

Many thanks for taking the trouble to reply.

Regards

Tim
 
B

Bob Barrows [MVP]

Tim said:
Hi Ray

Thanks for the reply and your suggestion.

What you say makes sense, but when I tried it I got a data type
mismatch error - because the RoleID field is a number (access
autonumber) whilst the string of numbers is text, having been held in
a memo field.

Show us what you did. Ray's suggestion should have worked, so it appears as
if you did something different. Verify that your sql statement is correct by
response.writing it.

Bob Barrows
 
R

Ray Costanzo [MVP]

CAn you Response.Write your final SQL query before populating the recordset
and then post if back here?

Eg.

sSQL = "SELECT RoleID, emailadd FROM tblRoles WHERE RoleID IN (" &
strNumbers & ")"
RESPONSE.WRITE SSQL
RESPONSE.END

Ray at work
 
T

Tim Pollard

Hi Ray & Co

Thanks for your responses.

I'm normally a DWMX04 user so I'm not used to hand coding my sql,
which is probably why I'm having a spot of bother.

If I do exactly what Ray says all is OK.

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connMomentaDB.asp" -->
<%
Dim rsLastList__MMColParam
rsLastList__MMColParam = "1"
If (Session("LastList") <> "") Then
rsLastList__MMColParam = Session("LastList")
End If
%>
<%
Dim rsLastList
Dim rsLastList_numRows

Set rsLastList = Server.CreateObject("ADODB.Recordset")
rsLastList.ActiveConnection = MM_connMomentaDB_STRING
rsLastList.Source = "SELECT ListID, MailingList FROM tblxCampPers
WHERE ListID = " + Replace(rsLastList__MMColParam, "'", "''") + ""
rsLastList.CursorType = 0
rsLastList.CursorLocation = 2
rsLastList.LockType = 1
rsLastList.Open()

rsLastList_numRows = 0
%>
<% strNumbers = (rsLastList.Fields.Item("MailingList").Value)
sSQL = "SELECT RoleID, emailadd FROM tblRoles WHERE RoleID IN (" &
strNumbers & ")"
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<p>Your mailing list has been created and is list number
<%=(rsLastList.Fields.Item("ListID").Value)%>
</p>
<p>
</p>
<p><%
Response.Write sSQL & "<BR>" & vbcrlf
%>
</p>
<p>&nbsp;</p>
</body>
</html>
<%
rsLastList.Close()
Set rsLastList = Nothing
%>

This produces a page like this:

Your mailing list has been created and is list number 15

SELECT RoleID, emailadd FROM tblRoles WHERE RoleID IN (428, 198, 796,
1251, 51, 707, 964, 496)

So far so good! The problem comes when I try to define a DW recordset
based on the same sql. If I do this I get no error now but when I try
to create a repeat region of my email addresses it is blank.

Presumably I need to do this latter step by hand also.

All I want is a simple list of email addresses e.g.

(e-mail address removed),
(e-mail address removed),
(e-mail address removed)
etc.

Sorry if I'm being dim!

Thanks again for the input.

Regards

Tim
 
T

Tim Pollard

Hi Ray

I've been back and done what you said again, carefully this time, and
lo and behold it works perfectly.

Thank you very much for taking the time to help me.

Regards

TP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,831
Latest member
HealthSmartketoReviews

Latest Threads

Top