recordCount and counting

[Ovatvvon]

Two questions..somewhat related...

Classic ADO had rs.recordCount. Is there a similar function in ADO.NET?


additionally,

I want to give site statistics. What to display total Work Requests on file, and then count the number of each work request type to show how many of the total work requests are in each category.

Using the datareader to retrieve the data, is there an efficient way to tell how many of each "problem type" there is...or is the only way to create an array and add a value of 1 to the total each time another of that problem type is encountered?

Problem type 1 (PC problems)
Problem type 2 (Network problems)
Problem type 3 (Phone problems)
etc. (this is a dynamic list and can change in the amount of problem types there are.

Make sense? Anyone know if there's a better way? the latter seams like it would slow everything down, along with be in-efficient. -Ovatvvon :-Q

 

[Isadore]

Ovatvvon: At the risk of saying something that might not be relevant (and if I ever do; just ignore) I have several reference books available, etc... so I might on occasion jump in and say something nonetheless (hey, if I get in the ballpark 50% of the time it might be worth it!)

Anyway, here's an idea. If you could set the "Value" of an Object in "Currency Manager" to your desired field, etc..., this might be a possibility, I have some example code I could post here...just hip shootin here...

 

[Ovatvvon]

not sure exactly where you're going with that. Can you explain that a lil more? -Ovatvvon :-Q

 

[link9]

dataReader has no way to just grab the number of rows. If you need this level of functionality (or anything past simply displaying the data, for that matter), you'd need to get yourself a dataset.

If you wanted, you could put each of your problem types into different table objects in your dataset, and then access the count of each like:

Dim pcCount As Integer = myDataSet.Tables("pcProblems&quot.Rows.Count

Here's a cool fact. If all you want is the count(*) from a table, or any other 1X1 result set, there's no need to even get a datareader. The command object has a method, .executeScalar which will return a single value, which represents the 0,0 position of any grid type set of data. So if I want to know the number of records in table, products,

cmdObj.commandText = "SELECT COUNT(*) FROM products"
dim numOfRecords as Integer = cmdObj.executeScalar()

man, that's nice. ;-)

paul

 

[Ovatvvon]

I think the executeScaler will work great for counting the total number of recods!

As far as the problemTypes. the "problemType" field in the database is a number field. If the problemType is 1 it's this problem, if it's 2 it's this problem, if it's 3 it's the other problem, etc.

It looks like the following:
Dim pcCount As Integer = myDataSet.Tables("pcProblems&quot.Rows.Count
would work if I had different fields for the different problemTypes...but I can't because we'd have to add another field for every new problemType we added.

Is there a simple way to count how many number 1's there are in the field, how many number 2's there are in the field, etc. ?
-Ovatvvon :-Q

 

[link9]

You can use the .select() method of the datatable object to get an array of datarows... I haven't tried it, but checking out the docs, it appears as if you could do something like this:

dim filterExpr as new string("probDesc = 1&quot
dim rowMatches as dataRow() = dataSet.tables("tableName&quot.select(filterExpr)

dim numOfMatches as integer = countRows(rowMatches)

someLabel.text = numOfMatches

private function countRows(byval rowMatches as datarow) as integer
dim output as integer
dim r as datarow
for each r in rowMatches
output += 1
next
return output
end function

or something like that. check out the docs on methods of the datatable --> select() and see what you come up with.


paul

 

[codestorm]

if you had a table like

pcproblems(
problemtypeid int,
problemdescription nchar(500),
...
)

why not execute a query like

select problemtypeid, count(*)
from pcproblems
group by problemtypeid codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>