Using Combocox Selection to select/update bound items

[njb35]

Hi all

I'm beginning my foray from VBA into VB 2005 Express, and enjoying
some of the efficiencies it provides! I'm stuck with some dataset
handling however that I _think_ can be automated but I can probably
code what I want to do the hard way. I've searching around online but
can't find an answer to this specific question.

Here's the situation: I have a dataset table with 3 fields: one
indexed as a primary key and the other two not. The indexed field is
bound to a combo box. When the program loads, the dataset data and
schema are loaded from disk and the combobox is automatically (!)
filled with the appropriate items because it's bound to that dataset.
Yay! The two other fields are bound to a numericUpDown and a TextBox
respectively and are also filled with their correct data from the
first entry of the dataset.

I also have a BindingNavigator in the mix. When I use the Navigator
within the running program to move between records, the ComboBox,
NumericUpDown and the TextBox all update correctly with their
appropriate data. So far it looks great - I'm very impressed with
everything and my coding effort has been almost zero.

What I want to do is select an entry using the combobox - this is far
more intuitive for the user than searching using the arrow buttons in
the BindingNavigator. However, when I use the combobox to select an
entry from the dropdown I get a ConstraintException because the
system thinks I'm trying to enter a NEW entry into the dataset using a
duplicate name in the primary key column of the table.

Is there a simple way to use a pre-filled and bound combobox to
_select_ an existing row of data from a dataset and have the other
bound items all update to the same row rather than have the program
think I'm trying to _enter_ a new row of data? I've seen some
examples talk about using a second table linked to the combobox but I
can't see how that will avoid the problem of the program thinking it's
a new field and not a selection of an existing one.

Thanks for any advice or help

Bennett

 

[RobinS]

Yes, use a combobox that is not bound to the rest of the data, and respond
to the SelectIndexChanged event. If you have a combobox bound along with a
bunch of other data, it will assume it is data you are storing, not data
you are selecting.

You could try responding to the SelectedIndexChanged event on your
combobox, but I don't think it will work.

When you respond to the event, you will need to move to the right record. I
would use a BindingSource to bind my data, and then you can do a Find on
the BindingSource and advance it to the right record.

Robin S.

 

[Bennett]

Yes, use a combobox that is not bound to the rest of the data, and respond
to the SelectIndexChanged event. If you have a combobox bound along with a
bunch of other data, it will assume it is data you are storing, not data
you are selecting.

Many thanks ) I'll give it a go.

You could try responding to the SelectedIndexChanged event on your
combobox, but I don't think it will work.

The error occurs before the program gets to do anything - I can try
trapping it I suppose but that's a shoddy way to program!

When you respond to the event, you will need to move to the right record. I
would use a BindingSource to bind my data, and then you can do a Find on
the BindingSource and advance it to the right record.

I'm also experimenting with using non-bound combo/list boxes and
'Fill'-ing them with items from the Dataset, then using the
SelectedIndex value to reference the correct row/item for
manipulation. It's less intuitive but at least I can tell _exactly_
where the data is coming from and going to and how.

e.g.

TextBoxMainDescription.Text =
experimentDV.Table.Rows(ListBoxExperiments.SelectedIndex).Item(1).ToString

Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperiments.SelectedIndex) into a textbox for
display so the user gets a description of the experiment they have
selected, rather than an abbreviated title or code.

I get the impression that DataView is the only way I can reasonably
search for data...? The 'Find' method doesn't exist for DataTables it
seems, so you have to shift stuff into a DataView object. What a
pain... What was/is the rationale behind that? Is it just that
DataSets can hold multiple tables but DataViews cannot?

Cheers

Bennett

 

[RobinS]

Many thanks ) I'll give it a go.


The error occurs before the program gets to do anything - I can try
trapping it I suppose but that's a shoddy way to program!

***It's raising the SelectedIndexChanged event as it does the data binding.

I'm also experimenting with using non-bound combo/list boxes and
'Fill'-ing them with items from the Dataset, then using the
SelectedIndex value to reference the correct row/item for
manipulation. It's less intuitive but at least I can tell _exactly_
where the data is coming from and going to and how.

e.g.

TextBoxMainDescription.Text =
experimentDV.Table.Rows(ListBoxExperiments.SelectedIndex).Item(1).ToString

Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperiments.SelectedIndex) into a textbox for
display so the user gets a description of the experiment they have
selected, rather than an abbreviated title or code.

Ugh. That's a lot of trouble and a royal pain in the a**. (I know, tell you
how I *really* feel. ;-)

I get the impression that DataView is the only way I can reasonably
search for data...? The 'Find' method doesn't exist for DataTables it
seems, so you have to shift stuff into a DataView object. What a
pain... What was/is the rationale behind that? Is it just that
DataSets can hold multiple tables but DataViews cannot?

That's not true. Here's an example. I think it might only work on pk
columns, but I'm too tired to look it up.

Dim rw as DataRow = dt.Rows.Find("NEWCO")
If rw Is Nothing Then
'Customer not found
Else
'Customer found
End If

Here's an example that looks based on two columns that make up the pk.
dt.PrimaryKey = New DataColumn() {dt.Columns("OrderID"),
dt.Columns("ProductID")}
Dim objCriteria As New Object() {10643, 28}
Dim row As DataRow = dt.Rows.Find(objCriteria)

Frankly, I use the BindingSource. It's like the glue between the datatable
and the controls, and exposes a lot of methods and properties you can use.

Putting a combobox on the top and using that is no different than the
implementation behind the record# in the binding navigator.

Bind your data using a binding source.
Dim myBindingSource as BindingSource = New BindingSource()
myBindingSource.DataSource = myDataTable 'or myDataSet.Tables(0) or
whatever
'bind all of your controls to the BindingSource, not the DataTable
m_Combo.DataSource = myBindingSource
m_Combo.DisplayMember = "CompanyName"
m_Combo.ValueMember = "CustomerID"
m_CustomerTextBox.DataBindings.Add("Text", myBindingSource, "CustomerID",
True)
(etc).

Then put a combobox for navigation on the top of the form. (Can you add it
to the Binding Navigator?) Use a *separate* BindingSource for it, and bind
it to whatever you want to -- a master table is usually best, if you have
one.

In the SelectedIndexChanged event, you can do something like this:

Dim index as integer = _
myBindingSource.Find("FieldnameToSearch", myNavigationComboBox.Text)
If index <> -1 then 'it was found; move to that position
myBindingSource.Position = index
Else
MessageBox.Show("Did not find your selection. My code is messed
up.") 'haha
End If

You may want to add a private variable to your form called something like
m_Loading, set to False as a default. At the end of Form_Load, set it to
true.

In the SelectedIndexChanged event, check the m_Loading, and if it's true,
don't do the Find. This way, as it does the data bindings and whacks the
selectedindexchanged event, it won't react until you are ready for it to.

Robin S.

 

[Bennett]

Ugh. That's a lot of trouble and a royal pain in the a**. (I know, tell you
how I *really* feel. ;-)

I dunno - it's one line of code that does exactly what I want. I
can't get my head around the BindingSource thing, as I can't see a way
yet to easily access what it's looking for. I know it's supposed to
be doing everything behind the scenes but clearly it isn't doing what
I want it to, and I can't access the code inside the BindingSource
"black box" to find out why (hence the posting here!) ) at least I
can't if I drag and drop in the VB express editor.

It would be easier to have a way to look at its variables, but if I'm
writing code to do that I may as well write code to look at the
variable that says which item in a combobox has been selected :-/

Bind your data using a binding source.
Dim myBindingSource as BindingSource = New BindingSource()
myBindingSource.DataSource = myDataTable 'or myDataSet.Tables(0) or
whatever
'bind all of your controls to the BindingSource, not the DataTable
m_Combo.DataSource = myBindingSource
m_Combo.DisplayMember = "CompanyName"
m_Combo.ValueMember = "CustomerID"
m_CustomerTextBox.DataBindings.Add("Text", myBindingSource, "CustomerID",
True)
(etc).

Then put a combobox for navigation on the top of the form. (Can you add it
to the Binding Navigator?) Use a *separate* BindingSource for it, and bind
it to whatever you want to -- a master table is usually best, if you have
one.

Now THAT seems like a royal pain in the ass ;-) Keeping track of
multiple bindingsources to do one job, creating a master table?

In the SelectedIndexChanged event, you can do something like this:

Dim index as integer = _
myBindingSource.Find("FieldnameToSearch", myNavigationComboBox.Text)
If index <> -1 then 'it was found; move to that position
myBindingSource.Position = index
Else
MessageBox.Show("Did not find your selection. My code is messed
up.") 'haha
End If

Hmmm you've got to jump through a few hoops to activate the .Position
method (the Dim statement has to be just as you've written, the "=
New" bit is important for some reason) but that looks useful. Maybe.

The bottom line is that I'm basically just wanting to use the
datatables as a very large array. I was using Excel in the same way,
just using the sheets as array holders for VBA to access. I'm trying
to get my head around the idea of programing to access datatables in a
similar way (go to row 3, columns A through Z, add 'em up, divide
them, find the standard deviation of the numbers etc). I'm not sure
yet which would be the faster way for the computer to work with large
arrays like that - array versus arraylist versus datatable versus
dataview.... The datatable structure is nice in that I can save space
by linking tables.

I don't necessarily need clunky code that does clever things (e.g.
searching through data for a matching field name) - I just need a way
to easily access one cell, any cell that I chose, in the matrix.

I think I need to buy more literature on datasets and databases in
VB

You may want to add a private variable to your form called something like
m_Loading, set to False as a default. At the end of Form_Load, set it to
true.

In the SelectedIndexChanged event, check the m_Loading, and if it's true,
don't do the Find. This way, as it does the data bindings and whacks the
selectedindexchanged event, it won't react until you are ready for it to.

Ooh, this is helpful. I've crashed my old program a few times that I
thought was due to premature code execution. Ta V much!

Bennett

 

[RobinS]

I dunno - it's one line of code that does exactly what I want. I
can't get my head around the BindingSource thing, as I can't see a way
yet to easily access what it's looking for. I know it's supposed to
be doing everything behind the scenes but clearly it isn't doing what
I want it to, and I can't access the code inside the BindingSource
"black box" to find out why (hence the posting here!) ) at least I
can't if I drag and drop in the VB express editor.

It would be easier to have a way to look at its variables, but if I'm
writing code to do that I may as well write code to look at the
variable that says which item in a combobox has been selected :-/


Now THAT seems like a royal pain in the ass ;-) Keeping track of
multiple bindingsources to do one job, creating a master table?

It's only 2 bindingsources. You can bind your selecting combo box to one,
and bind all of your other controls to the other.

Hmmm you've got to jump through a few hoops to activate the .Position
method (the Dim statement has to be just as you've written, the "=
New" bit is important for some reason) but that looks useful. Maybe.

If you're talking about this statement:

You have to instantiate the binding source. I believe if you put the
component on the form from the toolbox, you don't have to do this, but
you'd have to test it.

The bottom line is that I'm basically just wanting to use the
datatables as a very large array. I was using Excel in the same way,
just using the sheets as array holders for VBA to access. I'm trying
to get my head around the idea of programing to access datatables in a
similar way (go to row 3, columns A through Z, add 'em up, divide
them, find the standard deviation of the numbers etc). I'm not sure
yet which would be the faster way for the computer to work with large
arrays like that - array versus arraylist versus datatable versus
dataview.... The datatable structure is nice in that I can save space
by linking tables.

I don't necessarily need clunky code that does clever things (e.g.
searching through data for a matching field name) - I just need a way
to easily access one cell, any cell that I chose, in the matrix.

I think I need to buy more literature on datasets and databases in
VB

Check out Programming ADO.Net 2.0 Core Reference by Dave Sceppa. It's very
thorough, and quite readable.

DataTables are sort of like an array. You can access the rows and columns
with an ordinal.

Dim myRow As DataRow = myDataTable.Rows(0)
Debug.Print(myRow(0))

Ooh, this is helpful. I've crashed my old program a few times that I
thought was due to premature code execution. Ta V much!

Bennett

Good luck.
Robin S.