Subtotals from a Database - Loop problem.

[Alfredomulet]

Hello there,

I need to calculate the total amount (SUM(SALDO_ACTUAL)) of the accounts belonging to a customer obtained from a database after the customer's id is obtained from an input box.

What I would like to obtain is:

ACC NUMBER AMOUNT
123090912 125.00
468468664 100.00

TOTAL 225.00

Is it possible with ASP? If so, how do I calculate the total outside the loop so it won't be cleared each new account is added to the table? Will I need JavaScript? Thanks in advance for your replies.

my code looks like

<% 'crear objeto recordset
set rsProds=Server.CreateObject("ADODB.Recordset")
rowCount=0 %>

<form method="post" class ="cssazuL2"> <font size="1" face="Verdana">INTRODUCIR Nº DE CÉDULA</font> 
  <input name="fldSearch" size="16" style='font-family: Verdana; font-size: 7pt'> <input type="submit"  size=64 value="Buscar">
</form>

<% if Request.Form("fldSearch")<>"" then
   strSearch=Request("fldSearch") %>
   
   <% 'especificar manejador de bases de datos
   strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("lobo.mdb")
   
   'define the search query
   strQuery = "SELECT DATOS_BANCO.NAC_EXT AS NAC_EXT, DATOS_BANCO.CEDULA AS CEDULA, DATOS_BANCO.NOMBRES_BANCO AS NOMBRES_BANCO, DATOS_BANCO.TELEFONO1_BANCO AS TELEFONO1_BANCO, DATOS_BANCO.DIRECCION_BANCO AS DIRECCION_BANCO, DEUDOR_CUENTA.NRO_CUENTA AS NRO_CUENTA, DEUDOR_CUENTA.MORA AS MORA, DEUDOR_CUENTA.SALDO_ACTUAL AS SALDO_ACTUAL, DEUDOR_CUENTA.INTERESES AS INTERESES, DEUDOR_CUENTA.PRINCIPAL AS PRINCIPAL, DEUDOR_CUENTA.F_INGRESO AS ACTUALIZACION FROM DATOS_BANCO INNER JOIN DEUDOR_CUENTA ON DATOS_BANCO.CEDULA = DEUDOR_CUENTA.CEDULA WHERE DATOS_BANCO.CEDULA LIKE '" & strSearch & "'"
   
   'open the search results recordset
   rsProds.Open strQuery, strProvider, , , adCmdText 
   
   scriptregresar="deudoract.asp"
   %>
.....
.....
     <tr>

      <% 'cycle thru the record set and display each row results
      do until rsProds.EOF %>
    <td width="100" SIZE=2><%= rsProds("NRO_CUENTA")%></td>
	<td width="35" align=center><%= rsProds("MORA")%></td>
	<td width="120" align=right><%= "Bs. " & formatnumber(rsProds("SALDO_ACTUAL"),2)%></td>
	<td width="120"></td>
	<td width="120"></td>
</tr>

      <!-- next Row = next Record -->
      <% rowCount = rowCount+1
      'increment record position with MoveNext method
      rsProds.MoveNext
      loop
   else %>
      <td colspan="6" align="center" width="601"><h5>No se han encontrado resultados.</h5></td></tr>
   <% end if %>

 

[sugarthans]

After the loop is over write one more query using a sum Operator to get the total. Display The value after the loop to get it done.

If its using sql-server you can either use a compute sum(field) operator. Dont think Access support it.

This sounds simple.
Am i right or am i misunderstanding something?

Sugarthan

 

[Alfredomulet]

thanks sugarthans, two separate queries should work... (think, alfredo, think!):

Just to be sure, to open two queries with the same recordset I'll do it this way, right?:

...
'connection
sconectstring = "..."
'recordset
set strProd= "...."
'first query
set strProd.Open "the first sql query", sconectstring
'processing
Do  while not strProd.EOF
  'first tables here...
   strProd.MoveNext
Loop
'close first query
strProd.Close

'open second query

set strProd.Open "the second sql query", sconectstring
'processing again
Do  while not strProd.EOF
  'totals here...
   strProd.MoveNext
Loop
'close second query
strProd.Close
'free it up
set strProd = Nothing

 

[sugarthans]

Yup. you are right alfredo. For more code enhancement, there is no need of opening a recordset for this.
Since the count() query is going to return a value atleast 0 for sure,no need of opening recordset nd closing it again

you can just use,

connection.execute("select count(*) from table").fields(0)

Anyway your statment will execute perfectly

Sugarthan

 

[Tarwn]

There is a problem in your last sample code, which may or may not be in your real code also:

'connection
sconectstring = "..."
'recordset
set strProd= "...."
'first query
set strProd.Open "the first sql query", sconectstring

That section won't work, it's syntactically incorrect. You should have something that looks like one of the following:

[b]Either[/b]
'connection
sconectstring = "..."
'recordset
set strProd= "...."
'first query
strProd.Open "the first sql query", sconectstring

[b]Or[/b]
'connection
sconectstring = "..."
Set sconectobj = Server.CreateObject("ADODB.Connection")
sconectobj.Open sconectstring
'recordset
set strProd=sconectobj.Execute("your sql string here")

When just executing the Open method off your recordset object you do not need a set because your not assigning anything, just executing an internal method. If you want to use the connection execute method outlined in sugarthans last post you will need to create the connection object (though you will not need to create the ADODB.Recordset object using this method) and open the connection. My ersonal preference is to use the connection object.

-T


_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[Alfredomulet]

thanks sugarthans, Tarwn.

Tarwn, you're right, the SET command is not in my final code, it is written as the first option you show.

'connection
sconectstring = "..."
'recordset
set strProd= "...."
'first query
strProd.Open "the first sql query", sconectstring

What are the advantages and dissadvantages of using the connection object instead of the first option? Performance??

thanks!
Alfredo

 

[Tarwn]

I've found performance to generally be beter with the connection object. For one thing, if you .Open a recordset I have found the overhead to be higher than simply using a recordset that was generatd as part of en Execute. If you use Dynamic or Keyset options for the Open then your going to hav a lot of overhead as the system goes back to the database to query for more records as you loop through it.
My personal preference when dealing with more than about 30 records is to use the Connection object, then use the GetRows method of the recordset to dump the data to an array so I can get rid of the Recordset and COnnection soonest. Looping through an array is a lot more efficient and you don't hav the extra overhead of keeping the connection open (not to mention the extra storage needed for the recordset object and connection object over a two-dimensional array).

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/