RecordCount Problem

[Corneliu]

OK. What am I doing wrong here? I am trying to get all the records in the Knowledge table that match Software Category. Each record has a different category (software, hardware, microsoft stuff, etc), and I want to retreive that and diplay the total # of Q&As found. Anyone can help me here please:

<%
'here's the connection to the mdb
Dim myConnSoftware,myPathSoftware
Set myConnSoftware = Server.CreateObject(&quot;ADODB.Connection&quot
myPathSoftware = Server.MapPath(&quot;/Databases/Knowledge.mdb&quot
myConnSoftware.Open &quot;Driver={Microsoft Access Driver (*.mdb)}; DBQ=&quot; & myPathSoftware & &quot;;&quot;
'now we will set up an sql statement to do the counting
Dim mySQLSoftware
mySQLSoftware = &quot;Select Count(*) AS Software FROM Knowledge Where Category= '&quot; & Software & &quot;'&quot;

'here's a recordset being setup to run the connection
'and to run the sql statement thus doing all our math
Dim myRSSoftware
Set myRSSoftware = Server.CreateObject(&quot;ADODB.RecordSet&quot
myRSSoftware.Open mySQLSoftware, myConnSoftware

'and now we write it to the page
Response.Write myRSSoftware(&quot;Software&quot

' and we ALWAYS want to close our connections and recordsets!!!
myRSSoftware.Close
Set myRSSoftware = Nothing
myConnSoftware.Close
Set myConnSoftware = Nothing
%>


I also had the code this way, and still didnt work, same as above but with the Select statement different:

mySQLSoftware = &quot;Select Distinct Count(Category) AS Software FROM Knowledge Where Category= '&quot; & Software & &quot;'&quot;

And this way:
mySQLSoftware = &quot;Select Distinct Count(Category) AS Software FROM Knowledge&quot;
mySQLSoftware = mySQLSoftware & &quot; Where Category= '&quot; & UltiPro & &quot;'&quot;


Everyway I put it, it gives me 0. I have different categories in each record, but want to get only records that has category Software or Hardware, etc etc.
Thank You For Your Help.

 

[hiteshkapadia]

What database are you using? Try to run the query directly in the database and see if you still get 0 or some other value.

Hitesh

 

[Corneliu]

I am using Access 2000 database. I didnt try to do it in Access. I guess thats the next step. If anyone has other code that could help me, please post it, I would really appreciate it very much. I have been all over the net looking for scripts, but all I find is records count that only count all of them, not by a field name.

Anyone can help me here?

 

[Corneliu]

Finally Got it. Just in case anyone needs it, counting records depending on records data:

<!-- #include file=&quot;../Connections/adovbs.inc&quot; -->
<!-- #include file=&quot;../Connections/Knowledge.asp&quot; -->

<%
'Connection open is above in Knowledge.asp File as Non-DSN
Dim strSQL
strSQL = &quot;SELECT * FROM Knowledge WHERE Category = 'Hardware'&quot;

Dim ObjRS
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
objRS.Open strSQL, objConn

Dim HardwareCount
HardwareCount = 0

while not objRS.EOF

HardwareCount = HardwareCount + 1

objRS.MoveNext
WEND

'Writes the total # of Records found depending on the Where Cause
Response.Write (HardwareCount)

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

%>

 

[Tarwn]

How about something like:

Select Count(catagory) as num, catagory FROM Knowledge GROUP BY catagory ORDER BY catagoty

Tha should return a recordset of the catagories with the count for each, ordered alphabetically

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri