"Selecting" from a data table

29 Jun 2005 6:43 PM

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

29 Jun 2005 7:33 PM

Ross Presser

On Wed, 29 Jun 2005 14:43:36 -0400, Ron L wrote:

Show quoteHide quote

> 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.

29 Jun 2005 7:38 PM

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"

29 Jun 2005 7:49 PM

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

Show quoteHide quote

"Mike Labosh" <mlab***@hotmail.com> wrote in message
news:%230K$sIOfFHA.576@TK2MSFTNGP15.phx.gbl...
>> 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"
>

29 Jun 2005 9:29 PM

Ross Presser

On Wed, 29 Jun 2005 15:49:32 -0400, Ron L wrote:

> 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

30 Jun 2005 10:54 AM

Ron L

Ross

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

Ron L

Show quoteHide quote

"Ross Presser" <rpresser@NOSPAMgmail.com.invalid> wrote in message
news:cxiw675ihyps$.6x6ikf72difa.dlg@40tude.net...
> On Wed, 29 Jun 2005 15:49:32 -0400, Ron L wrote:
>
>> 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

30 Jun 2005 7:24 AM

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

..

Show quoteHide quote

"Ron L" <r***@bogus.Address.com> schreef in bericht
news:uXiS4pNfFHA.3460@TK2MSFTNGP10.phx.gbl...
>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
>
>

30 Jun 2005 10:53 AM

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

Show quoteHide quote

"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:eQZ6oTUfFHA.352@TK2MSFTNGP09.phx.gbl...
> 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" <r***@bogus.Address.com> schreef in bericht
> news:uXiS4pNfFHA.3460@TK2MSFTNGP10.phx.gbl...
>>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
>>
>>
>
>

30 Jun 2005 11:01 AM

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

30 Jun 2005 11:41 AM

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

Show quoteHide quote

"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:%23FcB6MWfFHA.1148@TK2MSFTNGP12.phx.gbl...
> 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
>

30 Jun 2005 12:56 PM

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

30 Jun 2005 7:27 PM

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

Show quoteHide quote

"Ron L" <r***@bogus.Address.com> wrote in message
news:OQizXiWfFHA.1472@TK2MSFTNGP12.phx.gbl...
> 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" <notmyfirstn***@planet.nl> wrote in message
> news:%23FcB6MWfFHA.1148@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>
>