table value totals help please [Archive]

startbar

09-13-2004, 09:48 PM

hi, i have this page with a table and lots of values from a database.

How can i create totals of the different fields etc.. such as total number of tickets..

I already have a little script for totalling the whole database entry but i need some help with the more specific totallying. thanks


<%

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

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MY DATABASE"
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 %>

<%

strSQL="SELECT * FROM visitors ORDER BY Date DESC"
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>

<%
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("DATABASE PATH")
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-13-2004, 10:16 PM

Totalling items usually involves initializing a "total" filed to zero and then adding one to it for each record or the value contained within each record tot the total.

Then it's merely a matter of printing that total at the approproate point and if necessary resetting the total field to 0 so it can be used again (such as the month changed so you print a monthly total and reset the monthly total to 0).

Do you understand what I'm saying here?

On an unrelated point I also noted that your code above has some serious errors that are not only easily fixed but will shrink your pages. All of those tags are incorrect since not a single one of them is closed by a matching tag. However, using is incorrect usage in the first place, this line:

<td>Date</td>

should be written as

<th class="textwhite">Date</th>

You'll need to add a rule to your CSS:

th { font-weight: bold; }

but that will clean up and shrink the html.

Likewise the code:

<td><%=objRS("Date")%></td>

should be

<td class="text"><%=objRS("Date")%></td>

Again that'll clean up and shorten the code you generate in your page.

startbar

09-14-2004, 06:36 PM

hi, thanks for your help but i really dont know enough about asp to use the info you gave me.. could i possibly have some examples?

thanks

Roy Sinclair

09-14-2004, 07:56 PM

It really has nothing to do with ASP per se, it's a standard programming technique:

dim myTotal1
dim myTotal2
...
myTotal = 0
do while not recordset.eof
myTotal1 = myTotal1 + 1
myTotal2 = myTotal2 + recordset("quatitysold")
...
recordset.movenext
loop
response.write "Total Records=" & myTotal1
response.write "Total Sales=" & myTotal2

That code sample should show how two types of simple totals would be created,

startbar

09-14-2004, 08:03 PM

ok so how would i apply that to my page?

sorry im thick! :confused:

thanks