"Selecting" from a data table

[Ron L]

I have a data table that lists a series of items in my database. In my user
form, I want the user to be able to filter by a number of criteria (e.g.
location, contract, date modified, etc). Other than modified date, all my
filters are selected via combo boxes. I would like to have the combo boxes
update so that if there are no items available in the currently filtered
list for a given selection, that selection will not appear in the list. I
also want to make it so that the filtering and updating of the filter lists
is done on the client, without needing to go back to the server. The
functionality I am looking for is something like this:

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

Is there some way to implement this functionality?

TIA
Ron L

 

[Ross Presser]

I have a data table that lists a series of items in my database. In my user
form, I want the user to be able to filter by a number of criteria (e.g.
location, contract, date modified, etc). Other than modified date, all my
filters are selected via combo boxes. I would like to have the combo boxes
update so that if there are no items available in the currently filtered
list for a given selection, that selection will not appear in the list. I
also want to make it so that the filtering and updating of the filter lists
is done on the client, without needing to go back to the server. The
functionality I am looking for is something like this:

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

Is there some way to implement this functionality?

TIA
Ron L

Read up on DataViews.

 

[Mike Labosh]

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

To filter a series of stuff in a DataTable, you can do this:

dt.DefaultView.RowFilter = "any where clause without the word where"

However, I think your problem is different. I think you need 3 DataTables:

1. the records for location
2. the records for contract
3. the records for your user to interact with on your form.

Make a stored procedure in your database like this:

CREATE PROCEDURE dbo.GetMyStuff (
@param INT -- or whatever, if you need parameters
) AS
SELECT DISTINCT stuff for location FROM wherever
SELECT DISTINCT stuff for contract FROM wherever
SELECT stuff for your form FROM wherever WHERE whatever
GO

Then you set up a SqlDataAdapter to call your stored procedure and you use a
DataSet instead of a DataTable:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
....
' [Jedi] you're storing the connect string in the .config file, aren't you.
Dim cnstr As String = _
ConfigurationSettings.AppSettings("connectionString")

Dim cn As New SqlConnection(cnstr)
Dim ds As New DataSet()
Dim da As New SqlDataAdapter("dbo.GetMyStuff", cn)

da.Fill(ds)

' Now your DataSet has three DataTables in it:
' ds.Tables(0)... ds.Tables(1)... ds.Tables(2)
' Which one is which is controlled by the order of the select
' statements inside the stored procedure:
' ds.Tables(0) is your location stuff
' ds.Tables(1) is your contract stuff
' ds.Tables(2) is your stuff for other bound things on your form

' If you need the three DataTables to have structured relationships
' inside the DataSet, you can create a DataRelation object that points
' to the parent column in one DataTable and the child column in the other.

' So now your list bindings are nicer:
location.DataSource = ds.Tables(0)
contract.DataSource = ds.Tables(1)

' etc...

For your filtering needs, each of those three DataTables has a DefaultView
property which represents a DataView object. That property has a RowFilter
property.

For more info, press CTRL+ALT+J and search the object browser for DataView
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

 

[Ron L]

Mike

Thanks for your response. The problem with that approach is that as I apply
one filter (say location), all of the records with contract of "Big Client"
may have been filtered out. In that case I want to be able to update the
Contract combo box to no longer include "Big Client", and I want to do it
without having to make a round trip to the server. Sorry if I wasn't clear
on this in the original post.

Ron L

dt is the data table of items.

location.datasource = SELECT DISTINCT location FROM dt
contract.datasource = SELECT DISTINCT contract FROM dt

To filter a series of stuff in a DataTable, you can do this:

dt.DefaultView.RowFilter = "any where clause without the word where"

However, I think your problem is different. I think you need 3
DataTables:

1. the records for location
2. the records for contract
3. the records for your user to interact with on your form.

Make a stored procedure in your database like this:

CREATE PROCEDURE dbo.GetMyStuff (
@param INT -- or whatever, if you need parameters
) AS
SELECT DISTINCT stuff for location FROM wherever
SELECT DISTINCT stuff for contract FROM wherever
SELECT stuff for your form FROM wherever WHERE whatever
GO

Then you set up a SqlDataAdapter to call your stored procedure and you use
a DataSet instead of a DataTable:

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
...
' [Jedi] you're storing the connect string in the .config file, aren't
you.
Dim cnstr As String = _
ConfigurationSettings.AppSettings("connectionString")

Dim cn As New SqlConnection(cnstr)
Dim ds As New DataSet()
Dim da As New SqlDataAdapter("dbo.GetMyStuff", cn)

da.Fill(ds)

' Now your DataSet has three DataTables in it:
' ds.Tables(0)... ds.Tables(1)... ds.Tables(2)
' Which one is which is controlled by the order of the select
' statements inside the stored procedure:
' ds.Tables(0) is your location stuff
' ds.Tables(1) is your contract stuff
' ds.Tables(2) is your stuff for other bound things on your form

' If you need the three DataTables to have structured relationships
' inside the DataSet, you can create a DataRelation object that points
' to the parent column in one DataTable and the child column in the other.

' So now your list bindings are nicer:
location.DataSource = ds.Tables(0)
contract.DataSource = ds.Tables(1)

' etc...

For your filtering needs, each of those three DataTables has a DefaultView
property which represents a DataView object. That property has a
RowFilter property.

For more info, press CTRL+ALT+J and search the object browser for DataView
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

 

[Ross Presser]

Thanks for your response. The problem with that approach is that as I apply
one filter (say location), all of the records with contract of "Big Client"
may have been filtered out. In that case I want to be able to update the
Contract combo box to no longer include "Big Client", and I want to do it
without having to make a round trip to the server. Sorry if I wasn't clear
on this in the original post.

I don't think there is a simple declarative way to do it without involving
a SQL server. However it would be straightforward to do it in a Sub:

dt.defaultview.RowFilter = "location = 'Northwest'"
Call Refill(comboContract,dt,"Contract")
Call Refill(comboProjectLeader,dt,"ProjectLeader")

Sub Refill(cbx as ComboBox, dt as datatable, col as string)
cbx.Items.clear
dim r as DataRow
for each r in dt.defaultview.Rows
dim s as string = r.Item(col)
if not cbx.Items.Contains(s) then
cbx.Items.Add(s)
end if
next
End Sub

 

[Cor Ligthert]

Ron,

I don't understand what you with "distinct" means in your message and reply,
your text is in my opinion in contradiction to that.

Can you describe that a little bit more.

On our site is a distinct solution, however I am not sure if is what you
ask.
http://www.windowsformsdatagridhelp.info/default.aspx?ID=dcad9a66-1366-4d61-8d32-1a580eb893b2

I hope this helps,

Cor


..

 

[Ron L]

Cor

DISTINCT is a SQL keyword which essentially says if there are multiple of
this item, only give me one. E.G. for the data:
Location Contract Item
Rm105 001a MS Word
Rm105 001a MS Excel
Rm105 002a Lotus Notes
Rm207 001a MS Word
Rm207 003b MQ Series

SELECT DISTINCT Location would return:
Rm105
Rm207

and SELECT DISTINCT Contract would return:
001a
002a
003b

Ron L

 

[Ron L]

Ross

Thank you for your responses. I will take a look at the dataviews.

Ron L

 

[Cor Ligthert]

Ron,

I know what "Distinct" is otherwise I could not make that sample that is on
our pages.

Did you see that?

That does probably what you ask.

Cor

 

[Ron L]

Cor

My Apologies, I am afraid I misunderstood what you were saying in your
response ("I don't understand what you with "distinct" means in your
message... ") and responded to that. I am taking a look at your link now.

Essentially, what I am trying to do is allow the user to first filter by
location and then by contract, but when the location filter is applied I
only want the contracts that are applicable to the now viewable locations.
To use my previous example, if the location filter was set to Rm105, the
contract list should be 001a and 002a, but if the location filter was set to
"all" then the contract filter should list 001a, 002a, and 003b.

I hope this is a more clear explanation.

Thanks,
Ron L

 

[Cor Ligthert]

Ron,

I did not check it, however you can probably as well set your filter in that
sample.

\\\
Dim dtclone As DataTable = dt.Clone
Dim dv As New DataView(dt)
dv.Sort = DistinctColumn
dv.Rowfilter = "State = 'USA'" 'This is the addition and than of course with
a variable
Dim SelOld As String
///

I did not test it, however it would show normally only Ken.

I hope this helps.

Cor

 

[Ron L]

Cor

Thanks for the link, that put me on the right track. I have included my
final version below.

Ron L


' This version, by taking a dataview, allows dtclone to be populated
from filtered data
' so that cascading filters may be loaded.
Protected Function Distinct(ByVal dv As DataView, ByVal DistinctColumn
As String, _
ByVal ValueColumn As String, Optional ByVal dtclone As DataTable
= Nothing) As DataTable
Dim SelOld As String

If dtclone Is Nothing Then
' Create a new datatable consisting of only the DistinctColumn
and ValueColumn
' columns from the original datatable
dtclone = New DataTable
dtclone.Columns.Add(New DataColumn(DistinctColumn,
dv.Table.Columns _
(DistinctColumn).DataType))
If ValueColumn.Trim <> "" Then
dtclone.Columns.Add(New DataColumn(ValueColumn,
dv.Table.Columns(ValueColumn).DataType))
End If
Else
dtclone.Clear()
End If

dv.Sort = DistinctColumn
SelOld = ""
' loop through the datatable to find each time the value of
DistinctColumn
' changes. When that happens, add the source row to the destination
datatable
For i As Integer = 0 To dv.Count - 1
If SelOld <> dv(i)(DistinctColumn).ToString Then
Dim drn As DataRow = dtclone.NewRow
Try
drn(DistinctColumn) = dv(i)(DistinctColumn)
If ValueColumn.Trim <> "" Then
drn(ValueColumn) = dv(i)(ValueColumn)
End If
Catch ex As Exception
Throw New Exception("Error occurred attempting to copy
the source table's row data to the _
destination table.", ex)
End Try
SelOld = dv(i)(DistinctColumn).ToString
dtclone.Rows.Add(drn)
End If
Next
Return dtclone
End Function