Looping Problems working with numbers 1

[acewilli]

Hello,

I am creating a site using vbscript that allows people to vote using a points system. Each person gets 10 points a month in which they can give points to any other person that they want to vote for. What is the best way to do the loop code for this? Below is some more detail.

dim points '10 points total for each month
dim npoints 'this is the points that are given for voting
dim tpoints 'this is the result of points-npoints

I don't want anyone to be able to use more than 10 points but I need to keep up with how many points they use. I also need to reset the points to 10 each month while keeping up with how many they were awarded for the year to date as assigned to yrpoints above. Below is a little bit of code that I'm working on which is really bad right now. I appreciate any help in advance and hope that you understand what I need. Thank you! Acewilli

If Request.Form("submit") = "Update" Then
While not rsStatus.eof
ID = rsStatus("ID")
smember= Replace(Trim(Request.Form("txtmember"& ID)),"'","''")
ppoints= Replace(Trim(Request.Form("txtpoints"& ID)),"'","''")
npoints= Replace(Trim(Request.Form("txtnpoints"& ID)),"'","''")
'Need Loop here I think??
tpoints= ppoints - npoints
strSQL = "UPDATE Team SET member='"& smember &"', points='"& tpoints &"', tpoints='"& tpoints &"' WHERE ID="& ID
db.Execute strSQL
rsStatus.movenext
Wend
rsStatus.movefirst
End If

 

[damber]

I think you'll need to explain that one again.. you describe a voting system that has multiple separate transactions, which could occur over different periods of time. However you only post 1 piece of code, which is cycling through a recordset that is not explained.. what is the data set used? and updates what seems to be another table, again not explained.

A tad confusing. Try providing us with a break down of the requirements in little bits - e.g.:
1. Monthly points allocation
2. Points Award by User (per team?)
a. Get List of users to award points to
b. User selects users and adds points (up to 10) to those selected
c. Form is submitted and points added to database against users, and detracted from user awarding the points total accumulated points.

etc etc...

Then provide a code sample for the bits that are confusing you. That way we can put the code in context and answer the bits that you don't know, rather than trying to write an entire application for you..


A smile is worth a thousand kind words. So smile, it's easy!

 

[Chopstik]

I would try to manage the points system more within your database than via the front end. Set up a table with columns that would include Total Points, Points Received, Points Voted, etc. You can then query your table to retrieve the valid values and then populate them to your page without having to go through whatever series of loops. You could then setup a monthly process whereby you could reset the point totals at the beginning of each month or back them up to another table and then reset them in the original table.

This is just an idea but it would seem simpler than doing all of the work in ASP.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[acewilli]

First off here is the full page code. And in the next post are the requirements that you asked for. Thanks again in advance.


<center>
<FORM>
<INPUT TYPE="BUTTON" VALUE="Home" ONCLICK="window.location.href='../cssopt/Home.asp'">
</FORM>
</center>
<!-- #includes file="includes/dbconn.inc" -->
<hr>
<%

If Request.QueryString("s") = "" Or Request.QueryString("s") = "sn" Then
sortBy = "member"
Elseif Request.QueryString("s") = "pp" Then
sortBy = "points"
Elseif Request.QueryString("s") = "np" Then
sortBy = "npoints"
End If

strSQL = "Select * FROM Team ORDER BY "& sortBy
Set rsStatus = Server.CreateObject("ADODB.Recordset")
rsStatus.open strSQL, db

If Request.Form("submit") = "Update" Then
While not rsStatus.eof
ID = rsStatus("ID")
smember= Replace(Trim(Request.Form("txtmember"& ID)),"'","''")
ppoints= Replace(Trim(Request.Form("txtppoints"& ID)),"'","''")
npoints= Replace(Trim(Request.Form("txtnpoints"& ID)),"'","''")
tpoints= Replace(Trim(Request.Form("txttpoints"& ID)),"'","''")
If ppoints = "0" then
Response.Write "<meta HTTP-EQUIV=REFRESH content=0; url=../cssopt/Home.asp>"
elseif npoints > "0" then
tpoints= ppoints-npoints
response.write tpoints & " " & ppoints & " " & npoints
ppoints= tpoints
mopoints= npoints
yrpoints= mopoints
strSQL = "UPDATE Team SET member='"& smember &"', points='"& ppoints &"', yrpoints='"& yrpoints &"', mopoints='"& mopoints &"', npoints='"& npoints &"', tpoints='"& tpoints &"' WHERE ID="& ID
elseif npoints = "0" then
else
end if
db.Execute strSQL
rsStatus.movenext
Wend
rsStatus.movefirst
End If

%>
<form name="frmMain" action="1.asp?s=<%=Request.QueryString("s")%>" method="post">
<h1><b>Team Member Point Allocation:</b></h1>
<h4><b>You only have 10 points each month to award to any team member including yourself.</b></h4>
<table>
<tr>
<td><center><a href="1.asp?s=sn" class="style1">Team Member</a></center></td>
<td><center><a href="1.asp?s=pp" class="style1">Points Allocated</a></center></td>
</tr>
<%
If rsStatus.eof or rsStatus.bof Then
response.Write "<tr><td align=center></td></tr>"
else
rsStatus.movefirst
end if
If NOT rsStatus.eof Then
While Not rsStatus.eof
Response.Write "<tr><td align=center><p align=center><input type=text name=txtmember"& rsStatus("ID") &" Value='"& rsStatus("member") &"'></p></td>"
Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txtnpoints"& rsStatus("ID") &" Value='"& rsStatus("npoints") &"'></td></tr>"
rsStatus.movenext
Wend
End If
%>
</table>
<hr>
<table>
<td><center><a href="1.asp?s=np" class="style1">Remaining Points</a></center></td>
<%
rsStatus.movefirst
While Not rsStatus.eof
If rsStatus("ID")="1" then
If rsStatus("points")="10" then
Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txtppoints"& rsStatus("ID") &" Value='"& rsStatus("points") &"'readonly></td>"
else
Response.Write "<td align=center><input type=text size=2 maxlength=2 name=txttpoints"& rsStatus("ID") &" Value='"& rsStatus("tpoints") &"'readonly></td>"
end if
else
end if
rsStatus.movenext
Wend
%>
<tr>
<td colspan="3" align="center"><input type="submit" name="submit" value="Update" ></td>
</tr>
</table>
</form>
</body>

 

[acewilli]

The different ASP pages are below:
1)Home page - Displays Name, Earned Points for month and year along with a link to their own individual page where they can allocate points to other members for voting.

2)Individual page (this is the example in my last post) - Displays Name, #ofPts. to give away (default is 10 each month and is reset each month back to 10), also displays each other member that points can be awarded to from the individuals 10 points.

The variables:
Points = total to give for month; default 10
YRPoints = total earned points given to individual from other members for the year.
MOPoints = total earned points given to individual from other members for the month.
NPoints = total remaining points to give to other members each month.
TPoints = (Points - NPoints) = Remaining Points for the individual for the month. Ex: 10pts.-2pts(awarded to member2)=8pts.earned for member2

The points can be allocated to each member on the same ASP. The individual types in the number of points to give to each person (No more than 10 total) and then clicks an Update button that updates the database and displays to the screen.

Each Member Gets:
(Variable=Points)10 points each month to give to other members and it resets back to 10 at the beginning of each month.
(Variable=NPoints) this is the points given to each member by another individual at any given time throughout the month. EX: I could give 2pts to member2, 4pts to member5 and 2pts to member6 and still have 2pts left to give to another member.
(Variable=TPoints) this is basically Points-NPoints or the total number of points left after the member gives the other members points. TPoints=Points-NPoints NOTE: Not sure if this is a good way to do this or if I should just subtract NPoints from the Points to get this number?
(Variable=MOPoints) this is a total of points given to a member by all other members and is cumlative for the month.
(Variable=YRPoints) this is the total of points given to a member by all other members and is cumlative for the year. So this would be each months total (MOPoints) added together to get YRPoints.

Home.asp looks like this:

|Member|MOPoints|YRPoints|
|John | 25 | 59 |Link to Individual Page for John
|------------------------|
|Steve | 12 | 48 |Link to Individual Page for Steve
|------------------------|

Individual.asp looks like this:
NOTE: The bottom half is remaining points for me(John) to give to whomever I wish in the top half of the page. I could give 2pts. to myself and 5pts. to Steve and still have 3pts. left over to give for the rest of the month.

Below is how it looks by default when I first go to the page. Below it is how it looks after I assign points and click update.

|Member|Points Allocated|
|John | 0 |
|-----------------------|
|Steve | 0 |
|-----------------------|
|Mary | 0 |
|-----------------------|

|Remaining Points for Individual to Give Away|
|--------------------------------------------|
| 10 |
|--------------------------------------------|
| UPDATE BUTTON |
|--------------------------------------------|

So, I give these points to each member below and
|Member|Points Allocated|
|John | 2 |
|-----------------------|
|Steve | 5 |
|-----------------------|
|Mary | 0 |
|-----------------------|

|Remaining Points for Individual to Give Away|
|--------------------------------------------|
| 3 |
|--------------------------------------------|
| UPDATE BUTTON |
|--------------------------------------------|

I sure hope this helps a little because I'm lost at getting this thing to update correctly. Thanks again!

acewilli

 

[davejam]

Are you planning to keep a full history log of points allocated so that you can see who gave what to who in a given time span, or are you just looking at a total amount.

the difference being, a simple table that you increment allocated points (resets at the start of a month ala stored proc) and a voted points for which accrues when people give points.

if it is left that simple (again depends if you want/need a history for reporting) then it should actually be quite simple.

which is it to be, remember, if you go with simplicity you can't change your mind easily down the line.

dave j

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[acewilli]

I do need to keep the total points for the year yes so each month the total monthly points gets added to the yearly points and it doesn't reset until the end of the year. It would be nice to archive them just in case but all I really need to do is reset the monthly count at midnight on the 1st of each month. Oh....thinking a little more about this I do need to archive the comments with each vote so I guess I do need to keep them. No problem...I can just add a field to the db to store them. So, with all of this said what suggestion do you have for me? Server-side script that runs by task manager to do this or some other way?

Thanks in advance....

 

[Tarwn]

My preference in this case woul dbe to go with Damber's more complex example, having a table that would track all point assignments. The reason being that if you track it like that then a greta deal of your logic can be done inside your SQl statements. Basically I would envision it as:

Member Table
MemberId
MemberName
MonthlyPointsAllowance
etc

Points Table
AssignID (member that assigned them)
Numpoints
Assignee ID (member they were assigned to)
DateAssigned
Comment

To get the number of points a user has left for this month:
SELECT Member.MonthlyPointAllowance - SUM(Points.NumPoints)
FROM Member LEFT JOIN Points ON Member.MemberID = Points.AssignID
WHERE Month(DateAssigned) = Month(getdate()) AND Year(DateAssigned) = Year(getdate()) AND MemberID = SOME_VALUE_FROM_ASP
GROUP BY Member.MemberID, Member.MonthlyPointAllowance

To get the number of points a user has been assigned (for any time period):
SELECT SUM(Points.NumPoints)
WHERE AssigneeID = SOME_VALUE_FROM_ASP AND DateAssigned > SOME_DATE AND DateAssigned < SOME_OTHER_DATE

Assign points:
INSERT INTO Points(AssignID, NumPoints, AssigneeID, Comments)
VALUES(MEMBER_ID_1,POINTS_FROM_ASP,MEMBER_ID_2, COMMENTS_FROM_ASP)

Find the person who likes you the best (has given you the most points):
SELECT TOP 1 Member.MemberID, Member.Name, SUM(Points.NumPoints) as TtlPoints
FROM Member INNER JOIN Points ON Member.MemberID = Points.AssignID
WHERE Points.AssigneeID = MEMBER_ID_FROM_ASP
GROUP BY Member.MemberID, Member.Name
ORDER BY SUM(Points.Numpoints) DESC

Find the person you like the best:
Just like the previous one but swap the AssignID and AssigneeID fields

Monthly History of Giving Points for a user:
SELECT Sum(Points.NumPoints) As TotalPoints, Month(DateAssigned) as MonthAssigned, Year(dateAssigned) as yearAssigned
FROM Points
WHERE AssignID = MEMBER_ID_FROM_ASP
GROUP BY Month(DateAssigned), Year(DateAssigned)
ORDER BY Year(DateAssigned), Month(DateAssigned)

Now, that obviously doesn't solve your current problem unless you can alter your db structure, and you may be too far into it to do so at the moment.

The easiest solution would be to add a field to your user to hold the monthly allowance like I have (in case anyone ever gets more than ten). A server-side script would solve the problem, if you have access to Scheduled Tasks. If your using SQL Server you could set up a monthly job to run an update statement against your records which would probably be my preference.

In either case you would simply run an UPDATE statement that looked something like:
UPDATE Team SET points = pointsAllowance

If you do it as a SQL job then your done. If you do it as a VBScript then you would need a little more:

Option Explicit

Dim conn, sqlStr
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "connection string here"

sqlStr = "UPDATE Team SET points = pointsAllowance"
conn.Execute sqlStr

conn.Close
Set conn = Nothing

Generally when I create a scheduled task to runa VBScript file I will make the task do this as the command:
C:\Windows\System32\wscript.exe /B "C:\myfolder\myscriptname.vbs"

The /B makes it run in batch mode, so you won't get a console window popping up every month to annoy you for a few seconds while the script runs.

In any case, hopefully one of these will help,

-T