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.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
%>

 

[Bob Barrows [MVP]]

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.

 

[pmarisole]

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

 

[Bob Barrows [MVP]]

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

 

[pmarisole]

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

 

[Bob Barrows [MVP]]

THIS IS THE ENTIRE CODE

Oh come on. I did not ask for that.

WITH THE OUTPUT YOU REQUESTED

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?

 

[pmarisole]

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]

I am new at this and I don't know what other code to use except this.

 

[Bob Barrows [MVP]]

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

 

[pmarisole]

I did as you suggested and it works just great.
I really appreciate you help with this.

 

[Bob Barrows [MVP]]

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).

 

[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.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.