<html> <head> <title>VCT INFO SYSTEM - COUNSELOR's administrator - TEST history</title> <!-- <link rel="stylesheet" type="text/css" href="../frame.css"> <script language="Javascript"> window.history.forward(1); </script> --> </head> <body onmouseover="window.status='Albert & Serge © 2005 All rights reserved.';return true"> <% 'Dim connLot 'Holds the string to connect to the db 'Dim adoCon 'Database Connection Variable Object 'Dim sSQL 'Holds the SQL query for the database 'Dim RsLot 'Holds the search recordset 'declare SQL statement that will query the database sSQL="SELECT * FROM followup WHERE vct_name LIKE '%musanzu'" sSQL= sSQL & "SELECT * FROM followup WHERE patient_result LIKE '%positive'" 'define the connection string, specify database 'driver and the location of database 'connection to the my SQL DATABASE 'create recordset Set connLot = Server.CreateObject("ADODB.Connection") Set RsLot = Server.CreateObject("ADODB.Recordset") connLot.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = dollakay; User Id = sa; Password=loukos" 'Open the recordset object, execute the SQL statement vct_name = Request.QueryString("vct_name") sSQL="SELECT vct_name, count(*) as patientCount FROM followup WHERE patient_result LIKE '%positive' GROUP BY vct_name HAVING count(*)>0 ORDER BY patientCount DESC" RsLot.Open sSQL, ConnLot, adopenstatic 'response.Write(sSQL) PatientCount = RsLot("PatientCount") response.write ("# OF PATIENTS TESTED POSITIVES") response.write (" ") response.write "" & PatientCount & "" %> </body> </html> Code (markup):
IMO it looks like you're doing this the hard way... What if you tried this: sSQL="SELECT * FROM followup WHERE vct_name LIKE 'musanzu' AND patient_result LIKE 'positive'" Code (markup): Then after you process the query output the results with: PatientCount = RsLot.RecordCount Response.Write "# OF PATIENTS TESTED POSITIVE" Response.Write " " Response.Write PatientCount Code (markup):