Using Vbscript "SPLIT" function on Multi-select field

Discussion in 'ASP General' started by pmarisole, Feb 14, 2006.

  1. pmarisole

    pmarisole Guest

    I am using the following code to split/join values in a multi-select
    field. It is combining all the values in All the records into one long
    string in each record in recordset.

    Example: I have a recordset with 2 records.
    The 1st contains the split/joined values: Alan Smir, Jeff Karl
    The 2nd contains the value: Keith Robb

    When it updates database, it will put Alan Smir, Jeff Karl, Keith Robb
    into each record in the recordset
    Instead it should be putting Alan Smir, Jeff Karl into the 1st record
    and Keith Robb into the 2nd record in the recordset.
    Does anyone see what is wrong with the code?

    <% strID = split(request.form("Proj"), ", ")
    mdgarray1 = split(request.form("mgrgroup"), ",")
    redim mdgarray2(ubound(mdgarray1))
    for i = 0 to ubound(mdgarray1)
    mdgarray2(i) = Split(mdgarray1(i)," - ")(0)
    next
    mgrgrp = Join(mdgarray2,", ")

    FOR i = LBound(strID) TO UBound(strID)
    mySQL = "UPDATE ERoj SET mgrgroup= '" & trim(mgrgrp) & "' where (id ='"
    & strID(i) & "')"
    dbRoj.Execute(mySQL)
    NEXT
    %>
     
    pmarisole, Feb 14, 2006
    #1
    1. Advertising

  2. pmarisole wrote:
    > I am using the following code to split/join values in a multi-select
    > field. It is combining all the values in All the records into one long
    > string in each record in recordset.
    >
    > Example: I have a recordset with 2 records.
    > The 1st contains the split/joined values: Alan Smir, Jeff Karl
    > The 2nd contains the value: Keith Robb
    >
    > When it updates database, it will put Alan Smir, Jeff Karl, Keith Robb
    > into each record in the recordset
    > Instead it should be putting Alan Smir, Jeff Karl into the 1st record
    > and Keith Robb into the 2nd record in the recordset.
    > Does anyone see what is wrong with the code?


    Not without seeing the result of :
    Response.Write request.form("Proj") & "<BR>"
    Response.Write request.form("mgrgroup") & "<BR>"

    Also, show us the two SQL statements you wish this code to generate and
    execute.



    --
    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], Feb 14, 2006
    #2
    1. Advertising

  3. pmarisole

    pmarisole Guest

    1st Record Value
    Alan Smir, Jeff Karl
    2nd Record Value
    Keith Robb

    response.write mgrgrp
    response.write "<br>"
    response.write strID(i)
    response.end

    This is the output from response.write for the two records
    Alan Smir, Jeff Karl, Keith Robb (it's combining all the values in
    both records into ONE VALUE)
    60691908 (this is the 1st strID in the loop)

    This is the SQL statement that I am using to update the database
    FOR i = LBound(strID) TO UBound(strID)
    mySQL = "UPDATE ERoj SET mgrgrp= '" & trim(mgrgrp) & "' where (id ='"
    & strID(i) & "')"
    dbRoj.Execute(mySQL)
    NEXT
     
    pmarisole, Feb 14, 2006
    #3
  4. pmarisole wrote:
    > 1st Record Value
    > Alan Smir, Jeff Karl
    > 2nd Record Value
    > Keith Robb


    Yes, you said this in your first message. It is not a valid statement, but I
    let it pass (records contain fields - you have not shown me any field names)

    >
    > response.write mgrgrp
    > response.write "<br>"
    > response.write strID(i)
    > response.end


    Why are you showing me this? I did not ask to see it.
    Please read my reply again. I asked to see the result of:

    Response.Write request.form("Proj") & "<BR>"
    Response.Write request.form("mgrgroup") & "<BR>"

    In fact, run this code:

    Response.Write "request.form(""Proj"") contains:<BR>"
    Response.Write request.form("Proj") & "<BR>"
    Response.Write "request.form(""mgrgroup"") contains:<BR>"
    Response.Write request.form("mgrgroup") & "<BR>"

    and show me that result.

    >
    > This is the output from response.write for the two records
    > Alan Smir, Jeff Karl, Keith Robb (it's combining all the values
    > in both records into ONE VALUE)


    Well, that's one clue as to what is going on, isn't it?

    > 60691908 (this is the 1st strID in the loop)
    >
    > This is the SQL statement that I am using to update the database


    No, it is not. This is the series of vbscript statements that is supposed to
    generate the sql statements to be executed.

    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], Feb 14, 2006
    #4
  5. pmarisole

    pmarisole Guest

    THIS IS THE ENTIRE CODE WITH THE OUTPUT YOU REQUESTED
    <%Option Explicit%>
    <!--#INCLUDE FILE="adovbs.inc"-->
    <%
    Dim dbProject,mdgarray1, mdgarray2(), mddarray1, mddarray2()
    Dim mySQL, strID, i, j, idate,pdate
    Dim projstat,impr,mdg, mgrgrp

    'On Error Resume Next
    Set dbProject = Server.CreateObject("ADODB.Connection")
    %>
    <!--#INCLUDE FILE="../../includes/EBS/eproj1.asp"-->
    <% strID = split(request.form("Proj"), ", ")
    projstat = split(request.form("projstat"),",")

    mdgarray1 =split(request.form("mgrgroup"), ",")
    redim mdgarray2(ubound(mdgarray1))
    for i = 0 to ubound(mdgarray1)
    mdgarray2(i) = Split(mdgarray1(i)," - ")(0)
    next
    mgrgrp = Join(mdgarray2,", ")

    FOR i = LBound(strID) TO UBound(strID)
    mySQL = "UPDATE EBSProjects SET mgrgroup= '" & trim(mgrgrp) & "'
    where (ebsrefid ='" & strID(i) & "')"
    dbProject.Execute(mySQL)
    NEXT
    Response.Write "request.form(""Proj"") contains:<BR>"
    Response.Write request.form("Proj") & "<BR>"
    Response.Write "request.form(""mgrgroup"") contains:<BR>"
    Response.Write request.form("mgrgroup") & "<BR>"
    response.end
    %>

    HERE'S THE OUTPUT
    request.form("Proj") contains:
    60691908, 60692155
    request.form("mgrgroup") contains:
    Alan Smir - TJT1, Jeff Karl - HEG2, Keith Robb - TPP3
     
    pmarisole, Feb 14, 2006
    #5
  6. pmarisole wrote:
    > THIS IS THE ENTIRE CODE


    Oh come on. I did not ask for that. :)

    > WITH THE OUTPUT YOU REQUESTED

    <snip>
    > Response.Write "request.form(""Proj"") contains:<BR>"
    > Response.Write request.form("Proj") & "<BR>"
    > Response.Write "request.form(""mgrgroup"") contains:<BR>"
    > Response.Write request.form("mgrgroup") & "<BR>"
    > response.end
    > %>
    >
    > HERE'S THE OUTPUT
    > request.form("Proj") contains:
    > 60691908, 60692155
    > request.form("mgrgroup") contains:
    > Alan Smir - TJT1, Jeff Karl - HEG2, Keith Robb - TPP3


    Now, what to the two sql statements you wish to generate look like? I'm
    going somewhere with this. I just want to make sure you see where I'm going.
    Pretend you are opening your database in Access and creating queies in SQL
    View to accomplish your task. What will those sql statements look like?

    --
    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], Feb 14, 2006
    #6
  7. pmarisole

    pmarisole Guest

    This is the SQL
    FOR i = LBound(strID) TO UBound(strID)
    UPDATE EBSProjects SET mgrgroup= '" & trim(mgrgrp) & "' where (ebsrefid
    ='" & strID(i) & "')"
    NEXT

    In english terms
    FOR i = LBound(strID) TO UBound(strID)
    UPDATE EBSProjects SET mgrgroup = 'Alan Smir, Jeff Karl ' where
    ebsrefid = 60691908
    Next
    UPDATE EBSProjects SET mgrgroup = Keith Robb ' where ebsrefid =
    60692155
     
    pmarisole, Feb 14, 2006
    #7
  8. pmarisole

    pmarisole Guest

    I am new at this and I don't know what other code to use except this.
     
    pmarisole, Feb 14, 2006
    #8
  9. pmarisole wrote:
    > This is the SQL


    Again, it is not the SQL. It is a series of vbscript statements intended to
    generate the SQL

    > FOR i = LBound(strID) TO UBound(strID)
    > UPDATE EBSProjects SET mgrgroup= '" & trim(mgrgrp) & "' where
    > (ebsrefid ='" & strID(i) & "')"
    > NEXT
    >
    > In english terms


    OK, here are the two SQL statements you wish your vbscript to generate::

    > UPDATE EBSProjects SET mgrgroup = 'Alan Smir, Jeff Karl ' where
    > ebsrefid = 60691908


    > UPDATE EBSProjects SET mgrgroup = Keith Robb ' where ebsrefid =
    > 60692155


    Now, let's look at the results of the Response.Writes I asked for:

    request.form("Proj") contains:
    60691908, 60692155
    request.form("mgrgroup") contains:
    Alan Smir - TJT1, Jeff Karl - HEG2, Keith Robb - TPP3

    Now ... imagine that you don't know which piece of data belongs in which sql
    statement. How would you plan to separate out the data to put each piece in
    the correct sql statement?
    ....
    I'm waiting. Think about it ....

    All you have available is the information presented above. How do you take
    "Alan Smir - TJT1, Jeff Karl - HEG2, Keith Robb - TPP3" and break it up into
    'Alan Smir, Jeff Karl ' and 'Keith Robb ' without knowing ahead of time
    which names were supposed to go with which record?
    ....

    You can't come up with a way, can you?
    Well, neither can I. Do you see what the problem is?

    .....

    The problem is: you have not provided enough information to enable the code
    to make the proper decisions. There is nothing there to tell it that 'Alan
    Smir, Jeff Karl ' are supposed to go into the record for 60691908, and that
    'Keith Robb ' is supposed to go into the record for 60692155.

    What you need to do is go back to your data-entry form and incorporate the
    ebsrefid into the names of the mgrgroup input elements. So, instead of
    request.form("mgrgroup")
    you will have
    request.form("mgrgroup_60691908") and request.form("mgrgroup_60692155")

    Once you have accomplished this, you can change your processing code to (the
    following is tested and works - you can test it yourself.):

    <%
    dim arID, strID, arMgrs(), strMgr, mgrgrp, mySQL, i
    if len(request.form("Proj"))> 0 then
    'arID = Split(request.form("Proj"),",")
    'for each strID in arID
    for each strID in request.form("Proj")
    mgrgrp=""
    if len(request.form("mgrgroup_" & strID)) > 0 then
    'arMgrs=Split(request.form("mgrgroup_" & strID),",")
    Redim arMgrs(request.form("mgrgroup_" & strID).Count -1)
    i = 0
    for each strMgr in request.form("mgrgroup_" & strID)
    arMgrs(i)=RTrim(split(strMgr,"-")(0))
    i = i + 1
    next
    mgrgrp=Join(arMgrs,",")
    end if
    mySQL = "UPDATE ERoj SET mgrgroup= '" & trim(mgrgrp) & _
    "' where (id ='" & strID & "')"
    Response.Write mySQL & "<BR>"
    next
    end if
    %>
    <HTML>
    <BODY>

    <form method="post">
    <table>
    <tr><td><input type="text" name="Proj" value="60691908"></td>
    <td>
    <input type="text" name="mgrgroup_60691908"
    value="Alan Smir - TJT1"><BR>
    <input type="text" name="mgrgroup_60691908"
    value="Jeff Karl - HEG2">
    </td>
    </tr>
    <tr><td><input type="text" name="Proj" value="60692155"></td>
    <td>
    <input type="text" name="mgrgroup_60692155"
    value="Keith Robb - TPP3">
    </td>
    </tr>
    </table>
    <INPUT type="submit" value="Submit">
    </form>

    </BODY>
    </HTML>

    Of course, my preference would be to use parameters instead of dynamic sql,
    but I think I've given you enough to think about here.

    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], Feb 14, 2006
    #9
  10. pmarisole

    pmarisole Guest

    I did as you suggested and it works just great.
    I really appreciate you help with this.
     
    pmarisole, Feb 14, 2006
    #10
  11. pmarisole wrote:
    > I did as you suggested and it works just great.
    > I really appreciate you help with this.


    Cool.
    Come back someday and ask about using parameters. Dynamic sql is prone to
    security problems (sql injection).
    --
    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], Feb 14, 2006
    #11
  12. pmarisole

    pmarisole Guest

    Ok I am trying to split the mgrgroup field to extract the MD codes. I
    got it to extract the codes and put them in the MDGroup field, but only
    if all the records in the recordset are changed. If only one record is
    changed while looping then it puts bogus names in the MDGroup. I'm not
    sure how to make it overlook the MDGroup field if it is not changing.
    Here's the code and the output is at the bottom of this message

    if len(request.form("Proj"))> 0 then
    for each strID in request.form("Proj")
    mgrgrp=""
    mdgrp=""

    if len(request.form("mgrgroup_" & strID)) > 0 then
    Redim arMgrs(request.form("mgrgroup_" & strID).Count -1)
    i = 0
    for each strMgr in request.form("mgrgroup_" & strID)
    arMgrs(i)=RTrim(split(strMgr,"-")(0))
    i = i + 1
    next
    mgrgrp=Join(arMgrs,", ")


    Redim arMds(request.form("mgrgroup_" & strID).Count -1)
    i = 0
    for each strMd in request.form("mgrgroup_" & strID)
    arMds(i)= right(split(strMd,",")(0), 5)
    i = i + 1
    next
    mdgrp=Join(arMds,",")
    end if

    mySQL = "UPDATE EBSProjects SET mgrgroup= '" & trim(mgrgrp) & "',
    mdgroup= '" & trim(mdgrp) & "' where (ebsrefid ='" & strID & "')"
    Response.Write mySQL & "<br>"
    dbProject.Execute(mySQL)
    next
    response.end
    end if

    ************************************************ OUTPUT
    *********************************************
    THIS 1ST RECORD OF THE RECORDSET IS CHANGED
    The MGRGROUP field contains:
    Ashley Calvert - RTB1, Chris Gibney - TPP7

    The MdGROUP field contains:
    RTB1, TPP7

    The SQL StatementUPDATE EBSProjects SET mgrgroup= 'Ashley Calvert,
    Chris Gibney', mdgroup= 'RTB1, TPP7' where (ebsrefid ='60633400')



    THIS IS THE 2ND RECORD IN THE RECORDSET AND IT DOES NOT CHANGE BUT IT
    WRITES OVER THE RTB2 THAT IS IN THE FIELD FROM THE FIRST TIME IT WAS
    CHANGED
    The MGRGROUP field contains:
    Alan Sanders

    The MdGROUP field contains:
    nders

    The SQL StatementUPDATE EBSProjects SET mgrgroup= 'Alan Sanders',
    mdgroup= 'nders' where (ebsrefid ='60691908')



    It correctly adds the MdGroup code (RTB1, RBT2 etc) when the record is
    modified but in a mass update, that particular record does not need to
    be changed and it will overwrite the correct value in the MdGroup field
    with 5 characters of the mgr name.
     
    pmarisole, Feb 15, 2006
    #12
    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. Replies:
    9
    Views:
    374
    Paul McGuire
    Nov 16, 2006
  2. pmarisole
    Replies:
    11
    Views:
    272
    Bob Barrows [MVP]
    Feb 3, 2006
  3. pmarisole
    Replies:
    0
    Views:
    115
    pmarisole
    Feb 14, 2006
  4. pmarisole
    Replies:
    0
    Views:
    223
    pmarisole
    Feb 16, 2006
  5. Sound
    Replies:
    2
    Views:
    454
    Randy Webb
    Sep 28, 2006
Loading...

Share This Page