By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Problem filtering recordset by values held in array

P: n/a
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, a@address.com
2, b@address.com
3, c@address.com
4, d@address.com

Such that what appears on my output page is:
a@address.com
b@address.com
d@address.com

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
Jul 22 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
"Tim Pollard" <ti*************@btinternet.com> wrote in message
news:f3**************************@posting.google.c om...
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, a@address.com
2, b@address.com
3, c@address.com
4, d@address.com

Such that what appears on my output page is:
a@address.com
b@address.com
d@address.com

Jul 22 '05 #2

P: n/a
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
Jul 22 '05 #3

P: n/a
Tim Pollard wrote:
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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #4

P: n/a
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

"Tim Pollard" <ti*************@btinternet.com> wrote in message
news:f3**************************@posting.google.c om...
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

Jul 22 '05 #5

P: n/a
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.

a@bc.com,
b@de.com,
c@gh.com
etc.

Sorry if I'm being dim!

Thanks again for the input.

Regards

Tim
Jul 22 '05 #6

P: n/a

"Tim Pollard" <ti*************@btinternet.com> wrote in message
news:f3**************************@posting.google.c om...
If I do exactly what Ray says all is OK.


That holds true in every facet of life. :P

Ray at work
Jul 22 '05 #7

P: n/a
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
Jul 22 '05 #8

P: n/a
You're quite welcome. :]

Ray at work

"Tim Pollard" <ti*************@btinternet.com> wrote in message
news:f3**************************@posting.google.c om...
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

Jul 22 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.