Using Vbscript function on Multi-select field

P

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?
 
P

Paxton

pmarisole said:
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?
Click to expand...
 
P

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

Bob Barrows [MVP]

pmarisole said:
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.
Click to expand...

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
 
P

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
 
B

Bob Barrows [MVP]

pmarisole said:
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.
Click to expand...
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")
Click to expand...

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"),",")
Click to expand...

Bob Barrows
 
B

Bob Barrows [MVP]

Bob said:
Which makes THIS split statement
Click to expand...

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
 
P

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

Bob Barrows [MVP]

pmarisole said:
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"),",")
Click to expand...

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= '" &
Click to expand...


trim(mgrgrp(i))
mgrgrp is now a string, not an array. Change this to

mgrgrp
 
B

Bob Barrows [MVP]

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Vbscript "SPLIT" function on Multi-select field 0
Code working properly in VS code for every test case but assigned wrong when submitted why? 0
Multi Update with If Else Statement ... error 3
Function is not worked in C 2
Using Vbscript "SPLIT" function on Multi-select field 11
Lexical Analysis on C++ 1
Survey details won't go through using php, ajax, Mysql 0
Problem with Rnd Function 3

Members online

Total: 28 (members: 2, guests: 26)
Robots: 669

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,057
Latest member
KetoBeezACVGummies

Latest Threads

Top