Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2005
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL - only getting the last results

    Hi, hope some1 can help me with this.. need this urgently...thanx!

    "For Each x in Action", the Action is the array that i got from another database, with 4 values... Now i'm trying to get the number of count of each value in another table...

    Code:
    Dim rs_eachAction
    Set rs_eachAction = Server.CreateObject("ADODB.Recordset")
    rs_eachAction.ActiveConnection = MM_dsprms_STRING
    
    For Each x in Action
    	sqlString = " AND Action_Taken = '" & (x) & "'"
    	sqlString = "SELECT COUNT(Action_Taken) as [numAct]  FROM Security_Vulnerability WHERE (tDate BETWEEN #" & startDate & "# AND #" & endDate & "#)" & sqlString
    	rs_eachAction.source = sqlString
    	rs_eachAction.CursorType = 0
    	rs_eachAction.CursorLocation = 2
    	rs_eachAction.LockType = 1
    	response.write "<p>" & rs_eachAction.source
    Next
    
    	rs_eachAction.Open()
    	NumAction = rs_eachAction.getRows
    	rs_eachAction.close()
    
    For x=0 to ubound(NumAction,2)
    	Response.write "<p>" & NumAction(0,x)
    Next
    Response.write SQL Statements:

    Code:
    SELECT COUNT(Action_Taken) as [numAct] FROM Security_Vulnerability WHERE (tDate BETWEEN #23-Feb-2006# AND #23-Feb-2006#) AND Action_Taken = 'Patch'
    
    SELECT COUNT(Action_Taken) as [numAct] FROM Security_Vulnerability WHERE (tDate BETWEEN #23-Feb-2006# AND #23-Feb-2006#) AND Action_Taken = 'Workaround'
    
    SELECT COUNT(Action_Taken) as [numAct] FROM Security_Vulnerability WHERE (tDate BETWEEN #23-Feb-2006# AND #23-Feb-2006#) AND Action_Taken = 'Take Note'
    
    SELECT COUNT(Action_Taken) as [numAct] FROM Security_Vulnerability WHERE (tDate BETWEEN #23-Feb-2006# AND #23-Feb-2006#) AND Action_Taken = 'No Action'
    I manage to search out using this 4 values, but my results for the count will only appear the last 1, which is the "No Action".

    How can i get the count value of each type?

  • #2
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You have the opening of the data ouside the loop. Therefore you are only getting the last data because it is the only data repersented in the rs_eachAction.source. You need to move the rest of it inside the loop to get the data for each one.
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #3
    New Coder
    Join Date
    Jan 2005
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx for your reply...
    i have got it work..


  •  

    Posting Permissions

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