Using Vbscript function on Multi-select field

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

  1. pmarisole

    pmarisole Guest

    I am trying to use the vbscript "split" function on a multi-select
    field. I am trying
    to do a mass update of several records at a time.
    I am getting an error and I'm not sure what to do. Here is the code if

    someone could help...
    strID = split(request.form("proj"), ", ")
    projstat = split(request.form("rojstat"),",")
    impr = split(request.form("impr"),",")
    idate = split(request.form("rojinitdate"),",")
    pdate = split(request.form("plancompletedate"),",")
    adate = split(request.form("actualcompletedate"),",")

    mdg = request.form("mgr")
    myArray3 = Split(mdg,"- ")
    p = ubound(myArray3)


    if mdg = "Not Assigned" Then
    mdgg = "Not Assigned"
    else
    if p = 1 then
    mdgg = myArray3(0)
    else
    if p = 2 then
    mdgg = split((myArray3(0) & "*" &
    mid(myArray3(1),6)),"*")
    else
    if p = 3 then
    mdgg = split((myArray3(0) & "*" &
    mid(myArray3(1),6) & "*" &
    mid(myArray3(2),6)),",")
    else
    if p = 4 then
    mdgg = split((myArray3(0) & "*" &
    mid(myArray3(1),6) & "*" &
    mid(myArray3(2),6) & "*" & mid(myArray3(3),6)),",")
    else
    if p = 5 then
    mdgg = split((myArray3(0) & "*" &
    mid(myArray3(1),6) & "*" &
    mid(myArray3(2),6) & "*" & mid(myArray3(4),6)),",")
    end if
    end if
    end if
    end if
    end if
    end if


    FOR i = LBound(strID) TO UBound(strID)
    mySQL = "UPDATE Roj SET rojstatus= '" & trim(rojstat(i)) & "',
    importrank= '" & trim(impr(i)) & "', mgr= '" & trim(mdgg(i)) & "',
    rojinitdate= '" & trim(idate(i)) & "', plancompletedate= '" &
    trim(pdate(i)) & "', actualcompletedate= '" & trim(adate(i)) & "' where

    (refid ='" & strID(i) & "')"
    dbRroject.Execute(mySQL)
    NEXT


    This is the error I'm getting
    Response object error 'ASP 0106 : 80020005'
    Type Mismatch
    /project/DMMassUpdated.asp, line 0
    An unhandled data type was encountered.


    Can anyone HELP?
     
    pmarisole, Feb 2, 2006
    #1
    1. Advertising

  2. pmarisole wrote:
    > This is the error I'm getting
    > Response object error 'ASP 0106 : 80020005'
    > Type Mismatch
    > /project/DMMassUpdated.asp, line 0
    > An unhandled data type was encountered.
    >
    >

    Start here.
    http://www.aspfaq.com/search.asp?q=80020005&x=10&y=10

    If these articles don't provide your answer, let us know and we'll start
    digging into your code.


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

  3. pmarisole

    Paxton Guest

    pmarisole wrote:
    > I am trying to use the vbscript "split" function on a multi-select
    > field. I am trying
    > to do a mass update of several records at a time.
    > I am getting an error and I'm not sure what to do. Here is the code if
    >
    > someone could help...
    > strID = split(request.form("proj"), ", ")
    > projstat = split(request.form("rojstat"),",")
    > impr = split(request.form("impr"),",")
    > idate = split(request.form("rojinitdate"),",")
    > pdate = split(request.form("plancompletedate"),",")
    > adate = split(request.form("actualcompletedate"),",")
    >
    > mdg = request.form("mgr")
    > myArray3 = Split(mdg,"- ")
    > p = ubound(myArray3)
    >
    >
    > if mdg = "Not Assigned" Then
    > mdgg = "Not Assigned"
    > else
    > if p = 1 then
    > mdgg = myArray3(0)
    > else
    > if p = 2 then
    > mdgg = split((myArray3(0) & "*" &
    > mid(myArray3(1),6)),"*")
    > else
    > if p = 3 then
    > mdgg = split((myArray3(0) & "*" &
    > mid(myArray3(1),6) & "*" &
    > mid(myArray3(2),6)),",")
    > else
    > if p = 4 then
    > mdgg = split((myArray3(0) & "*" &
    > mid(myArray3(1),6) & "*" &
    > mid(myArray3(2),6) & "*" & mid(myArray3(3),6)),",")
    > else
    > if p = 5 then
    > mdgg = split((myArray3(0) & "*" &
    > mid(myArray3(1),6) & "*" &
    > mid(myArray3(2),6) & "*" & mid(myArray3(4),6)),",")
    > end if
    > end if
    > end if
    > end if
    > end if
    > end if
    >
    >
    > FOR i = LBound(strID) TO UBound(strID)
    > mySQL = "UPDATE Roj SET rojstatus= '" & trim(rojstat(i)) & "',
    > importrank= '" & trim(impr(i)) & "', mgr= '" & trim(mdgg(i)) & "',
    > rojinitdate= '" & trim(idate(i)) & "', plancompletedate= '" &
    > trim(pdate(i)) & "', actualcompletedate= '" & trim(adate(i)) & "' where
    >
    > (refid ='" & strID(i) & "')"
    > dbRroject.Execute(mySQL)
    > NEXT
    >
    >
    > This is the error I'm getting
    > Response object error 'ASP 0106 : 80020005'
    > Type Mismatch
    > /project/DMMassUpdated.asp, line 0
    > An unhandled data type was encountered.
    >


    http://www.aspfaq.com/show.asp?id=2099

    /P.
    >
    > Can anyone HELP?
     
    Paxton, Feb 2, 2006
    #3
  4. pmarisole

    pmarisole Guest

    Thanks for the input. I fixed that problem but
    now I am getting the values for both list boxes in one (updating the
    first record in recordset with all values from every record)

    The field selection values are: "Jose Blewer - JLG" and I am using the
    SPLIT function to select only 'Jose Blewer' .
    It is updating the first field with all the values selected from every
    succeeding record. It almost as if it is not looping through the
    records.
     
    pmarisole, Feb 3, 2006
    #4
  5. pmarisole wrote:
    > Thanks for the input. I fixed that problem but
    > now I am getting the values for both list boxes in one (updating the
    > first record in recordset with all values from every record)
    >
    > The field selection values are: "Jose Blewer - JLG" and I am using
    > the SPLIT function to select only 'Jose Blewer' .
    > It is updating the first field with all the values selected from every
    > succeeding record. It almost as if it is not looping through the
    > records.


    Hard to say what is going on without seeing your revised code.
    Please try to extract only the relevant bits from your page so we can
    concentrate on your problem. Creating a small "repro" page that we can run
    to reproduce your problem would be really helpful.

    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 3, 2006
    #5
  6. pmarisole

    pmarisole Guest

    I am trying to update the database from a listing of all
    records belonging to a manager (Looping through Recordset)
    One of the fields in the recordset is a Multi-Select field which
    I have to split (I am trying to use the comma to separate the values
    that go
    into the field.) So the database field value could be: Joe Johnson,
    Bill Frisco, Gill Bryant etc...
    The field (mgrgroup) is the one I'm trying to split.


    ******** Here's the code for the Entry Screen ***********
    (This only shows 1 record but in reality there will be multiple
    records in the recordset and I
    Need to be able to update the database from all the records in this
    entry screen.)
    <html>
    <head>
    <title>EEE Mass Update</title>
    </head>
    <body link="#800000" vlink="#800000" alink="#00FF00">
    <form name="FrontPage_Form1" method="POST" action="DMMassUpdated.asp"
    <table align="center" border="0" width="1207">
    <tr>
    <td align="center" width="1074"><font color="#000000" size="5"><b>
    Mass Update for: </b></font></td>
    </tr>
    </table>

    <table align="center" border="0" width="100%">
    <tr>
    <th bgcolor="#bbbbFF"><font face="Arial" size="2">EEE Roj
    Status</th>
    <th bgcolor="#bbbbFF"><font face="Arial" size="2">Divisional
    Ranking</th>
    <th bgcolor="#bbbbFF"><font face="Arial" size="2">Support
    Manager</th>
    </tr>
    <tr>
    <td align="left" ><font face="Arial" size="2"><b></b></td>
    <td align="left" colspan="20"><font face="Arial"
    size="2"><b></b></td>
    </tr>
    <tr>
    <td bgcolor="#F8F8FF" align="center" height="22"><font face="Arial"
    size="1">
    <select size="1" name="rojstat">
    <option>-- Select One --</option>
    <option value="Cancelled">Cancelled</option>
    <option value="Concept Sizing">Concept Sizing</option>
    <option value="Deferred">Deferred</option>
    <option value="Development">Development</option>
    <option value="Implementation">Implementation</option>
    <option value="Pending">Pending</option>
    <option value="Prioritization">Prioritization</option>
    <option value="Review/Approval">Review/Approval</option>
    <option value="Testing">Testing</option>
    </select></font></td>
    <td align="center" bgcolor="#F8F8FF" ><font face="Arial"
    size="1"><input type="text" name="impr" size="5"></td>
    <td bgcolor="#F8F8FF" align="center" height="22"><font face="Arial"
    size="1">
    <select size="3" name="mgrgroup" multiple>
    <option value="Not Assigned - 0">Not Assigned - 0</option>
    <option value="Joe Johnson - RTB7"> Joe Johnson - RTB7</option>
    <option value="Bill Frisco - TPP7"> Bill Frisco - TPP7</option>
    <option value="Gill Bryant - TPP5"> Gill Bryant - TPP5</option>
    <option value="Marty Wilkins - HEG5"> Marty Wilkins -
    HEG5</option>
    <option value="Ashley Barlow - RTB1">Ashley Barlow -
    RTB1</option>
    <option value="Donna Smith - TJT1">Donna Smith - TJT1</option>
    <option value="Greg Trimble - TPP6">Greg Trimble - TPP6</option>
    <option value="Jeff Wilson - HEG1">Jeff Wilson - HEG1</option>
    <option value="Jane Ford - RTB6">Jane Ford - RTB6</option>
    </select></font>
    </td>
    <tr>
    <td bordercolor="#FFFFFF"><input type="hidden" name="Proj"></td>
    I will pass the eeerefid to the update screen in this hidden field
    </tr>
    <br>
    <tr>
    <td align="center" colspan="16"><input type="submit"
    name="cmdSubmitCustom" value="Submit">&nbsp;</td>
    </tr>
    </table>
    </form>
    </body>
    </html>



    ******** Here's the code on the Database UPDATE Screen
    ***********
    <%Option Explicit%>
    <!--#INCLUDE FILE="adovbs.inc"-->
    <%
    Dim dbRoj
    Dim mySQL, strID, i,
    Dim rojstat,impr,mdg, myArray3, p, mgrgrp

    On Error Resume Next
    Set dbRoj = Server.CreateObject("ADODB.Connection")
    %>
    <!--#INCLUDE FILE="../../includes/EEE/eroj1.asp"-->
    <%
    strID = split(request.form("Proj"), ", ")
    rojstat = split(request.form("rojstat"),",")
    impr = split(request.form("impr"),",")
    mdg = request.form("mgrgroup")
    myArray3 = Split(mdg,"- ")
    p = ubound(myArray3)


    if p = 1 then
    mgrgrp = split(myArray3(0),",")
    else
    if p = 2 then
    mgrgrp = split((myArray3(0) & "," & mid(myArray3(1),6)),",")
    else
    if p = 3 then
    mgrgrp= split((myArray3(0) & "," & mid(myArray3(1),6) & "," &
    mid(myArray3(2),6)),",")
    else
    if p = 4 then
    mgrgrp = split((myArray3(0) & "," & mid(myArray3(1),6) & "," &
    mid(myArray3(2),6) & "," & mid(myArray3(3),6)),",")
    else
    if p = 5 then
    mgrgrp = split((myArray3(0) & "," & mid(myArray3(1),6) & "," &
    mid(myArray3(2),6) & "," & mid(myArray3(4),6)),",")
    end if
    end if
    end if
    end if
    end if


    FOR i = LBound(strID) TO UBound(strID)
    mySQL = "UPDATE EEERoj SET eeerojstatus= '" & trim(rojstat(i)) & "',
    importancerank= '" & trim(impr(i)) & "', mgrgroup= '" & trim(mgrgrp(i))
    & "' where (eeerefid ='" & strID(i) & "')"
    dbRoj.Execute(mySQL)
    NEXT
    %>

    I think the SPLIT function on the arrays are wrong and I don't know
    why.
    Thanks so much for your help
     
    pmarisole, Feb 3, 2006
    #6
  7. pmarisole wrote:
    > I am trying to update the database from a listing of all
    > records belonging to a manager (Looping through Recordset)
    > One of the fields in the recordset is a Multi-Select field which
    > I have to split (I am trying to use the comma to separate the values
    > that go
    > into the field.) So the database field value could be: Joe Johnson,
    > Bill Frisco, Gill Bryant etc...
    > The field (mgrgroup) is the one I'm trying to split.
    >

    Then why are you splitting the other ones?

    Start by doing some elementary debugging by putting this at the beginning of
    the procedure:

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

    When you do, you will see this result:

    request.form("Proj") contains:
    request.form("rojstat") contains: Concept Sizing
    request.form("impr") contains: 10
    request.form("mgrgroup") contains: Joe Johnson - RTB7, Bill Frisco - TPP7,
    Marty Wilkins - HEG5


    Which makes THIS split statement
    > mdg = request.form("mgrgroup")


    wrong. The list of multi-selected items is delimited by "," not ", ". It
    needs to look like this:
    strID = split(request.form("mgrgroup"), ",")

    I still don't see why you are splitting the remainder of these...

    > strID = split(request.form("Proj"), ", ")
    > rojstat = split(request.form("rojstat"),",")
    > impr = split(request.form("impr"),",")


    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 3, 2006
    #7
  8. Bob Barrows [MVP] wrote:
    > Which makes THIS split statement
    >> mdg = request.form("mgrgroup")

    >


    Oh wait. That's not a split statement ... why did you not split on it? Hmm,
    I think you want to turn this:

    Joe Johnson - RTB7, Bill Frisco - TPP7,Marty Wilkins - HEG5

    into this

    Joe Johnson, Bill Frisco, Marty Wilkins

    Correct?

    Start by splitting on the commas:

    dim mdgarray1, mdgarray2()
    mdgarray1=split(request.form("mgrgroup"), ",")
    redim mdgarray2(ubound(mdgarray1))
    for i = 0 to ubound(mdgarray1)
    'then put the names into an array
    mdgarray2(i) = Split(mdg," - ")(0)
    next
    'Then join the array into a string:
    mgrgrp = Join(mdgarray2,", ")

    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 3, 2006
    #8
  9. Bob Barrows [MVP] wrote:
    > mdgarray2(i) = Split(mdg," - ")(0)

    Ah geez - copy/paste madness!

    mdgarray2(i) = Split(mdgarray1(i)," - ")(0)

    --
    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 3, 2006
    #9
  10. pmarisole

    pmarisole Guest

    I put your code in like as follows: but it will not upate the record
    at all, it selects the
    first option in the list box (Not Assigned) for every record.
    <%Option Explicit%>
    <!--#INCLUDE FILE="adovbs.inc"-->
    <%
    Dim dbProject,mdgarray1, mdgarray2()
    Dim mySQL
    Dim strID, i, j,
    Dim projstat,impr,mdg, mgrgrp()

    On Error Resume Next
    Set dbRoj = Server.CreateObject("ADODB.Connection")
    %>
    <!--#INCLUDE FILE="../../includes/eproj1.asp"-->
    <%
    strID = split(request.form("Proj"), ", ")
    rojstat = split(request.form("rojstat"),",")
    impr = split(request.form("impr"),",")
    mdgarray1 =split(request.form("mgrgroup"), ",")
    redim mdgarray2(ubound(mdgarray1))
    for j = 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 ebsprojectstatus= '" &
    trim(projstat(i)) & "', importancerank= '" & trim(impr(i)) & "',
    mgrgroup= '" & trim(mgrgrp(i)) & "' where (ebsrefid ='" & strID(i) &
    "')"
    dbProject.Execute(mySQL)
    NEXT
    %>
     
    pmarisole, Feb 3, 2006
    #10
  11. pmarisole wrote:
    > I put your code in like as follows: but it will not upate the record
    > at all, it selects the
    > first option in the list box (Not Assigned) for every record.
    > <%Option Explicit%>
    > <!--#INCLUDE FILE="adovbs.inc"-->
    > <%
    > Dim dbProject,mdgarray1, mdgarray2()
    > Dim mySQL
    > Dim strID, i, j,
    > Dim projstat,impr,mdg, mgrgrp()
    >
    > On Error Resume Next
    > Set dbRoj = Server.CreateObject("ADODB.Connection")
    > %>
    > <!--#INCLUDE FILE="../../includes/eproj1.asp"-->
    > <%
    > strID = split(request.form("Proj"), ", ")
    > rojstat = split(request.form("rojstat"),",")
    > impr = split(request.form("impr"),",")


    I still don't understand why you are splitting these three field values ...

    > mdgarray1 =split(request.form("mgrgroup"), ",")
    > redim mdgarray2(ubound(mdgarray1))
    > for j = 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 ebsprojectstatus= '" &
    > trim(projstat(i)) & "', importancerank= '" & trim(impr(i)) & "',
    > mgrgroup= '" &



    trim(mgrgrp(i))
    mgrgrp is now a string, not an array. Change this to

    mgrgrp



    --
    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 3, 2006
    #11
  12. Bob Barrows [MVP] wrote:
    > pmarisole wrote:
    >> I put your code in like as follows: but it will not upate the record
    >> at all, it selects the
    >> first option in the list box (Not Assigned) for every record.
    >> <%Option Explicit%>
    >> <!--#INCLUDE FILE="adovbs.inc"-->
    >> <%
    >> Dim dbProject,mdgarray1, mdgarray2()
    >> Dim mySQL
    >> Dim strID, i, j,
    >> Dim projstat,impr,mdg, mgrgrp()
    >>
    >> On Error Resume Next
    >> Set dbRoj = Server.CreateObject("ADODB.Connection")
    >> %>
    >> <!--#INCLUDE FILE="../../includes/eproj1.asp"-->
    >> <%
    >> strID = split(request.form("Proj"), ", ")
    >> rojstat = split(request.form("rojstat"),",")
    >> impr = split(request.form("impr"),",")

    >
    > I still don't understand why you are splitting these three field
    > values ...
    >

    Wait. I just remembered you said you were dealing with multiple records, as
    well as the multiple select listbox.

    You should look at this thread:

    http://groups.google.com/group/micr...99cd2cffca0/781615e016301bf5#781615e016301bf5

    The idea when dealing with multiple records in an entry form is to include
    the record identifier as part of the input element names
    --
    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 3, 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. pmarisole
    Replies:
    0
    Views:
    123
    pmarisole
    Feb 14, 2006
  2. pmarisole
    Replies:
    11
    Views:
    271
    pmarisole
    Feb 15, 2006
  3. pmarisole
    Replies:
    0
    Views:
    242
    pmarisole
    Feb 16, 2006
  4. Sound
    Replies:
    2
    Views:
    503
    Randy Webb
    Sep 28, 2006
  5. jr
    Replies:
    3
    Views:
    502
Loading...

Share This Page