Using Vbscript function on Multi-select field

[pmarisole]

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?

[Bob Barrows [MVP]]

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.

[Paxton]

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?

[pmarisole]

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.

[Bob Barrows [MVP]]

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.

[pmarisole]

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

[Bob Barrows [MVP]]

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]]

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]]

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.

[pmarisole]

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

[Bob Barrows [MVP]]

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]]

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/micro...1615e016301bf5

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.