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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, adCmdText
D5 = rsScors("QDiv5" )
SS5 = "0.0"
S11 = round(rsScors(" WS1"),2)
rsScors.close
set rsScors = Nothing
strID = split(request.f orm("Emp"), ", ")
QQ1 = split(request.f orm("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.
10  2540 
<60***@fedex.co mwrote in message
news:11******** **************@ j44g2000cwa.goo glegroups.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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, adCmdText
D5 = rsScors("QDiv5" )
SS5 = "0.0"
S11 = round(rsScors(" WS1"),2)
rsScors.close
set rsScors = Nothing
strID = split(request.f orm("Emp"), ", ")
QQ1 = split(request.f orm("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.F orm("Q1")*S11," ,") all about? If
Request.Form("Q 1") 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
FOR i = LBound(strID) TO UBound(strID)
MyNum = Clng(QQ1(i))*S1 1 '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
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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.
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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.
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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.
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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.
"pmarisole" <jb******@midso uth.rr.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then 'changed variable here
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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 CurrentAnswerTo Q1 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 Brind wrote:
"pmarisole" <jb******@midso uth.rr.comwrote in message
news:11******** **************@ l12g2000cwl.goo glegroups.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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = Clng(QQ1(i))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5 = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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))*S1 1
Q22 = Clng(QQ2(i))*S2 2
Q33 = Clng(QQ3(i))*S3 3
Q44 = Clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then 'changed variable here
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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 CurrentAnswerTo Q1 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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S1 1
Q22 = clng(QQ2(i))*S2 2
Q33 = clng(QQ3(i))*S3 3
Q44 = clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.
"pmarisole" <jb******@midso uth.rr.comwrote in message
news:11******** **************@ 79g2000cws.goog legroups.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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S1 1
Q22 = clng(QQ2(i))*S2 2
Q33 = clng(QQ3(i))*S3 3
Q44 = clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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 Brind wrote:
"pmarisole" <jb******@midso uth.rr.comwrote in message
news:11******** **************@ 79g2000cws.goog legroups.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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S1 1
Q22 = clng(QQ2(i))*S2 2
Q33 = clng(QQ3(i))*S3 3
Q44 = clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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.CreateOb ject("ADODB.Con nection")
C9.Open "Provider=sqlol edb;Data
Source=ebs-sqlc1-vs3.edn.runi.co m\SSS;UID=casus er;PWD=hppccas; DATABASE=skills "
Set rsScors= Server.CreateOb ject("ADODB.Rec ordset")
sSQ5 = "Select * from WgtScore where org = '" & session("orgg") & "'
AND mgmtlevel = '" & MLevel2 & "'"
rsScors.Open sSQ5, C9, adOpenKeySet,ad LockReadOnly, 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.f orm("Emp"), ", ")
QQ1 = split(request.f orm("Q1"),",")
QQ2 = split(request.f orm("Q2"),",")
QQ3 = split(request.f orm("Q3"),",")
QQ4 = split(request.f orm("Q4"),",")
QQ5 = split(request.f orm("Q5"),",")
QQ6 = split(request.f orm("Q6"),",")
QQ7 = split(request.f orm("Q7"),",")
QQ8 = split(request.f orm("Q8"),",")
QQ9 = split(request.f orm("Q9"),",")
FOR i = LBound(strID) TO UBound(strID)
Q11 = clng(QQ1(i))*S1 1
Q22 = clng(QQ2(i))*S2 2
Q33 = clng(QQ3(i))*S3 3
Q44 = clng(QQ4(i))*S4 4
if (QQ5(i) = "N/A") Then
Q55 = "N/A"
else
Q55 = Clng(QQ5(i))*S5 5
end if
Q66 = Clng(QQ6(i))*S6 6
Q77 = Clng(QQ7(i))*S7 7
Q88 = Clng(QQ8(i))*S8 8
Q99 = Clng(QQ9(i))*S9 9
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 'f' with values
'1' and
'2' are used to distinguish calls from one form from those from the
|
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 some of you guys provide me with
the solution, all goes well. But now, for the first time I am faced with the
porblem that I have to run Access over a network, and my program, which
worked so fine on a single machine does not function .... Here's...
|
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 the Query calculates a field to become
identical to the corresponding table field, for instance:
Table 1 contains field "ID", which is WV008A00
Table 2 contains field "ID In", which is WV001000
|
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 have a button on the main form that will call a public code
containing the calculation i need, the problem i have is that it will only
perform the calculation on the first record on the continous subform and i
need it for all the records
|
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 for long running reports. When
the processing is complete it uses crystal reports to load a template file,
populate it, and then export it to a PDF.
It works fine so far....
| | |
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 TeamScore(1-10)
Emp5 TeamScore(1-10)
I want to take the values from each drop-down and multiply them by a
|
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, Element, History
SQL should look like:
Lets make shorter Table Names:
Asset as A
Structure as S
Element as E
|
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 multiple instructors, which means I am getting multiple records for a single course. I need to capture all of the instructor names, but I only want a single record for each course due to the hour calculation. I would like the report to generate only one...
|
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 is used to access and read the value stored in it
C.A variable is allocated or deallocated in memory during runtime
D.A variable can be initialized at the time of its creation or later
2. The.……types feature facilitates the definition of classes...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
