473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

"Selecting" from a data table

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
Nov 21 '05 #1
11 3417
> 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("connectionStrin g")

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"
Nov 21 '05 #2
On Wed, 29 Jun 2005 14:43:36 -0400, Ron L wrote:
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.
Nov 21 '05 #3
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
"Mike Labosh" <ml*****@hotmail.com> wrote in message
news:%2***************@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("connectionStrin g")

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"

Nov 21 '05 #4
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
Nov 21 '05 #5
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....2-1a580eb893b2

I hope this helps,

Cor
..
"Ron L" <ro**@bogus.Address.com> schreef in bericht
news:uX**************@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

Nov 21 '05 #6
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

"Cor Ligthert" <no************@planet.nl> wrote in message
news:eQ*************@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....2-1a580eb893b2

I hope this helps,

Cor
.
"Ron L" <ro**@bogus.Address.com> schreef in bericht
news:uX**************@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


Nov 21 '05 #7
Ross

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

Ron L
"Ross Presser" <rp******@NOSPAMgmail.com.invalid> wrote in message
news:cx****************************@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

Nov 21 '05 #8
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
Nov 21 '05 #9
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" <no************@planet.nl> wrote in message
news:%2****************@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

Nov 21 '05 #10
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
Nov 21 '05 #11
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

"Ron L" <ro**@bogus.Address.com> wrote in message
news:OQ**************@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" <no************@planet.nl> wrote in message
news:%2****************@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


Nov 21 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Christian Fowler | last post by:
I have a VERY LARGE pile of geographic data that I am importing into a database (db of choice is postgres, though may hop to oracle if necessary). The data is strictly hierarchical - each node has...
3
by: tobhamlet | last post by:
If one is using a query as a Row Source on a Form, is there a way to set up a blank space, or to insert the word "ALL" in a combo box that represents ALL combo box row items. The scenario is as...
0
by: Bilo | last post by:
Hi, I only see the Icon of my app when i rightclick and select "open with" in explorer. How can I have the name of the app also seen?
13
by: andro | last post by:
Hi everybody! I have several tables from which I want to exract the SAME value (along with other referenced data). All the values are in the same column within the tables. How can I achieve...
2
by: Hexman | last post by:
Hello All, In SS EE I have nulls in a column. I want to select and eventually change to a zero (its a smallint column). I've tried selecting 'null', 'dbnull', etc. Then I read about the ISNULL...
33
by: Snis Pilbor | last post by:
With the "as if" rule in play, doesn't that effectively render the "register" keyword completely useless? Example: I make a silly compiler which creates code that goes out of its way to take a...
3
by: eskelies | last post by:
Good day all...I have a combo box that selects numbers (ie. 1,2,3, etc.). What I am looking for is the combo box to have an "ALL" selection. Right now this is what I have for my SQL code: SELECT...
1
by: Cirene | last post by:
I have a gridview that displays a listing of cars for sale. It's based on a "Cars" table. (The "Cars" table has a 1 to many relationship to "CarPictures" table.) On each gridview row (ie for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.