Professional Web Applications Themes

Can you concatenate recordset values in one field? - Dreamweaver AppDev

Hi I have an Access db which stores, amongst other things, details of small mailing lists, people who have received a particular mailing. Because it is Access, and therefore no stored procedures to do the insert, the identities of the people who have received a mailing are held in a memo field as a comma delimeted string. e.g. ListID Date MailingList 1 01/04/2005 3, 7, 99, 107 2 13/04/2005 2, 15, 27, 136 etc. It is easy enough to extract the recipients from one particular list: select one listID, set the field Mailing list as a variable: varMailingList2 = (rsDraftList.Fields.Item('MailingList').Value) ...

  1. #1

    Default Can you concatenate recordset values in one field?

    Hi I have an Access db which stores, amongst other things, details of small
    mailing lists, people who have received a particular mailing. Because it is
    Access, and therefore no stored procedures to do the insert, the identities of
    the people who have received a mailing are held in a memo field as a comma
    delimeted string. e.g. ListID Date MailingList 1
    01/04/2005 3, 7, 99, 107 2 13/04/2005 2, 15,
    27, 136 etc. It is easy enough to extract the recipients from one
    particular list: select one listID, set the field Mailing list as a variable:
    varMailingList2 = (rsDraftList.Fields.Item('MailingList').Value) then select
    from the people table all those peopleIDs who are in varMailingList2: SELECT *
    FROM qryCampaigns_maillist WHERE RoleID IN (' & varMailingList2 & ')
    However, I now need to find all people contacted from any list between two
    dates. Therefore my method above falls over because there could be one or more
    records in recordset rsDraftList (or indeed none). Anyone have any thoughts on
    the easiest way to do this? Can you concatenate instances in a recordset?
    Thanks in advance for any suggestions.

    tim-p Guest

  2. #2

    Default Re: Can you concatenate recordset values in one field?

    Why are you storing all of these values in a single field. Wouldn't it be
    easier to use separate fields for each element, then your searching would be
    a lot simpler to do

    --
    Regards

    Paul Whitham
    Macromedia Certified Professional for Dreamweaver MX2004
    Valleybiz Internet Design
    [url]www.valleybiz.net[/url]

    Team Macromedia Volunteer for Ultradev/Dreamweaver MX
    [url]www.macromedia.com/support/forums/team_macromedia[/url]

    "tim-p" <webforumsusermacromedia.com> wrote in message
    news:d30p88$308$1forums.macromedia.com...
    > Hi I have an Access db which stores, amongst other things, details of
    small
    > mailing lists, people who have received a particular mailing. Because it
    is
    > Access, and therefore no stored procedures to do the insert, the
    identities of
    > the people who have received a mailing are held in a memo field as a comma
    > delimeted string. e.g. ListID Date MailingList
    1
    > 01/04/2005 3, 7, 99, 107 2 13/04/2005 2,
    15,
    > 27, 136 etc. It is easy enough to extract the recipients from one
    > particular list: select one listID, set the field Mailing list as a
    variable:
    > varMailingList2 = (rsDraftList.Fields.Item('MailingList').Value) then
    select
    > from the people table all those peopleIDs who are in varMailingList2:
    SELECT *
    > FROM qryCampaigns_maillist WHERE RoleID IN (' &amp; varMailingList2 &amp;
    ')
    > However, I now need to find all people contacted from any list between two
    > dates. Therefore my method above falls over because there could be one or
    more
    > records in recordset rsDraftList (or indeed none). Anyone have any
    thoughts on
    > the easiest way to do this? Can you concatenate instances in a recordset?
    > Thanks in advance for any suggestions.
    >

    Paul Whitham TMM Guest

  3. #3

    Default Re: Can you concatenate recordset values in one field?

    Hi Paul Thanks for the reply. I went round the houses in terms of how the
    data should be stored on this one. The process that the user goes through to
    select the list has governed how the data has to be stored: - The user is a
    marketing agency who use this as their new business prospecting system. They
    have a number of search criteria on a search form, which allows them to choose
    people who work in certain industries, have a certain job responsibility and so
    on. - The search results page may therefore contain none, one or more results
    (sometimes up to hundreds). Next to each name is a check box, default checked,
    so they can uncheck people that, for whatever reason, they don't want to mail.
    This search results page is also a form. - Once they are happy with who they
    are mailing, they submit the form which creates a new mailing list in the form
    of an excel spreadsheet and makes the database insert to record that the
    propspect was sent the mailing. In an ideal world (ie. using SQL Server) I
    would have used an SP to do the insert and inserted ListID and ProspectID as
    one instance per row. However, as far as I know, there is no way of doing this
    with Access except by using the Command object, but this only works if you know
    the total number of records to be inserted (and in practical terms is limited
    to a few dozen inserts). So the way I implemented it is to attach the
    ProspectID to the checkbox value and insert the value of all checked checkboxes
    into a memo field. Subsequent pages which retrieve the names of recipients of
    the mailing then have to p the memo text, extracting the appropriate IDs.
    Not ideal, but the best workaround I could come up with. This is easy enough
    when you just want to find out who received one mailing, but becomes less tidy
    when you want to know which mailing(s) one person received out of many. So I
    also can't use a separate field in the mailing list table for each recipient
    (recipient1, recipient2 etc.) because there could be up to hundreds of
    recipients. Hope this makes sense!

    tim-p Guest

  4. #4

    Default Re: Can you concatenate recordset values in one field?

    If all you need to track each time is which customers received a mailing
    then what I would do is have a master table holding all their information,
    and a second table that simply had three fields namely

    Mailing ID - autonumber
    CustomerID - link to the customers table
    MailDate - date of the mailing.

    Then if you wished to see which mailing a customer had it is a simple matter
    of querying this table.

    --
    Regards

    Paul Whitham
    Macromedia Certified Professional for Dreamweaver MX2004
    Valleybiz Internet Design
    [url]www.valleybiz.net[/url]

    Team Macromedia Volunteer for Ultradev/Dreamweaver MX
    [url]www.macromedia.com/support/forums/team_macromedia[/url]

    "tim-p" <webforumsusermacromedia.com> wrote in message
    news:d32rgl$41h$1forums.macromedia.com...
    > Hi Paul Thanks for the reply. I went round the houses in terms of how
    the
    > data should be stored on this one. The process that the user goes through
    to
    > select the list has governed how the data has to be stored: - The user is
    a
    > marketing agency who use this as their new business prospecting system.
    They
    > have a number of search criteria on a search form, which allows them to
    choose
    > people who work in certain industries, have a certain job responsibility
    and so
    > on. - The search results page may therefore contain none, one or more
    results
    > (sometimes up to hundreds). Next to each name is a check box, default
    checked,
    > so they can uncheck people that, for whatever reason, they don't want to
    mail.
    > This search results page is also a form. - Once they are happy with who
    they
    > are mailing, they submit the form which creates a new mailing list in the
    form
    > of an excel spreadsheet and makes the database insert to record that the
    > propspect was sent the mailing. In an ideal world (ie. using SQL Server)
    I
    > would have used an SP to do the insert and inserted ListID and ProspectID
    as
    > one instance per row. However, as far as I know, there is no way of doing
    this
    > with Access except by using the Command object, but this only works if you
    know
    > the total number of records to be inserted (and in practical terms is
    limited
    > to a few dozen inserts). So the way I implemented it is to attach the
    > ProspectID to the checkbox value and insert the value of all checked
    checkboxes
    > into a memo field. Subsequent pages which retrieve the names of recipients
    of
    > the mailing then have to p the memo text, extracting the appropriate
    IDs.
    > Not ideal, but the best workaround I could come up with. This is easy
    enough
    > when you just want to find out who received one mailing, but becomes less
    tidy
    > when you want to know which mailing(s) one person received out of many.
    So I
    > also can't use a separate field in the mailing list table for each
    recipient
    > (recipient1, recipient2 etc.) because there could be up to hundreds of
    > recipients. Hope this makes sense!
    >

    Paul Whitham TMM Guest

  5. #5

    Default Re: Can you concatenate recordset values in one field?

    Hi Paul Thanks for the reply. Yes, what you are saying is similar to what I
    described abouve as my ideal structure. Ideally I would have a table with the
    mail shot details and a table holding all mail recipients: tblMailing:
    MailingID (autonumber), DateCreated, MailingName + some other details
    tblMaillist: MailingID, RecipientID tblMailList would then contain all
    recipients of all mailshots in one table, dead easy to search. Easily
    achievable in SQL Server because your insert into tblMailList can be done by
    stored procedure. But as far as I know there is no way of doing this insert
    in Access. The number of rows in this table to be inserted is variable,
    generated from a repeat region, could be anything from 1 to thousands. If you
    know the number of rows you can do it with the MM Command object, but even then
    only for a small number of records. Do you know of any way to do this
    multi-line insert with Access? Regards TP

    tim-p Guest

  6. #6

    Default Re: Can you concatenate recordset values in one field?

    Hi

    Having finally sorted this out I thought I'd better post back the answer,
    worked out after looking at many other posts in other forums.

    I did succeed in making my asp page insert multiple records inAccess, but
    using the command object and making the insert behaviour into a loop.

    What I did was this:

    My search results are in a repeat region, which itself is inside a form. There
    is a hidden field which has the value
    <%=rsRecordsetName.Fields.Item("fieldname").value% > -i.e. because it is in the
    repeat region each record in that region returns its own value to the form
    element. The form does not insert into the database itself, but instead is set
    to have the page which does the insert as its target. If you were to do an
    insert from this first page it would insert a comma delimeted string into the
    target db field corresponding to that form element.

    It is page two which does the insert. I set up a command object to do the
    insert using the standard DW object, mapping each form field onto its
    corresponding db field. This produces code like this:

    <%

    if(Request.Form("RoleID") <> "") then Command1__varRole =
    Request.Form("RoleID")

    if(Request.Form("CampID") <> "") then Command1__varCamp =
    Request.Form("CampID")

    if(Request.Form("DraftList") <> "") then Command1__varDraftList =
    Request.Form("DraftList")

    %>
    <%

    set Command1 = Server.CreateObject("ADODB.Command")
    Command1.ActiveConnection = MM_connMomentaDB_STRING
    Command1.CommandText = "INSERT INTO tblxCamp_X_Role_X_DraftList (RoleID,
    CampID, DraftList) VALUES (" + Replace(Command1__varRole, "'", "''") + ", " +
    Replace(Command1__varCamp, "'", "''") + ", " + Replace(Command1__varDraftList,
    "'", "''") + " ) "
    Command1.CommandType = 1
    Command1.CommandTimeout = 0
    Command1.Prepared = true
    Command1.Execute()

    %>

    Then you modify the standard behaviour to loop through the multi-instance
    field until it has finished.

    My code:

    <%

    if(Request.Form("RoleID") <> "") then Command1__varRole =
    Request.Form("RoleID")

    if(Request.Form("CampID") <> "") then Command1__varCamp =
    Request.Form("CampID")

    if(Request.Form("DraftList") <> "") then Command1__varDraftList =
    Request.Form("DraftList")

    %>
    <%
    Dim allRoles
    allRoles = Split(Command1__varRole,", ")
    'This takes the comma-separated list and makes an array
    For Each X In allRoles
    set Command1 = Server.CreateObject("ADODB.Command")
    Command1.ActiveConnection = MM_connMomentaDB_STRING
    Command1.CommandText = "INSERT INTO tblxCamp_X_Role_X_DraftList (RoleID,
    CampID, DraftList) VALUES (" + Replace(X, "'", "''") + ", " +
    Replace(Command1__varCamp, "'", "''") + ", " + Replace(Command1__varDraftList,
    "'", "''") + " ) "
    Command1.CommandType = 1
    Command1.CommandTimeout = 0
    Command1.Prepared = false
    Command1.Execute()
    set Command1 = nothing
    Next
    %>

    This splits the field that contains the multiple entries separated by commas
    and inserts the spilt string items until it has looped through them all. It
    also inserts two other fields, which as the same for all records.

    Note that you can't use this method to split two different strings.

    Hope this helps someone in the future.

    TP

    tim-p Guest

Similar Threads

  1. Concatenate fields with no values
    By Pubcit in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 15th, 03:41 PM
  2. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM
  3. Recordset is complete, but has no values
    By Jordan in forum ASP Database
    Replies: 17
    Last Post: January 16th, 10:46 PM
  4. How to concatenate rows of data into one field?
    By Larry Trutter in forum IBM DB2
    Replies: 1
    Last Post: September 4th, 09:23 PM
  5. concatenate in Javascript? simple field validation funciton notworking
    By mick_white in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 22nd, 08:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139