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> </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
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> </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