DataSet Filtering into new table

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
So I have the following:
oracle_da = New System.Data.Odbc.OdbcDataAdapter(strSQL, oracle_conn2)
oracle_da.Fill(oracle_ds, "All_Empl")

If I understand this correctly, it fills the table All_Empl in data set oracle_ds with the results from the sql statement. This so far has worked like I wanted. My question is how to filter this

I found that I can do this:
oracle_ds.Tables("All_Empl").Select("TYPE='MGMT'")

I would like to take those results and put them into a new table in dataset oracle_ds, but dont know how. I want to put the results of the filtered table into oracle_ds.Tables("Mgr_Lvl1").

What is the easiest way of doing this?
 
Firstly, I would strongly suggest that, rather than using Odbc, you download ODP.NET from the Oracle web site and use their dedicated ADO.NET provider. The code pattern will remain the same but you would change all your types, e.g. OracleConnection instead of OdbcConnection, and your connection string would change too. Appropriate connection strings can be found at www.connectionstrings.com.

As for the question, there probably shouldn't be a need to create a new DataTable. You can use a DataView to provide a sorted and/or filtered view of the data in a DataTable without affecting the DataTable itself. Every DataTable is already associated with a DataView via its DefaultView property. When you bind a DataTable to, for instance, a DataGridView, it is the contents of the DefaultView that you see, which explains how you can sort a grid bound to a DataTable. If you need more than that single view then you can simply create your own DataView, passing the source DataTable as an argument to the constructor.

Once you have a DataView, regardless of how you came by it, you can set its RowFilter property to include rows by criteria. If you then loop through the DataView you will get a DataRowView for each DataRow that satisfies the criteria. If you really do need a new DataTable then you can call ToTable on the DataView to create a DataTable that matches the sorting and filtering you applied. You can Add that to the Tables collection of the DataSet if desired.
 
Ok, first I did change my oracle connection like mentioned above.

Next, based on your suggestions, I did the dataview and got the filtering. I assigned the dataview to the datasource of a combo box. It looks like it works. Next questions, how do I make it so there is no selection in the combobox until one is chosen from the list. Basically put a blank row at the top of the list.

Also, when I am loading the data into the different combo boxes, and i assign a blank row, it always goes to my code for selectedindexchanged. Maybe that isnt what I want. I want the code to run when a user selects an item from the list, not when I am loading the data. I have three combo boxes (Manager, Supervisor, Employee). During my load, i pull all the employee information, then i create a dataview of all the employees and filter on managers. I assign this to the combobox (still need to make the default blank). The Supervisor Combo, I leave blank and the employee combo i assign to the datatable of all the employees. A user can either select an employee from the employee combo to get what they want or select a manager, which then loads the supervisor combo (a new dataview - still need to make default blank). they then select a supervisor and it filters the employee table down to just those employees that report to the supervisor. I hope that makes sense. I can't seem to get the events right so my code is jumping around all over the place. If i had the old school click event that would solve it.

Any help would be appreciated. Thanks
 
Don't put a blank row at the top. If you want no selection then make no selection, not a selection of a dummy item. What indicates the selection in a ComboBox? The SelectedIndex and SelectedItem, right? If there's no selection made in the ComboBox, what are the values of those properties? Nothing and -1, right? So if you want to clear the selection, what would you do? You'd set the SelectedItem to Nothing or the SelectedIndex to -1.

If you want to be notified when the user changes the selection, rather than whenever the selection changes, you handle the SelectionChangeCommitted event rather than SelectedindexChanged.
 
Ok i think that is working. Thank you. So when i select an item from the combobox i have this code:
HierEmp = Me.MHier.SelectedValue.ToString - THis is working, i get the value I want.
mstrwho = Me.MHier.??? - this i want the text or displaymember. Ive tried several of the selected items, I am not getting the value i want.
 
I ended up using DirectCast(Me.THier.SelectedItem, System.Data.DataRowView).Row.ItemArray(0), is there an easier way?

Also I have
With Me.lblAuthDays
.DataBindings.Add("Text", odp_ds.Tables("Emp_LHData"), "auth", True)
.DataBindings(0).FormatString = "0.0"
End With
I need to divide this value by 8 and show it in the text box. How do I do that?
 
Using the text property does not work, it doesnt give me a value, just blank. Same with Selected text

Private Sub MHier_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MHier.SelectionChangeCommitted
HierEmp = Me.MHier.SelectedValue.ToString
mstrwho = DirectCast(Me.MHier.SelectedItem, System.Data.DataRowView).Row.ItemArray(0) -- this works
mstrwho = Me.MHier.Text -- this doesnt
Call LoadTHier()
End Sub
 
Question:
So i have three comboboxes on my form, each has a different datasource (different dataviews). I then have txt boxes that fill in with data based on the selection of one of the combo boxes. So when that value changes, the data is the text boxes change. How do I do databinding to that specific combobox.

I have the following to set the datasource of the combobox. empl_dv is just a dataview
VB.NET:
             empl_dv = New DataView(odp_ds.Tables("All_Empl"), "empl_empl_id='" & HierEmp & "'", "Employee", DataViewRowState.CurrentRows)
            Me.EHier.DataSource = empl_dv
            Me.EHier.DisplayMember = "employee"
            Me.EHier.ValueMember = "empl_id"
            Me.EHier.SelectedIndex = -1

then i have
Me.lblEmpNo.DataBindings.Add("Text", odp_ds.Tables("Emp_LHData"), "empl_id") in a different sub. how do i relate this to the combobox above.

As you can see the data comes from different tables, but they have a common value in empl_id. I am totally doing something wrong. Please help
 
You could create a new DataTable but the simplest option is to perform the join on the database and have your query(s) return the combined data. You'd only not do that if doing so meant retrieving a large amount of duplicate data, in which case the extra complexity of joining the data in the application is justified by the added efficiency of retrieving significantly less data.
 
ok, i will give that a try tomorrow. Thank you for all your help.

I have one more easy question, I think. When typing in a combobox, i have the autocompletemode set to append. So as i am typing, it starts filtering my list. when i see a value i want, if i click on it or hit enter, the selectionchangecommitted isnt triggered. What am i missing?
 
just one thing to point out: you seem to be slightly confusing a dataset and a database; they are not the same and a dataset is not supposed to behave as a database. if you need a datatable with just TYPE='mgmt' then actually make its query WHERE TYPE = :type
and then call the tableadapter fill and pass the MGMT in as a parameter:

Dim mgmtDT as New MyDataSet.TypeDataTable
myTypeTableAdapter.Fill(mgmtDT, "MGMT"

Now you have all your MGMT rows in the table
 
Back
Top