totals and database stats.. help me arrg! [Archive]

View Full Version : totals and database stats.. help me arrg!

startbar

09-27-2004, 09:13 PM

hi, please take a look here (http://www.asiashow.co.uk/register/visitors.asp)

you will see a table with all the entries from my database...

what i need is too total the fields like 'no of tickets' so i can have like a total number of tickets etc..

please help, i really need this soon.

thanks a lot
cheers

Roy Sinclair

09-27-2004, 09:41 PM

DIM TotalTickets

TotalTickets = 0

TotalTickets = TotalTickets + TicketsSoldValueFromYourDatabase

response.write "Total Tickets: " & TotalTickets

I'll leave it up to you to place the code into the appropriate places within the script you didn't post. Hint: Only the place where you add a value to TotalTickets should be within the loop where you output the detail lines.

startbar

09-29-2004, 05:26 PM

please help me input that in: heres the code


<%

dim objConn, strCon, objRS, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\PATHGOESHERE\members.mdb"
objConn.connectionstring = strCon
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
ON ERROR RESUME NEXT

Dim pageView, strNewsText

pageView=Request.Querystring("view")
%>

<html>
<head>
<title>visitors</title>
<link rel="stylesheet" href="/css/text.css" type="text/css">
</head>
<body>

<%if pageView="" THEN
%>

<%

' Set the SQL Statement to get the information from the database
strSQL="SELECT * FROM visitors ORDER BY Date DESC"
' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
%>

 
 

<div align="center">
<table cellpadding="0" cellspacing="0" width="100%" bgcolor="#A50A01" style="border: 0px solid #006699;">
<tr><td>
<table width="100%" cellpadding="3" cellspacing="0" border="0">
<tr>
<td>

<tr>

<td>Date</td>

<td>Name</td>

<td>House Name/No</td>

<td>Street Name</td>

<td>Town</td>

<td>County</td>

<td>Country</td>

<td>Postcode</td>

<td>Email</td>

<td>No of Tickets</td>

<td>Printed</td>

<td>Weddings</td>
<td>Food</td>
<td>Fashions</td>
<td>Jewellery</td>
<td>Travel</td>
<td>Entertainment</td>
<td>Dating</td>

</tr>

<%
' Write out the list of articles
DO WHILE NOT objRS.EOF%>

<%if intRowColor = 0 Then
Response.Write "<TR bgcolor=""#FFFFFF"" onMouseOver=""this.bgColor = '#f5f4db'"" onMouseOut =""this.bgColor = '#FFFFFF'"">"
intRowColor = 1
Else
Response.Write "<TR bgcolor=""#ffffff"" onMouseOver=""this.bgColor = '#f5f4db'"" onMouseOut =""this.bgColor = '#FFFFFF'"">"
intRowColor = 0
End if%>

<td><%=objRS("Date")%></td>
<td><%=objRS("Name")%></td>
<td><%=objRS("House")%></td>
<td><%=objRS("Street")%></td>
<td><%=objRS("Town")%></td>
<td><%=objRS("County")%></td>
<td><%=objRS("Country")%></td>
<td><%=objRS("Postcode")%></td>
<td><%=objRS("Email")%></td>
<td align="center"><%=objRS("Tickets")%></td>
<td><%=objRS("Printer")%></td>

<td><%=objRS("Opt1")%></td>
<td><%=objRS("Opt2")%></td>
<td><%=objRS("Opt3")%></td>
<td><%=objRS("Opt4")%></td>
<td><%=objRS("Opt5")%></td>
<td><%=objRS("Opt6")%></td>
<td><%=objRS("Op7")%></td>

</tr>
<%
objRS.MoveNext
Loop
objRS.Close
%>
</table></table> 

 Number of registrations = <%
'here's the connection to the mdb
Dim myConn,myPath
Set myConn = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("pathgoeshere\members.mdb")
myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
'now we will set up an sql statement to do the counting
Dim mySQL
mySQL = "Select Count(*) AS myTotal FROM visitors"

'here's a recordset being setup to run the connection
'and to run the sql statement thus doing all our math
Dim myRS
Set myRS = Server.CreateObject("ADODB.RecordSet")
myRS.Open mySQL, myConn

'and now we write it to the page
Response.Write myRS("myTotal")

' and we ALWAYS want to close our connections and recordsets!!!
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing
%>

<%ELSE%>
...
<%END IF%>
<%END IF%>

Roy Sinclair

09-29-2004, 08:54 PM

<%
DIM TotalTickets

TotalTickets = 0
' Write out the list of articles
DO WHILE NOT objRS.EOF
TotalTickets = TotalTickets + CInt(objRS("Tickets"))
%>

.....

<%
objRS.MoveNext
Loop
objRS.Close
response.write "<tr><td span=""18"">Total Tickets=" & TotalTickets & "</td></tr>"
%>
</table></table>

startbar

09-29-2004, 09:09 PM

so would i apply it like this?

it doesnt work at the moment..


<%

dim objConn, strCon, objRS, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\members.mdb"
objConn.connectionstring = strCon
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
ON ERROR RESUME NEXT

Dim pageView, strNewsText
' See what view the User is requesting
pageView=Request.Querystring("view")
%>

<html>
<head>
<title>visitors</title>
<link rel="stylesheet" href="/css/text.css" type="text/css">
</head>
<body>

<%if pageView="" THEN

%>

<%

' Set the SQL Statement to get the information from the database
strSQL="SELECT * FROM visitors ORDER BY Date DESC"
' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
%>

 
 

<div align="center">
<table cellpadding="0" cellspacing="0" width="100%" bgcolor="#A50A01" style="border: 0px solid #006699;">
<tr><td>
<table width="100%" cellpadding="3" cellspacing="0" border="0">
<tr>
<td>

<tr>

<td>Date</td>

<td>Name</td>

<td>House Name/No</td>

<td>Street Name</td>

<td>Town</td>

<td>County</td>

<td>Country</td>

<td>Postcode</td>

<td>Email</td>

<td>No of Tickets</td>

<td>Printed</td>

<td>Weddings</td>
<td>Food</td>
<td>Fashions</td>
<td>Jewellery</td>
<td>Travel</td>
<td>Entertainment</td>
<td>Dating</td>

</tr>

<%
' Write out the list of articles
DO WHILE NOT objRS.EOF%>

<%if intRowColor = 0 Then
Response.Write "<TR bgcolor=""#FFFFFF"" onMouseOver=""this.bgColor = '#f5f4db'"" onMouseOut =""this.bgColor = '#FFFFFF'"">"
intRowColor = 1
Else
Response.Write "<TR bgcolor=""#ffffff"" onMouseOver=""this.bgColor = '#f5f4db'"" onMouseOut =""this.bgColor = '#FFFFFF'"">"
intRowColor = 0
End if%>

<td><%=objRS("Date")%></td>
<td><%=objRS("Name")%></td>
<td><%=objRS("House")%></td>
<td><%=objRS("Street")%></td>
<td><%=objRS("Town")%></td>
<td><%=objRS("County")%></td>
<td><%=objRS("Country")%></td>
<td><%=objRS("Postcode")%></td>
<td><%=objRS("Email")%></td>
<td align="center"><%=objRS("Tickets")%></td>
<td><%=objRS("Printer")%></td>

<td><%=objRS("Opt1")%></td>
<td><%=objRS("Opt2")%></td>
<td><%=objRS("Opt3")%></td>
<td><%=objRS("Opt4")%></td>
<td><%=objRS("Opt5")%></td>
<td><%=objRS("Opt6")%></td>
<td><%=objRS("Op7")%></td>

</tr>
<%
objRS.MoveNext
Loop
objRS.Close
%>
</table></table> 

 Number of registrations = <%
'here's the connection to the mdb
Dim myConn,myPath
Set myConn = Server.CreateObject("ADODB.Connection")
myPath = Server.MapPath("\members.mdb")
myConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
'now we will set up an sql statement to do the counting
Dim mySQL
mySQL = "Select Count(*) AS myTotal FROM visitors"

'here's a recordset being setup to run the connection
'and to run the sql statement thus doing all our math
Dim myRS
Set myRS = Server.CreateObject("ADODB.RecordSet")
myRS.Open mySQL, myConn

'and now we write it to the page
Response.Write myRS("myTotal")

' and we ALWAYS want to close our connections and recordsets!!!
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing
%>

 

<%
DIM TotalTickets

TotalTickets = 0
' Write out the list of articles
DO WHILE NOT objRS.EOF
TotalTickets = TotalTickets + CInt(objRS("Tickets"))
%>

<%
objRS.MoveNext
Loop
objRS.Close
response.write "Total Tickets=" & TotalTickets & " "
%>
 

<%ELSE%>
...
<%END IF%>
<%END IF%>

thanks for your help by the way..

Roy Sinclair

09-29-2004, 09:42 PM

No. What I provided was supposed to be inserted into the code you already had, not tacked onto the bottom. I provided both your code and the new statements. I guess I'll post it again and highlight just the added statements so you can see where they should be inserted into your code.

<%
DIM TotalTickets

TotalTickets = 0
' Write out the list of articles
DO WHILE NOT objRS.EOF
TotalTickets = TotalTickets + CInt(objRS("Tickets"))
%>

..... this represents a skip where I left out a lot of your code because it was unchanged by this addition and was therefore no value in posting it again

<%
objRS.MoveNext
Loop
objRS.Close
response.write "<tr><td span=""18"">Total Tickets=" & TotalTickets & "</td></tr>"
%>
</table></table>

startbar

09-30-2004, 06:47 PM

ok thats brilliant it works.. however my problem is getting the same page to show my original database stuff and this new code..

i dont know how to show two lots of the <%
DIM TotalTickets

TotalTickets = 0
' Write out the list of articles
DO WHILE NOT objRS.EOF
TotalTickets = TotalTickets + CInt(objRS("Tickets"))
%>

..... this represents a skip where I left out a lot of your code because it was unchanged by this addition and was therefore no value in posting it again

<%
objRS.MoveNext
Loop
objRS.Close
response.write "<tr><td span=""18"">Total Tickets=" & TotalTickets & "</td></tr>"
%>
</table></table> 

stuff - for example if i wanted to show the totals for tickets and something else on the same page what code can i use?

this has been my main probelm with asp - showing 2 sets of data on one page. thanks

BuddhaMan

10-01-2004, 11:09 AM

Make two connections to the database, have two sql statements, and two recordsets. I gave a code example for what you want within the last couple of weeks so give search a try.

startbar

10-10-2004, 02:05 PM

does this just work for values of numbers?

<tr><td>
<%

' Set the SQL Statement to get the information from the database
strSQL="SELECT * FROM visitors ORDER BY Opt1 DESC"
' Open the Database
objRS.Open strSQL, objConn, adOpenKeyset, adLockPessimistic, adCmdText
IF NOT objRS.EOF THEN
%>

<%
DIM TotalOpt1

TotalOpt1 = 0
' Write out the list of articles
DO WHILE NOT objRS.EOF
TotalOpt1 = TotalOpt1 + CInt(objRS("Opt1"))
%>

<%
objRS.MoveNext
Loop
objRS.Close
response.write "Asian Weddings / Planning Interest </td><td> " & TotalOpt1 & ""
%>
</td></tr>
<%END IF%>

Lets say that the Value "Opt1" was a text value and said 'Asian Speed Dating' how would i get this code to still could how many times 'Asian Speed Dating' was entered?

urgently need help, thanks

Roy Sinclair

10-11-2004, 10:11 PM

If "Opt1" were a text value then the line for adding to the total would be:

TotalOpt1 = TotalOpt1 + 1
instead of
TotalOpt1 = TotalOpt1 + CInt(objRS("Opt1"))

Of course you might want to add it to the total only if it has a non-empty value so maybe

if Len(objRS("Opt1")) > 0 then
TotalOpt1 = TotalOpt1 + 1
end if

would be the preferred method.