By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,101 Members | 1,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,101 IT Pros & Developers. It's quick & easy.

Using Vbscript "SPLIT" function on Multi-select field

P: n/a
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
%>

Feb 14 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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.
Feb 14 '06 #2

P: n/a
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

Feb 14 '06 #3

P: n/a
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.
Feb 14 '06 #4

P: n/a
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

Feb 14 '06 #5

P: n/a
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.
Feb 14 '06 #6

P: n/a
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

Feb 14 '06 #7

P: n/a
I am new at this and I don't know what other code to use except this.

Feb 14 '06 #8

P: n/a
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.
Feb 14 '06 #9

P: n/a
I did as you suggested and it works just great.
I really appreciate you help with this.

Feb 14 '06 #10

P: n/a
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.
Feb 14 '06 #11

P: n/a
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.

Feb 15 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.