Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    20

    Unanswered: Calculate total for recordset particular data

    I got a problem on calculating the total for particular data. This is how it works, the drop down list display the data from database, same data group as one, I use distinct for that. Example, in drop down list has, A,B,C
    I want to calculate how many A, B and C and put them in seperate text box.
    If I choose A in drop down list, then it will display the total in textbox, if I choose B it will display the total in textbox.

    <select size="1" name="list2">
    .
    .
    <option value="<%=rs("Course_Title")%>"><%=rs("Course_Titl e")%></option>
    .
    .
    </select>
    .
    .
    Set rs2 = Server.CreateObject("ADODB.Recordset")
    rs2.open "SELECT * FROM Registration where Course_Title = '" & Request.Form("list2") & "' and Approval_Status = 'yes' ",Conn

    Dim strCount
    strCount = 0
    While NOT rs2.EOF AND NOT rs2.BOF
    strCount = strCount + 1
    rs2.MoveNext()
    Wend
    .
    .
    Approved:<input type="text" name="appcount" size="10" value="<%=strCount%>">

    Hope anyone can help, thanks.
    Yutaka

  2. #2
    Join Date
    Feb 2003
    Posts
    20

    forgot to add something

    it display 0 in the textbox

    Yutaka

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    if all you want is the record count, do it in the SQL statement:

    Code:
    rs2.open "SELECT Count(*) AS RecordCount FROM Registration where Course_Title = '" & Request.Form("list2") & "' and Approval_Status = 'yes' ",Conn
    
    Approved:<input type="text" name="appcount" size="10" value="<%=rs2("RecordCount")%>">
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Feb 2003
    Posts
    20

    new problem

    my previous code:

    set rs = conn.execute("select distinct Course_Title,Date,Month from Registration")
    .
    .
    <select size="1" name="list2">
    .
    .
    <option value="<%=rs("Course_Title")%>"><%=rs("Course_Titl e")%></option>
    .
    .
    </select>
    .
    .
    Set rs2 = Server.CreateObject("ADODB.Recordset")
    rs2.open "SELECT Course_Title, [Date], [Month], COUNT(Course_Title) AS [no] FROM Registration WHERE Approval_Status ='NO' GROUP BY Course_Title, [Date], [Month]",Conn

    Dim strCount
    strCount = 0
    While NOT rs2.EOF AND NOT rs2.BOF
    strCount = strCount + 1
    rs2.MoveNext()
    Wend
    .
    .
    Approved:<input type="text" name="appcount" size="10" value="<%=strCount%>">

    But it display one time only. Example, I got record A(NO), A(NO),A(YES),
    B(NO), B(NO),B(NO),B(YES) in drop downlist.

    when it display the number for A where Approval Status is "NO", it only 2, meaning got two A(NO) it counts as 1. Got 2 B(NO) counted as 1
    Then it display 2 in the textbox. It should be 3 if I choose record B.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •