Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select and Count

Select and Count

From
"Shavonne Marietta Wijesinghe"
Date:
Hello
 
I have a postgresql table and i do a select via ASP
 
strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND TRIM(date_inserted) <= '" & datafine & "'"
 
oRs.open strSQL,oConn,3
schede = oRs.RecordCount
 
Do until oRs.EOF
 sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
oRs.movenext
Loop
 
Then inside my asp page in the body i write the result. and this works
 
<%
 Response.write schede
%>
 
Then i want to get the SUM of the values inserted in the SCHE_SINGOLA column (defined as text)
 
<%
 Response.Write sch_sin
%>
 
But here i get a type mismacth error..
What should i do?
 
Thanks
 
Shavonne Wijesinghe
www.studioform.it
 

Re: Select and Count

From
Martin Marques
Date:
On Tue, 20 Mar 2007, Shavonne Marietta Wijesinghe wrote:

> Hello
>
> I have a postgresql table and i do a select via ASP
>
> strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND TRIM(date_inserted)
<='" & datafine & "'" 
>
> oRs.open strSQL,oConn,3
> schede = oRs.RecordCount
>
> Do until oRs.EOF
> sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
> oRs.movenext
> Loop

I know nothing about ASP, but it looks like you are doing a SUM of an int
with, maybe, an array (don't know how ASP defines oRs().Value output).

Keep in mind that you are pulling all the columns of that table (as you
used a * in the column selection).

Just a guess, nothing more then that.

-- 21:50:04 up 2 days,  9:07,  0 users,  load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués         |   SELECT 'mmarques' ||
Centro de Telemática        |       '@' || 'unl.edu.ar';
Universidad Nacional        |   DBA, Programador,    del Litoral             |   Administrador
---------------------------------------------------------

Re: Select and Count

From
"Bart Degryse"
Date:
Don't know any ASP but maybe Response.Write only accepts strings. In that case you would need to do something like
<%
 Response.Write cStr(sch_sin)
%>
 
Maybe you can let the database do the summing:
 
<% option explicit %>
<%
  Dim strSQL
  Dim oRs
  Dim oConn
 
  oConn = ... 'Fill this in for yourself
 
  strSQL = "SELECT COUNT(SCHE_SINGOLA) COU, SUM(SCHE_SINGOLA) SU FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND TRIM(date_inserted) <= '" & datafine & "'"
 
  oRs.open strSQL,oConn,3
 
  If oRs.RecordCount = 1 Then
    Response.Write(oRs("COU"))
    Response.Write(oRs("SU"))
  Else
    Response.Write("Something went wrong")
  End If
%>

>>> Martin Marques <martin@bugs.unl.edu.ar> 2007-03-20 12:55 >>>
On Tue, 20 Mar 2007, Shavonne Marietta Wijesinghe wrote:

> Hello
>
> I have a postgresql table and i do a select via ASP
>
> strSQL = "SELECT * FROM " & MioTabella & " WHERE TRIM(date_inserted) >= '" & datainizio & "' AND TRIM(date_inserted) <= '" & datafine & "'"
>
> oRs.open strSQL,oConn,3
> schede = oRs.RecordCount
>
> Do until oRs.EOF
> sch_sin = cint(sch_sin) + cint(oRs("SCHE_SINGOLA").Value)
> oRs.movenext
> Loop

I know nothing about ASP, but it looks like you are doing a SUM of an int
with, maybe, an array (don't know how ASP defines oRs().Value output).

Keep in mind that you are pulling all the columns of that table (as you
used a * in the column selection).

Just a guess, nothing more then that.

--
  21:50:04 up 2 days,  9:07,  0 users,  load average: 0.92, 0.37, 0.18
---------------------------------------------------------
Lic. Martín Marqués         |   SELECT 'mmarques' ||
Centro de Telemática        |       '@' || 'unl.edu.ar';
Universidad Nacional        |   DBA, Programador,
     del Litoral             |   Administrador
---------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster