Problem filtering recordset by values held in array

Discussion in 'ASP General' started by Tim Pollard, Dec 1, 2004.

  1. Tim Pollard

    Tim Pollard Guest

    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,
    2,
    3,
    4,

    Such that what appears on my output page is:




    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
     
    Tim Pollard, Dec 1, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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,
    > 2,
    > 3,
    > 4,
    >
    > Such that what appears on my output page is:
    >
    >
    >
     
    Ray Costanzo [MVP], Dec 1, 2004
    #2
    1. Advertising

  3. Tim Pollard

    Tim Pollard Guest

    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
     
    Tim Pollard, Dec 2, 2004
    #3
  4. 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.
     
    Bob Barrows [MVP], Dec 2, 2004
    #4
  5. 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" <> wrote in message
    news:...
    > 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
     
    Ray Costanzo [MVP], Dec 2, 2004
    #5
  6. Tim Pollard

    Tim Pollard Guest

    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.

    ,
    ,

    etc.

    Sorry if I'm being dim!

    Thanks again for the input.

    Regards

    Tim
     
    Tim Pollard, Dec 2, 2004
    #6
  7. "Tim Pollard" <> wrote in message
    news:...

    > If I do exactly what Ray says all is OK.


    That holds true in every facet of life. :p

    Ray at work
     
    Ray Costanzo [MVP], Dec 2, 2004
    #7
  8. Tim Pollard

    Tim Pollard Guest

    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
     
    Tim Pollard, Dec 3, 2004
    #8
  9. You're quite welcome. :]

    Ray at work

    "Tim Pollard" <> wrote in message
    news:...
    > 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
     
    Ray Costanzo [MVP], Dec 3, 2004
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Srinivasan S Saripalli
    Replies:
    1
    Views:
    375
    Carl Prothman [MVP]
    Dec 15, 2003
  2. Mike Hutton
    Replies:
    1
    Views:
    415
    Ian Suttle
    Nov 11, 2004
  3. rh
    Replies:
    6
    Views:
    15,983
    Jared Tullis
    Jan 17, 2005
  4. nobody
    Replies:
    1
    Views:
    542
    Venkatesh
    Apr 26, 2006
  5. Hung Huynh
    Replies:
    8
    Views:
    317
    Bob Barrows
    Sep 24, 2003
Loading...

Share This Page