Querying A Datatable

[Arg81]

Did a quick search but I don't think my search keywords were correct...got back quite a lot of irrelevant posts

OK, I fill a dataSet from a dataAdapter. (dataAdapter connects to MS SQL 2000)

...Once I have data in the table in the dataSet, can it be queried??? I.E. I load all my employees from our Employee table in SQL into my dataSet. I want one form with a combobox set to display the employees that are still employeed (Where Employed=1) and another form with a combobox set to display those who have left (Where Employed=0)

The reason I ask this is for historial reasons. I load all the employee data in the app on the main form (then shared global). When someone leaves they get marked in the database. But I need a form that can load all the records for a previous employee.....

I assume the other way is to create another dataTable in the dataSet and change the forms to point to the correct table?????

Help / Advice / Slapping welcomed warmly

 

[vis781]

V-B_New-B you are much more likely to get a response if to title your post appropriately. I've changed to that end. In the future if you could try to title your posts in a way that describes your query.

 

[Arg81]

I titled my post Querying A Datatable .....?


EDIT : OK I see what it did now...I realised I'd left the title as what I thought was the subtitle...I did Edit the title in "Go Advanced" but obviously this doesn't change the main post title as well. Sorry, I use so many forums everyday that Title means a different thing on each forum...

 

[cjard]

A few points of note:

We dont fill DataSets with data, we fill DataTables with data and DataTables themselves live inside DataSets.

DO you really intend to download your whole database into the client, work with it and put it back? Why bother having a database in this case? Just serialize your dataset to disk.

Yes you can query a DataTable (not DataSet, remember that DS dont contain data) using the .Select method. The input parameter is a string that bears resenblance to an SQL WHERE clause(s)

 

[Arg81]

"We dont fill DataSets with data" - I know, I was typing quickly and was meant to put DataTable...

Now you have really confused me. I thought the whole idea of ADO and dataAdapters / dataTables / dataSets was to take a copy of the database and work with it offline, then update any changes back to the database?????

I am not pulling in the whole database. A lot of my forms query the database and pull back the results.

What I am attempting to do here is load all of my employees into a DataTable. There are about 50 employee rows in the database at the moment. One of the columns is "Employed" where 0 = no and 1 = yes.

Once the data is in the dataTable, I want one form to be able to load the employees who ARE employed, and another to load the employees who AREN'T employed.
I was hoping I could do this with one set of data, instead of having a DataAdapter on one form making a connection and getting one set of results, and another DataAdapter on the 2nd form making a connection and getting a different set of results.

 

[cjard]

Now you have really confused me. I thought the whole idea of ADO and dataAdapters / dataTables / dataSets was to take a copy of the database and work with it offline, then update any changes back to the database?????

Correct, but you only take the data you need. Your post implied you were transferring the entire database into the client.. Hence my use of the word "whole" in my response

I am not pulling in the whole database.

I guess I should further extend my explanation to the notion that we dont often load whole tables either!

Once the data is in the dataTable, I want one form to be able to load the employees who ARE employed, and another to load the employees who AREN'T employed.
I was hoping I could do this with one set of data, instead of having a DataAdapter on one form making a connection and getting one set of results, and another DataAdapter on the 2nd form making a connection and getting a different set of results.

You may consider if more sensible to create a helper class that has just one data adapter, housing a parameterized query:

SELECT * FROM Employees WHERE Employed = ____

Replace ___ with whatever parameter semantic your database requires. OLE uses ? marks, Oracle uses ARAMETER_NAME and SQL Server uses @PARAMETER_NAME.

Create a method that takes 2 parameters:

FillByEmployed(dtToFill as DataTable, employedState as Integer)


-

In each of your forms, make a datatable, and pass it to the method:

Dim dtNonEmp as DataTable
DatabaseModule.FillByEmployed(dtNonEmp, 0)


-

 

[Arg81]

OK maybe an easier approach...

Is there a way to filter a combobox? I.e. it loads all of the data but then I can apply a filter to it to display what I want?

 

[Arg81]

lol, I love it when you simplify a question, then figure the answer out!!!

I've changed my dataAdapter to load all "Account Managers" (all = no filter on Employed)

On my form, i then have changed the code so that;

[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cbALL_AM.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboAccountManager
.DataSource = DataSetEmployees.Tables("AccountManager")
.DisplayMember = "FullName"
.ValueMember = "EmployeeID"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cbALL_AM.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboAccountManager
.DataSource = DataSetEmployees.Tables("AccountManager").Select("Employed=1")
.DisplayMember = "FullName"
.ValueMember = "EmployeeID"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

Therefore if the "ex staff" checkbox is ticked, it loads all the data from the dataTable "AccountManager", and if it isn't checked, it filters it to only those who are currently employed using:
.Select("Employed=1")

Thank god for an easy solution!!!!!