Using Vbscript "SPLIT" function on Multi-select field

[pmarisole]

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.f orm("Proj"), ", ")
mdgarray1 = split(request.f orm("mgrgroup") , ",")
redim mdgarray2(uboun d(mdgarray1))
for i = 0 to ubound(mdgarray 1)
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(m ySQL)
NEXT
%>

[Bob Barrows [MVP]]

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.

[pmarisole]

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.for m("Proj"))> 0 then
for each strID in request.form("P roj")
mgrgrp=""
mdgrp=""

if len(request.for m("mgrgroup_" & strID)) > 0 then
Redim arMgrs(request. form("mgrgroup_ " & strID).Count -1)
i = 0
for each strMgr in request.form("m grgroup_" & strID)
arMgrs(i)=RTrim (split(strMgr,"-")(0))
i = i + 1
next
mgrgrp=Join(arM grs,", ")
Redim arMds(request.f orm("mgrgroup_" & strID).Count -1)
i = 0
for each strMd in request.form("m grgroup_" & strID)
arMds(i)= right(split(str Md,",")(0), 5)
i = i + 1
next
mdgrp=Join(arMd s,",")
end if

mySQL = "UPDATE EBSProjects SET mgrgroup= '" & trim(mgrgrp) & "',
mdgroup= '" & trim(mdgrp) & "' where (ebsrefid ='" & strID & "')"
Response.Write mySQL & "<br>"
dbProject.Execu te(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.