|
 |
asp_databases thread: Totaling values
Message #1 by dap_1911@h... on Thu, 26 Jul 2001 21:36:44
|
|
Hello again,
I found a new problem, I am trying to see what my predecessor was trying
to do with this code. I know it is the "calculation" of the question
values. Each question has a value (i.e. q1, question 1; q2, question 2,
etc.) They are stored in a table. I am trying to add all these scores
together and get an average. Could someone help me?
Here is the code:
dim objConn, rs
dim strConnect, strRS, total, percent, totalscore
Set objConn = Server.CreateObject("ADODB.connection")
strConnect = "Provider=SQLOLEDB;Data
Source=online.scs.wsu.edu;UID=sa;PWD=recycler;DATABASE=level1_week3"
objConn.Open strConnect
set rs=Server.CreateObject("adodb.Recordset")
strRS="select * from week3test where user_id like '" & Session
("login")&"'"
rs.Open strRS, objConn, adOpenDynamic, adLockPessimistic, adCmdText
%>
<%
total = 0
do while rs.EOF = false
total = total + rs("q1")
rs.movenext
loop
'total = 0
'total is added up the old fashion way for right now.
'total = rs("q1") + rs("q2")+ rs("q3") + rs("q4") + rs("q5") + rs("q6") +
rs("q7") + rs("q8") + rs("q9") + rs("q10")+rs("q11")+rs("q12")+rs("q13")+rs
("q14")+rs("q15")+rs("q16")+rs("q17")+rs("q18")+rs("q19")+rs("q20")+rs
("q21")+rs("q22")+rs("q23")+rs("q24")+rs("q25")
'total = "q1" + "q2"+ "q3" + "q4" + "q5" + "q6" + "q7" + "q8" + "q9"
+ "q10" + "q11" + "q12" + "q13" + "q14" + "q15" + "q16" + "q17" + "q18"
+ "q19" + "q20" + "q21" + "q22" + "q23" + "q24" + "q25"
'rs("total") = total ' field total in
database is set to total value
totalscore = (total/25)
if totalscore < .80 then
Response.Write ("Failed") 'rs("pass") =
else
Response.Write ("Passed") 'rs("pass") =
end if
percent = totalscore * 100%>
<br>
<br>
<%Response.Write percent
'rs("percent") = percent
'rs.Update
%>
Another question... does anyone know what the problem is when you do the
command: rs("field_name") and it doesn't send any value?
thank you,
Derek
Message #2 by "Dallas Martin" <dmartin@z...> on Thu, 26 Jul 2001 19:55:39 -0400
|
|
I assume that the table structure is something like this: (agh!!!)
userid,
q1,
q2,
q3,
etc
Then a simple query like this should work:
SQL70 SELECT:
strSQL = "SELECT userid, (q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10
+ q11 + q12 + q13 + q14 + q15 + q16 + q17 + q18 + q19 + q20 + q21 + q22 +
q23 + q24 + q25)/25 AS TotalScore FROM week3test WHERE userid = " & Session
("login")
This assumes that NULLS are not allowed in the q1...q25 columns.
If NULLS are allowed, then you should convert the NULLS to 0, as in
ISNULL(q1,0) + ISNULL(q2,0), etc.
Of course NULLS aren't a concern in ACCESS2000, as the columns will have
been created with a default value of 0.
ACCESS2000 SELECT:
strSQL = "SELECT userid, (q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 +
q11 + q12 + q13 + q14 + q15 + q16 + q17 + q18 + q19 + q20 + q21 + q22 + q23
+ q24 + q25)/25 AS TotalScore FROM week3test WHERE userid = " & Session
("login")
set RS = Conn.Execute(strSQL)
response.write(rs(("TotalScore"))
response.write(formatpercent(rs("TotalScore")))
Dallas
----- Original Message -----
From: <dap_1911@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, July 26, 2001 9:36 PM
Subject: [asp_databases] Totaling values
> Hello again,
>
> I found a new problem, I am trying to see what my predecessor was trying
> to do with this code. I know it is the "calculation" of the question
> values. Each question has a value (i.e. q1, question 1; q2, question 2,
> etc.) They are stored in a table. I am trying to add all these scores
> together and get an average. Could someone help me?
>
> Here is the code:
>
> dim objConn, rs
> dim strConnect, strRS, total, percent, totalscore
>
> Set objConn = Server.CreateObject("ADODB.connection")
>
> strConnect = "Provider=SQLOLEDB;Data
> Source=online.scs.wsu.edu;UID=sa;PWD=recycler;DATABASE=level1_week3"
>
> objConn.Open strConnect
>
> set rs=Server.CreateObject("adodb.Recordset")
> strRS="select * from week3test where user_id like '" & Session
> ("login")&"'"
>
> rs.Open strRS, objConn, adOpenDynamic, adLockPessimistic, adCmdText
> %>
> <%
> total = 0
> do while rs.EOF = false
> total = total + rs("q1")
> rs.movenext
> loop
>
> 'total = 0
> 'total is added up the old fashion way for right now.
> 'total = rs("q1") + rs("q2")+ rs("q3") + rs("q4") + rs("q5") + rs("q6") +
> rs("q7") + rs("q8") + rs("q9") +
rs("q10")+rs("q11")+rs("q12")+rs("q13")+rs
> ("q14")+rs("q15")+rs("q16")+rs("q17")+rs("q18")+rs("q19")+rs("q20")+rs
> ("q21")+rs("q22")+rs("q23")+rs("q24")+rs("q25")
> 'total = "q1" + "q2"+ "q3" + "q4" + "q5" + "q6" + "q7" + "q8" + "q9"
> + "q10" + "q11" + "q12" + "q13" + "q14" + "q15" + "q16" + "q17" + "q18"
> + "q19" + "q20" + "q21" + "q22" + "q23" + "q24" + "q25"
>
> 'rs("total") = total ' field total in
> database is set to total value
>
> totalscore = (total/25)
>
> if totalscore < .80 then
> Response.Write ("Failed") 'rs("pass")
> else
> Response.Write ("Passed") 'rs("pass")
> end if
>
> percent = totalscore * 100%>
> <br>
> <br>
>
>
> <%Response.Write percent
> 'rs("percent") = percent
>
> 'rs.Update
>
> %>
>
> Another question... does anyone know what the problem is when you do the
> command: rs("field_name") and it doesn't send any value?
>
> thank you,
>
> Derek
Message #3 by Steve Carter <Steve.Carter@t...> on Fri, 27 Jul 2001 10:30:12 +0100
|
|
Looks like the database used to be not in normal form, i.e. there was
one
field for each question in the test, but now it is in normal form, i.e.
one
row for each question and nobody has bothered giving the db better
names.
If this is not the case then the code is a bit strang and seems to give
the
average score for question 1, but ONLY if there were exactly 25 tests.
I'd throw away the code and start over from the requirement
> -----Original Message-----
> From: dap_1911@h... [mailto:dap_1911@h...]
> Sent: 26 July 2001 22:37
> To: ASP Databases
> Subject: [asp_databases] Totaling values
>
>
> Hello again,
>
> I found a new problem, I am trying to see what my predecessor
> was trying
> to do with this code. I know it is the "calculation" of the question
> values. Each question has a value (i.e. q1, question 1; q2,
> question 2,
> etc.) They are stored in a table. I am trying to add all
> these scores
> together and get an average. Could someone help me?
>
> Here is the code:
>
> dim objConn, rs
> dim strConnect, strRS, total, percent, totalscore
>
> Set objConn =3D Server.CreateObject("ADODB.connection")
> =09
> strConnect =3D "Provider=3DSQLOLEDB;Data
>
Source=3Donline.scs.wsu.edu;UID=3Dsa;PWD=3Drecycler;DATABASE=3Dlevel1_we
ek3"
>
> objConn.Open strConnect
> =09
> set rs=3DServer.CreateObject("adodb.Recordset")
> strRS=3D"select * from week3test where user_id like '" & Session
> ("login")&"'"
> =09
> rs.Open strRS, objConn, adOpenDynamic,
> adLockPessimistic, adCmdText
> %>
> <%
> total =3D 0
> do while rs.EOF =3D false
> total =3D total + rs("q1")=09
> rs.movenext
> loop
>
> 'total =3D 0
> 'total is added up the old fashion way for right now.
> 'total =3D rs("q1") + rs("q2")+ rs("q3") + rs("q4") + rs("q5")
> + rs("q6") +
> rs("q7") + rs("q8") + rs("q9") +
> rs("q10")+rs("q11")+rs("q12")+rs("q13")+rs
>
("q14")+rs("q15")+rs("q16")+rs("q17")+rs("q18")+rs("q19")+rs("q20")+rs
> ("q21")+rs("q22")+rs("q23")+rs("q24")+rs("q25")
> 'total =3D "q1" + "q2"+ "q3" + "q4" + "q5" + "q6" + "q7" + "q8" +
"q9"
> + "q10" + "q11" + "q12" + "q13" + "q14" + "q15" + "q16" +
> "q17" + "q18"
> + "q19" + "q20" + "q21" + "q22" + "q23" + "q24" + "q25"
>
> 'rs("total") =3D total ' field total in
> database is set to total value
>
> totalscore =3D (total/25)
>
> if totalscore < .80 then
> Response.Write ("Failed") 'rs("pass") =3D
> else
> Response.Write ("Passed") 'rs("pass") =3D
> end if
>
> percent =3D totalscore * 100%>
> <br>
> <br>
>
>
> <%Response.Write percent
> 'rs("percent") =3D percent
>
> 'rs.Update
>
> %>
>
> Another question... does anyone know what the problem is
> when you do the
> command: rs("field_name") and it doesn't send any value?
>
> thank you,
>
> Derek
>
|
|
|
View Cart
