Sum Rows and return to webpage.

Question

User-1476589518 posted

Hi, I am new to ASP web. I have an Access db on webserver. In my ASP webpage I need to return the sum of rows which match criteria.

I am not sure where the sum needs to happen. I tried sum in the sql statement, but got an error when it tried to put it in asp page.

this is what I have

strSQL = "Select * from tblApplicantCreditors where ApplicantID = '" & strApplicantID & "'"

rstCreditor.Open strSQL, conDatabase

strFirstPayment = rstCreditor("CalculatedMonthlyPayment")

strSQL = "Select * from tblApplicantCreditors where ApplicantID = '" & strApplicantID & "'"
rstCreditor.Open strSQL, conDatabase
strFirstPayment = rstCreditor("CalculatedMonthlyPayment")

I changed to 

strSQL = "Select sum(CalculatedMonthlyPayment) as NewPayment from tblApplicantCreditors where ApplicantID = '" & strApplicantID & "' group by NewPayment"
rstCreditor.Open strSQL, conDatabase
strFirstPayment = rstCreditor(NewPayment)

I also tried to sum on the ASP side and can't get it.

Can someone point me in the right direction

Thanks

Answer 1

User-1199946673 posted

Can someone point me in the right direction

 

Ask your question on another forum, because these forums are intended for ASP.NET (.aspx) related questions only, and your code is classic ASP (.asp)

To give you a hint, remove the "group by" part

 But if you're new to classic ASP. why are you learning it anyway? May I suggest you start learning ASP.NET instead? You'll find many tutorials and video's on this site...

http://www.asp.net/get-started/
http://www.asp.net/web-forms/

Answer 2

User-1476589518 posted

Thank you

p.s. I'm not really trying to learn classic, I inherited it, and need to make changes. I am scheduled for .Net classes in January but have to keep it going until I can redo the whole thing from scratch in .Net

Answer 3

User-62323503 posted

Why you are using "group by NewPayment" and I dont think that "NewPayment" is the column of your table

Write the query as

strSQL = "Select sum(CalculatedMonthlyPayment) as NewPayment from tblApplicantCreditors where ApplicantID = '" & strApplicantID & "'"

rstCreditor.Open strSQL, conDatabase  
strFirstPayment = rstCreditor(NewPayment) 

Answer 4

User-1476589518 posted

I put in the group by because without it i get error

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/umi/AgreementRevision0.asp, line 96 

where line 96 is strFirstPayment = rstCreditor(NewPayment)

with group by in I get 

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/umi/AgreementRevision0.asp, line 95

where line 95 is rstCreditor.Open strSQL, conDatabase

so basically I put it in just trying different things.

Answer 5

User-62323503 posted

I think NewPayment should be in double quote like this

strFirstPayment = rstCreditor("NewPayment")

Answer 6

User-1476589518 posted

yep that did it, I tried that before, but it was probably with the group by clause in the select statement

thanks