Help with Calculation of Multiple Records

6

60325

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;DATABASE=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.
 
M

Mike Brind

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;DATABASE=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.
Click to expand...

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

pmarisole

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;DATABASE=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.
 
P

pmarisole

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;DATABASE=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.
 
P

pmarisole

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;DATABASE=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.
 
P

pmarisole

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;DATABASE=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.
 
M

Mike Brind

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

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) & "')"
 
P

pmarisole

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


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;DATABASE=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.
 
M

Mike Brind

pmarisole said:
[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;DATABASE=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
Click to expand...

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

pmarisole

Mike said:
pmarisole said:
[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;DATABASE=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
Click to expand...

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


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;DATABASE=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
 
P

pmarisole

Mike said:
pmarisole said:
[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;DATABASE=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
Click to expand...

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

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
 

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

Help with Calculation of Multiple Records 0
Need Help With Calculation 1
Trouble with prediction code, for the life of me I can't figure out why it isnt running properly. Help would be appreciated. 0
insert multiple records 11
insert multiple records to table 3
split array twice and do a multiple insert 1
Recordset Paging - Not Showing records on next page 3
checkboxes and other form inserts into a database 1

Members online

No members online now.
Total: 68 (members: 1, guests: 67)
Robots: 432

Forum statistics

Threads
473,995
Messages
2,570,228
Members
46,818
Latest member
SapanaCarpetStudio

Latest Threads

Top