DataViews

[Alaric]

Hi,

 

I have a dataset with one datatable with 2 columns, 'R_ID' and 'Miles'.

 

I want to create a dataview based on the datatable.

 

The SQL for the view I want is:

 

SELECT R_ID

FROM datatable

GROUP BY R_ID

HAVING count > 1

 

can this be done or can dataviews only be created with trivial criteria? and if so is there anyway I can do this? (I can't run this query against the data provider).

 

Cheers,

 

A.

[Robby]

You can use this against SQL Server or Access (I think)...

 

SELECT  R_ID, some_other_columns
FROM some_table
WHERE R_ID In (SELECT R_ID FROM some_table As T GROUP BY R_ID HAVING Count(*)>1 )

'If you want only ones that have no duplicates then change the  > 1 to = 1

[spas]

In the context of the question, you say that you cannot go back to the Data Provider to get the data; therefore, the only way to accomplish this is to create a DataSet that implements multiple related tables.

 

Something that looks like this:

 

Table["IDS"].Columns["R_ID"]

Tablle["IDCOUNTS"].Columns["R_ID"]

Table["IDCOUNTS"].Columns["Miles"]

A DataRelation between IDS and IDCOUNTS on R_ID

 

Finally, you then can create an Aggregation that counts the R_ID values.

 

 

The relevant documentation in VS.NET is

help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfSystemDataDataColumnClassExpressionTopic.htm

 

Or Search for DataView, Sellect 'DataView Members', select 'RowFilter', select 'Expression' hyperlink.

 

Unfortunately, the constraint of not being able to access the Data Provider, turns what would be a simple select statement in a DataAdapter into a nightmare...

 

Hope this helps, Scott

[jfackler]

You can do a select on your dataset. The select returns an array

of datarows which can then be added to a table after comparing

each rows ID value to all those in the table.

 

The following is untested but gives you the idea.

Dim arR_ID As DataRow() ' datarow array
Dim dr as DataRow 'datarow
Dim dr1 as DataRow 'datarow
Dim ID as String
Dim dt As DataTable


arR_ID = Me.dataset1.Tables(0).Select

For each dr in arR_ID
ID = Cstr(dr("R_ID")
For each dr1 in arR_ID
 If Cstr(dr1("ID")) = ID Then
    dt.Rows.Add(dr)
    dt.Rows.Add(dr1)
 End If
Next
Next

 

My interpretation of your question was that you want duplicates

grouped together by ID.

 

Jon