locked
Sum Rows and return to webpage. RRS feed

  • 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

    Tuesday, November 30, 2010 10:02 AM

Answers

  • User-62323503 posted

    I think NewPayment should be in double quote like this

    strFirstPayment = rstCreditor("NewPayment")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 30, 2010 11:19 AM

All replies

  • 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/

    Tuesday, November 30, 2010 10:43 AM
  • 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

    Tuesday, November 30, 2010 10:48 AM
  • 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) 

    Tuesday, November 30, 2010 10:50 AM
  • 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.


    Tuesday, November 30, 2010 11:14 AM
  • User-62323503 posted

    I think NewPayment should be in double quote like this

    strFirstPayment = rstCreditor("NewPayment")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 30, 2010 11:19 AM
  • 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

    Tuesday, November 30, 2010 11:28 AM