473,713 Members | 2,604 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 BindingNavigato r 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 BindingNavigato r. However, when I use the combobox to select an
entry from the dropdown I get a ConstraintExcep tion 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 2593
Yes, use a combobox that is not bound to the rest of the data, and respond
to the SelectIndexChan ged 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 SelectedIndexCh anged 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.n etwrote in message
news:11******** **************@ h3g2000cwc.goog legroups.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 BindingNavigato r 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 BindingNavigato r. However, when I use the combobox to select an
entry from the dropdown I get a ConstraintExcep tion 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 SelectIndexChan ged 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 SelectedIndexCh anged 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.

TextBoxMainDesc ription.Text =
experimentDV.Ta ble.Rows(ListBo xExperiments.Se lectedIndex).It em(1).ToString

Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperim ents.SelectedIn dex) 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.n etwrote in message
news:11******** *************@t 69g2000cwt.goog legroups.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 SelectIndexChan ged 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 SelectedIndexCh anged 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 SelectedIndexCh anged 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.

TextBoxMainDesc ription.Text =
experimentDV.Ta ble.Rows(ListBo xExperiments.Se lectedIndex).It em(1).ToString

Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperim ents.SelectedIn dex) 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("N EWCO")
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("Or derID"),
dt.Columns("Pro ductID")}
Dim objCriteria As New Object() {10643, 28}
Dim row As DataRow = dt.Rows.Find(ob jCriteria)

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.Table s(0) or
whatever
'bind all of your controls to the BindingSource, not the DataTable
m_Combo.DataSou rce = myBindingSource
m_Combo.Display Member = "CompanyNam e"
m_Combo.ValueMe mber = "CustomerID "
m_CustomerTextB ox.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 SelectedIndexCh anged event, you can do something like this:

Dim index as integer = _
myBindingSource .Find("Fieldnam eToSearch", myNavigationCom boBox.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 SelectedIndexCh anged 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
selectedindexch anged 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.ne twrote in message
TextBoxMainDesc ription.Text =
experimentDV.Ta ble.Rows(ListBo xExperiments.Se lectedIndex).It em(1).ToString
Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperim ents.SelectedIn dex) 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.Table s(0) or
whatever
'bind all of your controls to the BindingSource, not the DataTable
m_Combo.DataSou rce = myBindingSource
m_Combo.Display Member = "CompanyNam e"
m_Combo.ValueMe mber = "CustomerID "
m_CustomerTextB ox.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 SelectedIndexCh anged event, you can do something like this:

Dim index as integer = _
myBindingSource .Find("Fieldnam eToSearch", myNavigationCom boBox.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 SelectedIndexCh anged 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
selectedindexch anged 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.n etwrote in message
news:11******** *************@3 0g2000cwc.googl egroups.com...
On Mar 5, 12:41 am, "RobinS" <Rob...@NoSpam. yah.nonewrote:
>"Bennett" <n...@cantab.ne twrote in message
TextBoxMainDesc ription.Text =
experimentDV.Ta ble.Rows(ListBo xExperiments.Se lectedIndex).It em(1).ToString
Loads the second entry from the dataset (Item(1)) of the selected
experiment (ListBoxExperim ents.SelectedIn dex) 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.Table s(0) or
whatever
'bind all of your controls to the BindingSource, not the DataTable
m_Combo.DataSou rce = myBindingSource
m_Combo.Display Member = "CompanyNam e"
m_Combo.ValueMe mber = "CustomerID "
m_CustomerTextB ox.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 SelectedIndexCh anged event, you can do something like this:

Dim index as integer = _
myBindingSource .Find("Fieldnam eToSearch",
myNavigationCo mboBox.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.Row s(0)
Debug.Print(myR ow(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 SelectedIndexCh anged 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
selectedindexc hanged 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
1802
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 goal. And I don't want to spend hours to find out, would you tell me if I'm going in the wrong path? Private dbConnector As New OleDbConnection
2
9590
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 table. To understand the problem more, consider using the AdventureWorks2000 database. I want a simple form that shows FirstName, LastName, and Shift. Instead of the ShiftID, I want the shift name from the Shift table to be displayed. Here's...
19
4106
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 the code that implements managing unbound controls on forms given the superior performance of unbound controls in a client/server environment. I can easily understand a newbie using bound controls or someone with a tight deadline. I guess I need...
2
3355
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 dropdownlist controls that I want to list available options but will be bound to the Detailview control. I can achieve all this and seem to select the correct item from the datasource. However, when I try to update, the bound dropdownlist value seems to...
10
3297
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
2386
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 in the database is the top item in the listbox. How can I get the listbox to add all the bit values to be stored and also come out properly when viewed later? Example:
1
4028
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 are not bound, I select from the bottom set and add to the top set which works fine, but now i decide to remove an item from the top set. when i tried to use a remove item code it worked fine, it did delete the item form the list but it added...
6
2908
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. If the user selects any of the item, that item so selected should not appear in the list again; or The user should be warned about previous selection.
0
8701
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9166
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9066
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9007
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7939
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5942
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4459
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3154
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2101
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.