Dataset Count Problem 1

[IndyGill]

Hi, I have a dataset and I am trying to filter the information in many ways on a page. I would like to show the count/occurences of a specific column.

eg, my dastset looks like

Name
Fred
Lisa
Fred
Mike
Fred
Mike

and would like to show it in a datagrid as

Name Occurences
Fred 3
Mike 2
Lisa 1

I am trying the following :

ds.SearchGaps.Select("SUM(Name)")
DataGrid1.DataSource = ds.SearchGaps
DataGrid1.DataBind()

But i keep getting an error of:
'Filter expression 'Count(Name)' does not evaluate to a Boolean term'

Do I need to put it in a dataview first or something first?

Thanks in advance

 

[cappmgr]

can you change your sql

select nt.name, count(*) as count 
from name_table nt
group by nt.name

this should give you the result set you want.
Marty

 

[IndyGill]

Thanks Cappmgr

Sorry I cant use that approach as I cant make any calls to the db directly. I am working wityh a backend developer who has developed a set of object that give return datasets. So really I need a method to filter the data at my business logic end.

Any ideas

 

[Isadore]

Indy -

This is an excellent question. Using the count property of a datatable, e.g.,

Dim colCt As DataColumn = New DataColumn("smName", GetType(Decimal))
colCt.expression = "Count(Name)"
ds1.Tables("myNames").Columns.Add(colCt)
....

..allows you to get the "row count" for a particular column and add a summation column. It seems (haven't worked with this) that you may need to create yet a second datatable and use the 'merge' property to complete the task. You also may find in the end you'll need to iterate through the first datatable to get the "distinct" names.

At any rate, the solution may be quite simple, as you suggest. I think I'll take a second look at this Indy; it appears you have something fundamental here.

 

[Isadore]

Thanks Indy. Hey, if you get this solved post back, I'm going to work on this tonite a bit and see what I can come up with - would love to see a "quick" answer for this but not so sure. Anyway, if I don't see a post back from you when I get this working I'll post back my solution.

 

[IndyGill]

Hi Isadore, the way I have got round it at the moment is to get Crystal reports to do all the hardwork. However I have noticed that Crystal Reports for ASP.NET and SQL Server has quite a few bugs. But once you get Crystal working it takes out all the hassle of sums, counts and grand totals.

So I guess I have cheated a bit

 

[Isadore]

Indy: Great question. Here I have been avoiding one of the richest areas of dot.NET, i.e., capitilizing on the use and functionality of DataSets, DataTables and DataViews.

DataTables, manipulated in code-behind, are rich and dynamic data objects that allow for a wide spectrum of filtering, sorting, updating, deleting and otherwise maintaing records. These objects prevent excessive hitting of tables, can be sorted, filtered, use to update the database tables, and can be maintained in Cache, Session, etc... In addition, parent-child relationships between tables can be maintained (DataTables are subsets of DataSets, while DataViews are subsets of DataTables).

I am posting one solution, hardly a candidate for a final product, but perhaps reliable. I look forward to a more polished approach but for now this will do.

The initial aggregate function that you tried to execute:

  ds.Tables("myTable").Select("Sum(Name)")

demonstrates one of the many functions of DataTables, q.v., the aggragate functions (also utilizes calculated expressions)

On first glance you might think a DataTable wouldn't have a problem recognizing a contraction of records, but the aggregate functions appear to work best in parent-child relations where the many table is counted, and the one table has a count column added to it.

The approach I took, a far cry from a one-liner, is probably overkill -- yet I cannot see how to contract this, hence the reason I became more interested in the details your question brought up.

Brief of current solution:

* Caputre DataTable with datadapter (required to create a "DataSet")
* Use the fill command (internal connection mgmnt) to populate DataSet
* Name the DataSet and assign Table name (da.fill(ds,"mytable"))
* Create dataview 1 (sorting not necessary)
* Create dataview 2 by copying dataview 1 (LV thread855-854036)
* Loop through dataview 1
* Strip dataview 2 in process of creating unique new DataTable
* Use new unique name table in parent-child count (Option 1)
* Capture name count during stripping of dataview 2 (Option 2 - used)
* Bind

<%@ Page Language="VB"%>
<%@Import Namespace = "Microsoft.VisualBasic"%>
<%@Import Namespace = "System"%>
<%@Import Namespace = "System.Web"%>
<%@Import Namespace = "System.Web.UI.WebControls"%>
<%@Import Namespace = "System.Web.UI.HtmlControls"%>
<%@Import Namespace = "System.Data"%>
<%@Import Namespace = "System.Data.OleDb"%>
<script runat="server">

Dim ds As New DataSet
Dim dt As  New DataTable
Dim i, j As Integer
Dim strName As String
Dim intRowIndex As Integer
Dim dtNew As DataTable
Dim colName As DataColumn
Dim colCount As DataColumn
Dim dRow As DataRow

Sub Page_Load(sender As Object, e As EventArgs)
 If Not IsPostBack Then
  'open database...
  Dim cmdSelect As OLEDbCommand
  Dim dbconn As OleDbConnection = New OleDbConnection( _
   "Provider=Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source=" & Server.MapPath("fpdb\Sites.mdb;"))          
  Dim daName = New OLEDbDataAdapter("SELECT Name FROM tblName", dbconn)
  daName.Fill(ds, "myNames")  <-- note conn management is automatic...
  dt = ds.Tables("myNames")  <-- add datatable myNames to dataset id is dt...
  Dim dv As DataView = ds.Tables("myNames").DefaultView  '<-- create dataview...
  Dim dv2 As DataView = dv.Table.Copy().DefaultView '<-- LV's copy datatable code...

  '2 dataviews are to be used here.  In the first dv, a loop is created
  'while a second dv is having its records sripped while creating yet
  'a third datatable with unique names.

  'after creating a unique table of names, one could create a parent
  'child relationship between the original table storing many names
  'while the newly created table has the name only once....

  'an alternative way, which I employed below, was just to capture
  'the Name count while strippping dataview 2...

  dv.Sort="Name"  '<-- sort dv  sorting was initially required...
  dv2.Sort="Name"  '<-- sort dv 2  and is not needed here...

  'create new DataTable and add two columns, one for Names, the other for Count...
  dtNew = New DataTable("UniqueNames")
  colName = New DataColumn("Name", GetType(String))
  dtNew.Columns.Add(colName)   
  colCount = New DataColumn("decCount", GetType(Decimal))
  dtNew.Columns.Add(colCount) 

  'loop with FIND dataview 2 and strip the names, populating new DataTable 
  'dtNew, which will create table of unique Names...
For i = 0 to dv.Count-1           
 strName = dv(i).row("Name")
 j=0
 'using the dataview Find property...
 Do While dv2.Find(strName) <> -1  '<-- use dv.FIND in Loop...          
 intRowIndex = dv2.Find(strName) '<-- assign index to variable...
 dv2(intRowIndex).delete  '<--- utilizing the delete property of datasets...
 j += 1
 Loop
 If j <> 0 Then 'want 1st occurrence...            
  dRow = dtNew.NewRow()  '<-- add unique name as one row to new table...
  dRow("Name") = strName
  dRow("decCount") = j  '<-- add count from above loop through dv2...
  dtNew.Rows.Add(dRow)  '<-- stick row...
 End If
Next

 'parent-chield routine would fit here (see below)
         
 'bind datagrid...
 With dgName
  .dataSource = dtNew  '<-- bind DataTable directly
  .dataMember = "UniqueNames"  '<-- name of DataTable
  .dataBind()
 End With

 End If
End Sub
</script>
<HTML>
<HEAD>
<TITLE>Test DataSets</TITLE>
</HEAD>
<body>
<form id="Form1" runat="server">
<asp:DataGrid id="dgName" AutogenerateColumns="false" runat="server">
<Columns>
<asp:BoundColumn DataField="Name" HeaderText="Name"/>
<asp:BoundColumn DataField="decCount" HeaderText="Occurrence"/>
</Columns>
</asp:DataGrid>
</form>
</BODY>
</HTML>

...a parent-child approach to this problem, applied above might look like:

'establish the relationships between the new unique table and the original many table...
ds.Relations.Add("tblUnique", ds.Tables("UniqueNames").Columns("Name"), ds.Tables("myNames").Columns("Name"))

'Add the aggregate column...
colCount = New DataColumn("Occurrence", GetType(Decimal))
colCount.Expression =  "Count(Child.Name)"
ds.Tables("UniqueNames").Columns.Add(colCount)

This is a fundamental advantage with DataTables, being able
to maintain and establish parent-child relations -- it is in these relations that one can take most advantage of the "aggragate" functions (although not exclusively).

A few examples since I have them at my fingertips (a few posts on this subject that might help you along).

1. SQL in parent-child tables: qwert321 (thread855-611367)

dsDataMatch.Orders.Compute("Count(recNum)", "(P1 NOT LIKE 'SL' OR P1 Is Null) AND (Child.Grade NOT LIKE 'f*' AND Child.Grade NOT LIKE 's*' AND Child.Grade NOT LIKE 't*')")

which is an excellent example of using a DataSets parent-child relationships in order to carry out an aggragate operation. Note that qwert uses the relations as part of the DataTables filtering SQL. What's the difference between doing a Count using "Compute" and "Select"?

2. Looping through DataTable (calculated values): arpan (thread855-437571)

objDapter=New SQLDataAdapter("SELECT ID, Name",objConn)
objDS=new DataSet()
objDapter.Fill(objDS,"PFRegister")
dTable=objDS.Tables("PFRegister")

For Each dRow In dTable.Rows
i=i+CType(dRow("Basic"),Double)
j=j+CType(dRow("PF"),Double)
Next

3. Filtering - using Import, databinding dataview: faithfull (thread855-682341)

Filter = " intEmployeeID > 0 "
Sort = " intEmployeeID DESC"
tempdt = CType(Session("Results"), DataTable)
drs = tempdt.Select(Filter, Sort)
filterdt = New DataTable()
Dim row As DataRow
For Each row In drs
  filterdt.ImportRow(row)
Next
Dim dv As DataView = filterdt.DefaultView
dgSearch.DataSource = dv
dgSearch.DataBind()

So, Indy, I think there is a message here, at least an academic one, and that is to use more of the DataSets, DataTables and DataViews to better manage server resources and make the site more scalable. There is also I am sure an efficiency gain as well. I guess I never realized how critical this area really is (I think it plays a central role in shopping cart design).

Thanks, if you can contract the above, have at it....!

Oh, and one final note.

A thread posted by SJG0526 (Programmer) (thread855-810671) posed an interesting question regarding DataViews and DataSets. See if you can answer his question -- to date nobody has.

 

[Isadore]

Indy -

One final worthwhile example. Using a DataSet to do simple binding of textboxes, etc...

checkai (thread855-852034)

lblMe.Text = CStr(DataBinder.Eval(datasetName, "Tables[TableName].defaultView.[0].FIELDNAME"))

..worth adding here...