Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Using Vbscript function on Multi-select field

Reply
Thread Tools

Using Vbscript function on Multi-select field

 
 
pmarisole
Guest
Posts: n/a
 
      02-02-2006
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?

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-02-2006
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.


 
Reply With Quote
 
 
 
 
Paxton
Guest
Posts: n/a
 
      02-02-2006

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?


 
Reply With Quote
 
pmarisole
Guest
Posts: n/a
 
      02-03-2006
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.

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-03-2006
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.


 
Reply With Quote
 
pmarisole
Guest
Posts: n/a
 
      02-03-2006
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

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-03-2006
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-03-2006
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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-03-2006
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.


 
Reply With Quote
 
pmarisole
Guest
Posts: n/a
 
      02-03-2006
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
%>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
javascript validation for a not required field, field is onlyrequired if another field has a value jr Javascript 3 07-08-2010 10:33 AM
1.Enter space bar for field names and save the field.The field shoud not get saved and an alert should be there as"Space bars are not allowed" Sound Javascript 2 09-28-2006 02:43 PM
Using Vbscript "SPLIT" function on Multi-select field pmarisole ASP General 0 02-16-2006 04:55 PM
Using Vbscript "SPLIT" function on Multi-select field pmarisole ASP General 11 02-15-2006 09:16 PM
Using Vbscript "SPLIT" function on Multi-select field pmarisole ASP General 0 02-14-2006 04:11 PM



Advertisments