473,287 Members | 1,926 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,287 software developers and data experts.

Using Combocox Selection to select/update bound items

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

Mar 2 '07 #1
5 2561
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.
------------------------------------------
<nj***@cantab.netwrote in message
news:11**********************@h3g2000cwc.googlegro ups.com...
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

Mar 4 '07 #2
On Mar 4, 1:05 pm, "RobinS" <Rob...@NoSpam.yah.nonewrote:
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 :o) 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.Selecte dIndex).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

Mar 5 '07 #3

"Bennett" <nj***@cantab.netwrote in message
news:11*********************@t69g2000cwt.googlegro ups.com...
On Mar 4, 1:05 pm, "RobinS" <Rob...@NoSpam.yah.nonewrote:
>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 :o) 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!
***It's raising the SelectedIndexChanged event as it does the data binding.
>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.Selecte dIndex).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.
Mar 5 '07 #4
On Mar 5, 12:41 am, "RobinS" <Rob...@NoSpam.yah.nonewrote:
"Bennett" <n...@cantab.netwrote in message
TextBoxMainDescription.Text =
experimentDV.Table.Rows(ListBoxExperiments.Selecte dIndex).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 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 :-P
>
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

Mar 11 '07 #5

"Bennett" <nj***@cantab.netwrote in message
news:11*********************@30g2000cwc.googlegrou ps.com...
On Mar 5, 12:41 am, "RobinS" <Rob...@NoSpam.yah.nonewrote:
>"Bennett" <n...@cantab.netwrote in message
TextBoxMainDescription.Text =
experimentDV.Table.Rows(ListBoxExperiments.Selecte dIndex).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 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?
It's only 2 bindingsources. You can bind your selecting combo box to one,
and bind all of your other controls to the other.

>
>>
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.
If you're talking about this statement:
> Dim myBindingSource as BindingSource = New BindingSource()
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 :-P

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

>>
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
Good luck.
Robin S.
Mar 13 '07 #6

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

Similar topics

7
by: Marty | last post by:
Hi, Ok I use the OLEDBConnector and dataset to retrieve data from my Access DB. I have a problem to read/parse the dataset and I would like to know if I am using the right object to reach my...
2
by: bds | last post by:
I'm new to .net 2.0 and I'm trying to figure out the best way to create a form that has a couple of controls and one of the controls is a combo box with a data bound list of items from another...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
2
by: Paul Owen | last post by:
Hmm, nobody seems to want to answer any of my questions, but here goes again! I have a Detailview control that is bound to a data source. Within the Detailview control I have a number of...
10
by: Adam Clauss | last post by:
I have a page containing a list box. This list may contain duplicate items - in which the ORDER is important. ex: a b b a is significant as compared to: b
3
by: Jay Ruyle | last post by:
I'm trying to figure out a way to list several items in a listbox and let the user select any number of items in the listbox. I have tried to code in the items as bitwise items but all it stores...
1
by: Sunray | last post by:
I have a form called the sales form and i have 2 sets of listboxes So what happens is. i add items form the bottom set of list boxes which are bound to a data base to the top set of list boxes which...
6
by: henry.onovwaka | last post by:
Can someone help on this problem. I have a combo box of, say three Items, selected from a table that serves as the lookup: E.g. 1 Earth, 2 Pine, 3 Earth Grave The combo box is on the subform. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
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...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.