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.
