473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Help with Calculation of Multiple Records

This is the page where I collect the data in drop-down boxes with
values of 1-10 and send it to a submitted page to do calculations.
Example:
Employee1 TeamScore(1-10)
Employee2 TeamScore(1-10)
Employee3 TeamScore(1-10)
Employee4 TeamScore(1-10)
Then I submit this page with all the values in TeamScore for every
employee and I want to
perform a calculation based on the values in the drop-down and a
weighted score from another
database table. An example of a weighted score is 0.11 and I need to
multiply the value(from 1 to 10) times the weighted score of 0.11 for
each employee.

I have several records to update all at once from the previous screen
of drop-down boxes containing numbers 1 - 10 and I want to take each
individual drop-down value and multiply it by a weighted score (i.e.
0.11 etc.)
I am getting the following error message when I run this code below:
Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: ""]'

<%
MLevel2 = "0"
'On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
SS5 = "0.0"
S11 = round(rsScors("WS1"),2)
rsScors.close
set rsScors = Nothing
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1")* S11,",")
FOR i = LBound(strID) TO UBound(strID)
sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "' where (empid ='"
& strID(i) & "')"
C9.Execute(sSQL)
NEXT
C9.Close
Set C9 = Nothing
%>
I can't figure out how to make this calculation work. Any help would
be Greatly appreciated.

Dec 7 '06 #1
10 2492

<60***@fedex.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
This is the page where I collect the data in drop-down boxes with
values of 1-10 and send it to a submitted page to do calculations.
Example:
Employee1 TeamScore(1-10)
Employee2 TeamScore(1-10)
Employee3 TeamScore(1-10)
Employee4 TeamScore(1-10)
Then I submit this page with all the values in TeamScore for every
employee and I want to
perform a calculation based on the values in the drop-down and a
weighted score from another
database table. An example of a weighted score is 0.11 and I need to
multiply the value(from 1 to 10) times the weighted score of 0.11 for
each employee.

I have several records to update all at once from the previous screen
of drop-down boxes containing numbers 1 - 10 and I want to take each
individual drop-down value and multiply it by a weighted score (i.e.
0.11 etc.)
I am getting the following error message when I run this code below:
Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: ""]'

<%
MLevel2 = "0"
'On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
SS5 = "0.0"
S11 = round(rsScors("WS1"),2)
rsScors.close
set rsScors = Nothing
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1")* S11,",")
FOR i = LBound(strID) TO UBound(strID)
sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "' where (empid ='"
& strID(i) & "')"
C9.Execute(sSQL)
NEXT
C9.Close
Set C9 = Nothing
%>
I can't figure out how to make this calculation work. Any help would
be Greatly appreciated.
You didn't say where the error is ocurring, but you are probably trying to
perform a numeric operation on a variable that is a string at the moment.
Try using a suitable conversion function - Cint, Cdbl, Clng.

Hold on - What is QQ1 = split(request.Form("Q1")*S11,",") all about? If
Request.Form("Q1") is a comma-delimited string, you can't multiply it by
anything. You need to perform the multiplication on each item in the array
separately after using Split. You may also need to convert each item to a
numeric to perform the multiplication as suggested above. Something like:

<%
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
FOR i = LBound(strID) TO UBound(strID)
MyNum = Clng(QQ1(i))*S11 'might be cint or cdbl depending on QQ1(i)
sSQL = "UPDATE EDNCurr SET Q1= '" & MyNum & "' where (empid ='"
& strID(i) & "')"
C9.Execute(sSQL)
NEXT
%>

--
Mike Brind
Dec 7 '06 #2
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

Dec 7 '06 #3
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

Dec 7 '06 #4
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

Dec 7 '06 #5
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

Dec 7 '06 #6

"pmarisole" <jb******@midsouth.rr.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then 'changed variable here
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN 'changed variable here
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if
.....
I've commented the 2 changes that I can see needed doing. They were slight
modification to variable names, which now means you are working with the
correct one.

I tend to use variable names that are more different from eachother than
yours when I'm performing operations on them and assigning the result to a
new value. Usually the kind of names I use describe the stage in the
operation that I'm at eg CurrentAnswerToQ1 instead of Q11. That way I find
it easier to see what I'm doing. Also, more descriptive names can be
self-documenting, which makes maintenance etc easier. It may take a bit
longer to type, but it can save hours in debugging.

When you revisit this code in 6 months or more, you might have a hard time
working out what is going on in the code. Also, just a glance at this type
of variable naming policy is enough to significantly reduce the number of
people prepared to plough through it to help you :-)

One other thing - I would remove On Error Remove Next from your code until
it's finished. It hides errors that you need to see during development. It
only really has a place in code once you have put it in production, and then
only if you have proper error handling in place.

In this particular case, it is hiding the error message that should be
thrown by your attempt to Split(QS(i),","). QS is not an array - its the
result of a calculation for each iteration of the loop, ie for each set of
answers you are processing, so you can just insert the value of QS into the
database:

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & QS "' where (empid ='" & strID(i) & "')"

--
Mike Brind
Dec 8 '06 #7

Mike Brind wrote:
"pmarisole" <jb******@midsouth.rr.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
Mike,
I have incorporated the code above and it works great except in
category 5 the user is allowed to
select an "N/A" or a value 1-10. It automatically calculates an "N/A"
in the 5th category even if I choose a value of 1-10 but it captures
the right value in the database table. That is the first problem and
I'm not sure how fix it.

Here is what the code looks like now:
MLevel2 = "0"
On Error Resume Next
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = round(rsScors("WS1"),2)
S22 = round(rsScors("WS2"),2)
S33 = round(rsScors("WS3"),2)
S44 = round(rsScors("WS4"),2)
S55 = round(rsScors("WS5"),2)
S66 = round(rsScors("WS6"),2)
S77 = round(rsScors("WS7"),2)
S88 = round(rsScors("WS8"),2)
S99 = round(rsScors("WS9"),2)

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (QQ5 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

Score = split((QS(i)),",")
I don't think the Score field is the proper SPLIT syntax because it is
not putting it in the database table when I execute it.

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & trim(Score(i)) & "' where (empid ='" & strID(i) &
"')"
C9.Execute(sSQL)
NEXT

************************************************** *********************************************
Here is the response.write output values when I run it:
The SQL statement is:
UPDATE EAPcurrentyear SET Q1= '6', Q2= '3', Q3= '5', Q4= '4',
Q5= '5', Q6= '4', Q7= '4', Q8= '6', Q9= '5' where (empid ='118694')

Q11 = 0.66
Q22 = 0.33
Q33 = 0.55
Q44 = 0.44
Q55 = N/A
Q66 = 0.44
Q77 = 0.44
Q88 = 0.66
Q99 = 0.55
Score= 4.58
As you can see the value in Q5 field is a 5 but the calculation sees it
as an "N/A" and it calculated the wrong Score. The calculation is
assigning all values in field 5 an "N/A" regardless of what value the
user selects so it only computes 8 categories all the time instead of 9
even when there is a value of 1-10 in field 5.

Finally, I am going to post the update back to the submitted page with
the calculated Score showing but I don't want to show a score until all
9 category values have been selected. I do want it to add the
individual category values to the database table, just not allow the
user to see a final score on the screen until all categories have been
assigned a value per each employee.

You have been so helpful and I am almost there with just a few tweaks.
I really appreciate your guidance. I am new at this and fell sooooo
overwhelmed. THANKS SO MUCH FOR YOUR HELP....this will take a load off
me.

FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S11
Q22 = Clng(QQ2(i))*S22
Q33 = Clng(QQ3(i))*S33
Q44 = Clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then 'changed variable here
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN 'changed variable here
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if
....
I've commented the 2 changes that I can see needed doing. They were slight
modification to variable names, which now means you are working with the
correct one.

I tend to use variable names that are more different from eachother than
yours when I'm performing operations on them and assigning the result to a
new value. Usually the kind of names I use describe the stage in the
operation that I'm at eg CurrentAnswerToQ1 instead of Q11. That way I find
it easier to see what I'm doing. Also, more descriptive names can be
self-documenting, which makes maintenance etc easier. It may take a bit
longer to type, but it can save hours in debugging.

When you revisit this code in 6 months or more, you might have a hard time
working out what is going on in the code. Also, just a glance at this type
of variable naming policy is enough to significantly reduce the number of
people prepared to plough through it to help you :-)

One other thing - I would remove On Error Remove Next from your code until
it's finished. It hides errors that you need to see during development. It
only really has a place in code once you have put it in production, and then
only if you have proper error handling in place.

In this particular case, it is hiding the error message that should be
thrown by your attempt to Split(QS(i),","). QS is not an array - its the
result of a calculation for each iteration of the loop, ie for each set of
answers you are processing, so you can just insert the value of QS into the
database:

sSQL = "UPDATE EDNCurr SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score = '" & QS "' where (empid ='" & strID(i) & "')"

--
Mike Brind

Mike,
Everything is working fine except the QS calculated field.
There are many employee records on this mass update grid.
The user may or may not fill out each employee record completely across
all categories. So they may jump around and fill out 2 or 3 categories
per employee but not the entire record across all categories at a given
time.

What it is doing is adding the QS computed value to each FY07Score
field
in the database for every employee in the recordset whether all their
categories have been filled out or not. So they may not have all
their categories scored but the database has a final calculated score
which
is the same as the employee who has a completed record.
In other words, it is replicating that value all the way down to each
employee in the recordset.

The users can move around and select values for the categories
that they see fit but not have to complete each record all at once, so
I need
it to save all the category values selected
by the user in the database but not compute a final score per employee
nor
add it (FY07Score) to the database until all categories have been
completed for that
employee.

Here is the code....
MLevel2 = "0"
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
C9.Execute(sSQL)
NEXT
rsScors.close
set rsScors = Nothing
C9.Close
Set C9 = Nothing

Thank you so much for your help. You are so good at this....
I hope I am as good at this one day as you are.

Dec 9 '06 #8

"pmarisole" <jb******@midsouth.rr.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
>
[snipped]
>

Mike,
Everything is working fine except the QS calculated field.
There are many employee records on this mass update grid.
The user may or may not fill out each employee record completely across
all categories. So they may jump around and fill out 2 or 3 categories
per employee but not the entire record across all categories at a given
time.

What it is doing is adding the QS computed value to each FY07Score
field
in the database for every employee in the recordset whether all their
categories have been filled out or not. So they may not have all
their categories scored but the database has a final calculated score
which
is the same as the employee who has a completed record.
In other words, it is replicating that value all the way down to each
employee in the recordset.

The users can move around and select values for the categories
that they see fit but not have to complete each record all at once, so
I need
it to save all the category values selected
by the user in the database but not compute a final score per employee
nor
add it (FY07Score) to the database until all categories have been
completed for that
employee.

Here is the code....
MLevel2 = "0"
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
C9.Execute(sSQL)
NEXT
rsScors.close
set rsScors = Nothing
C9.Close
Set C9 = Nothing
Put some code in to check that all answers have been provided. If they
have, calculate QS. If not, set QS to an empty string.

Alternatively, leave QS out of the code at this point, and calculate it when
you output the scores - if all questions have been answered.

--
Mike Brind

--
Mike Brind
Dec 9 '06 #9

Mike Brind wrote:
"pmarisole" <jb******@midsouth.rr.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
[snipped]


Mike,
Everything is working fine except the QS calculated field.
There are many employee records on this mass update grid.
The user may or may not fill out each employee record completely across
all categories. So they may jump around and fill out 2 or 3 categories
per employee but not the entire record across all categories at a given
time.

What it is doing is adding the QS computed value to each FY07Score
field
in the database for every employee in the recordset whether all their
categories have been filled out or not. So they may not have all
their categories scored but the database has a final calculated score
which
is the same as the employee who has a completed record.
In other words, it is replicating that value all the way down to each
employee in the recordset.

The users can move around and select values for the categories
that they see fit but not have to complete each record all at once, so
I need
it to save all the category values selected
by the user in the database but not compute a final score per employee
nor
add it (FY07Score) to the database until all categories have been
completed for that
employee.

Here is the code....
MLevel2 = "0"
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
C9.Execute(sSQL)
NEXT
rsScors.close
set rsScors = Nothing
C9.Close
Set C9 = Nothing

Put some code in to check that all answers have been provided. If they
have, calculate QS. If not, set QS to an empty string.

Alternatively, leave QS out of the code at this point, and calculate it when
you output the scores - if all questions have been answered.

--
Mike Brind

--
Mike Brind

Mike,

I did what you suggested and its works except the QS field is getting
confused and copying the calculated score all the way down for every
record in the recordset. If I make a change to say the 3rd record in
the recordset, then it will copy THAT QS calculated score for every
record thereafter. I have tried the following code to put QS in an
array and it doesn't give errors, but it just doesn't add/update any
records to the database at all.

Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText

D5 = rsScors("QDiv5")
SS5 = "0.0"
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")

strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99

If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = (QS + .001)
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

QS = split(QS(i),",")

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS(i) & "' where (empid ='" & strID(i) & "')"

Any idea what is wrong? It is not calculating the correct QS score all
the way down on the grid.
Thanks so much

Dec 12 '06 #10

Mike Brind wrote:
"pmarisole" <jb******@midsouth.rr.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...
[snipped]


Mike,
Everything is working fine except the QS calculated field.
There are many employee records on this mass update grid.
The user may or may not fill out each employee record completely across
all categories. So they may jump around and fill out 2 or 3 categories
per employee but not the entire record across all categories at a given
time.

What it is doing is adding the QS computed value to each FY07Score
field
in the database for every employee in the recordset whether all their
categories have been filled out or not. So they may not have all
their categories scored but the database has a final calculated score
which
is the same as the employee who has a completed record.
In other words, it is replicating that value all the way down to each
employee in the recordset.

The users can move around and select values for the categories
that they see fit but not have to complete each record all at once, so
I need
it to save all the category values selected
by the user in the database but not compute a final score per employee
nor
add it (FY07Score) to the database until all categories have been
completed for that
employee.

Here is the code....
MLevel2 = "0"
Set C9 = Server.CreateObject("ADODB.Connection")
C9.Open "Provider=sqloledb;Data
Source=ebs-sqlc1-vs3.edn.runi.com\SSS;UID=casuser;PWD=hppccas;DATAB ASE=skills"
Set rsScors= Server.CreateObject("ADODB.Recordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,adLockReadOnly, adCmdText
D5 = rsScors("QDiv5")
S11 = rsScors("WS1")
S22 = rsScors("WS2")
S33 = rsScors("WS3")
S44 = rsScors("WS4")
S55 = rsScors("WS5")
S66 = rsScors("WS6")
S77 = rsScors("WS7")
S88 = rsScors("WS8")
S99 = rsScors("WS9")
strID = split(request.form("Emp"), ", ")
QQ1 = split(request.form("Q1"),",")
QQ2 = split(request.form("Q2"),",")
QQ3 = split(request.form("Q3"),",")
QQ4 = split(request.form("Q4"),",")
QQ5 = split(request.form("Q5"),",")
QQ6 = split(request.form("Q6"),",")
QQ7 = split(request.form("Q7"),",")
QQ8 = split(request.form("Q8"),",")
QQ9 = split(request.form("Q9"),",")

FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
if (left(session("org"),5) = "10035") or (left(session("org"),5) =
"10032") then
QS = round(QS,2)
else
QS = round(QS,2)
end if
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if

sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"
C9.Execute(sSQL)
NEXT
rsScors.close
set rsScors = Nothing
C9.Close
Set C9 = Nothing

Put some code in to check that all answers have been provided. If they
have, calculate QS. If not, set QS to an empty string.

Alternatively, leave QS out of the code at this point, and calculate it when
you output the scores - if all questions have been answered.

--
Mike Brind

--
Mike Brind
Mike,
I did what you suggested and its works great except when the user
selects "N/A" in the 5th category, it does not calculate the
correct QS score. All other records that do not have "N/A" as a
selection calculates perfectly. I can't figure out why it does not
calculate the "N/A" score correctly. Do you see anything in the
code that doesn't look right? I can't figure out why it calculates
all records correctly except those with "N/A" selected in the 5th
category.
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S11
Q22 = clng(QQ2(i))*S22
Q33 = clng(QQ3(i))*S33
Q44 = clng(QQ4(i))*S44
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S55
end if
Q66 = Clng(QQ6(i))*S66
Q77 = Clng(QQ7(i))*S77
Q88 = Clng(QQ8(i))*S88
Q99 = Clng(QQ9(i))*S99
If (Q55 = "N/A") THEN
QS = ((Q11 + Q22 + Q33 + Q44 + Q66 + Q77 + Q88 + Q99) / D5)
QS = round(QS,2)
else
QS = round((Q11 + Q22 + Q33 + Q44 + Q55 + Q66 + Q77 + Q88 + Q99),2)
end if
sSQL = "UPDATE EAPcurrentyear SET Q1= '" & trim(QQ1(i)) & "', Q2= '" &
trim(QQ2(i)) & "', Q3= '" & trim(QQ3(i)) & "', Q4= '" & trim(QQ4(i)) &
"', Q5= '" & trim(QQ5(i)) & "', Q6= '" & trim(QQ6(i)) & "', Q7= '" &
trim(QQ7(i)) & "', Q8= '" & trim(QQ8(i)) & "', Q9= '" & trim(QQ9(i)) &
"', FY07Score= '" & QS & "' where (empid ='" & strID(i) & "')"

Any idea what is wrong?
Thanks so much

Dec 12 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Joseph S. | last post by:
Hi, How do I pass a string from one call to a php block to another call in the same page but from another form? Here's my full php code: I'm using two forms in the same page. A hidden field...
2
by: Ronny sigo | last post by:
Hello all, A big part of my life I have been working with databases (as a non professional) on a local machine. Apart from a small problem now and then, for which I find the solution myself, or...
0
by: leavandor | last post by:
I am trying to design a query that works with a relationship between a Table and a Query. I am comparing a value in the table with a computed value inside the query. The reason for this is that...
0
by: gavo | last post by:
Hi. using A2K; i have a form containing a continous subform. The question is, how can i call a calculation for one of the fields in the continous subform from the main form. At the moment i...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
0
by: pmarisole | last post by:
I need help in calculating a score for each employee based on values submitted from drop-down boxes Example: Emp1 TeamScore(1-10) Emp2 TeamScore(1-10) Emp3 TeamScore(1-10) Emp4 ...
0
by: Tomazas | last post by:
Hi All, I have very complex database and I am trying to build an Shaped SQL string with no success. Could anyone help me to answer how this would look like. TABLE LIST: Asset, Structure,...
3
by: heckstein | last post by:
I have created a query in MS Access 2003 that is pulling training records for our company that includes training hour calculation. One field I am pulling is the instructor name. Many courses have...
2
by: hcaptech | last post by:
This is my Test.can you help me ? 1.Which of the following statement about C# varialble is incorrect ? A.A variable is a computer memory location identified by a unique name B.A variable's name...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.