...

View Full Version : SQL - only getting the last results



Cuttie0506
02-23-2006, 03:37 AM
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...




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:




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?

SpirtOfGrandeur
02-23-2006, 12:48 PM
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.

Cuttie0506
02-27-2006, 03:55 AM
thanx for your reply...
i have got it work..



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum