Hi!

I've once again got a problem. Im building a vote-system. You are suppose to

vote from 1-10 and the result will be saved in a table in the database. I'm

using cookies so the user can only vote one time. It's no problem adding new

values, but I don't know how to get the average value of these values. I

must add all values and then divide them by the number of votes, right? But

how do I write this?

--

Martin Johansson

Something like this:







<%

	dim totalvote

	dim votecount

	dim averagevote

	

	

	Set rsd2 = Server.CreateObject("ADODB.Recordset")

	strSQL= "SELECT * FROM tblRating WHERE CodeID = " & choice 

	rsd2.Open strSQL, conn, 1'adOpenForwardOnly

	votecount = rsd2.recordcount

	

	do while NOT rsd2.EOF

		totalvote = totalvote + rsd2("rating")

		rsd2.movenext

	loop

	if votecount > 0 then

	averagevote = int(totalvote/votecount)+1

	end if

	

	%>

                    <img src="images/<%=averagevote%>stars.gif" width="62"

height="12"> 



No doubt better way...



Thanks



Sam







-----Original Message-----

From: SD-Studios [mailto:info@s...]

Sent: 24 September 2001 15:51

To: ASP Databases

Subject: [asp_databases] Vote System





Hi!

I've once again got a problem. Im building a vote-system. You are suppose to

vote from 1-10 and the result will be saved in a table in the database. I'm

using cookies so the user can only vote one time. It's no problem adding new

values, but I don't know how to get the average value of these values. I

must add all values and then divide them by the number of votes, right? But

how do I write this?

--

Martin Johansson

Sure does, we use it for a very basic 'rate this article' system, it does'nt

have cookie stuff or nowt fancy but yeah it works..



-----Original Message-----

From: SD-Studios [mailto:info@s...]

Sent: 24 September 2001 16:26

To: ASP Databases

Subject: [asp_databases] SV: RE: SV: RE: Vote System





But it works, right? =)

Thanks again! =)

--

Martin Johansson

Whoa there. There are faster ways of doing that.



First comment, never use recordcount. There is a page about it on Ken's site

(www.adopenstatic.com), but basically to get a true result you need to move

last and move first, which is a performance loss. COUNT(*) in the SQL

statement is always a better option.



<%

	dim averagevote

		

	Set rsd2 = Server.CreateObject("ADODB.Recordset")

	strSQL= "SELECT * FROM tblRating WHERE CodeID = " & choice

	strSQL = "SELECT SUM(rating)/COUNT(*) as Average " & _

	  "FROM tblRating WHERE CodeID = " & choice

	rsd2.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

	rsd2.close

	Set rsd2 = Nothing

	averagevote = int(rsd2("Average"))

	If averagevote < 1 Then averagevote = 1

	

%>

                    <img src="images/<%=averagevote%>stars.gif" width="62"

height="12"> 



I checked the SQL string for SQL Server, it should work in Access. If not

you can return the sum and 2 different fields for Count and Sum and do the

calculation in the page.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Sam Clohesy [mailto:sam@e...]

Sent: Tuesday, 25 September 2001 1:06 AM

To: ASP Databases

Subject: [asp_databases] RE: Vote System





Something like this:



<%

	dim totalvote

	dim votecount

	dim averagevote

	

	

	Set rsd2 = Server.CreateObject("ADODB.Recordset")

	strSQL= "SELECT * FROM tblRating WHERE CodeID = " & choice 

	rsd2.Open strSQL, conn, 1'adOpenForwardOnly

	votecount = rsd2.recordcount

	

	do while NOT rsd2.EOF

		totalvote = totalvote + rsd2("rating")

		rsd2.movenext

	loop

	if votecount > 0 then

	averagevote = int(totalvote/votecount)+1

	end if

	

	%>

                    <img src="images/<%=averagevote%>stars.gif" width="62"

height="12"> 



No doubt better way...



Thanks



Sam