ComboBox updating data source via look-up table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi - first post and not quite sure which board to post to as it involves
windows forms and data access .... sorry if wrong one chosen!

I am developing a database project for my small vehicle trading business,
and thus have a cars table and (among others) a colours table. I use a combo
box as a look up to pick a colour for a car and all works well .... until I
want to add a new colour to the colour table and at the same time select this
new colour as the one relevant to the current car. I add the new colour to
the colour table by calling a stored procedure and sure enough the new colour
is stored. But when I save the new record the old colour is the one STILL
associated with that record.

I have tried playing around with lots of things but can't get the behaviour
I want. I hope I've explained my problem clearly, any help greatly
appreciated!!! Dave
 
Hi - first post and not quite sure which board to post to as it involves
windows forms and data access .... sorry if wrong one chosen!

I am developing a database project for my small vehicle trading business,
and thus have a cars table and (among others) a colours table. I use a combo
box as a look up to pick a colour for a car and all works well .... until I
want to add a new colour to the colour table and at the same time select this
new colour as the one relevant to the current car. I add the new colour to
the colour table by calling a stored procedure and sure enough the new colour
is stored. But when I save the new record the old colour is the one STILL
associated with that record.

I have tried playing around with lots of things but can't get the behaviour
I want. I hope I've explained my problem clearly, any help greatly
appreciated!!! Dave
Click to expand...

I presume you are storing the colors in a lookup table and joining the Car and
color tables to get the main row of data, then linking the combobox to the car's
color via the row id of the colors table. If you are doing that then you will
need to do an update of the row in the car table that changes the id in the
column of the car table that links the car with the color.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Hi Otis, thanks for the reply. You got the gist of it. What I'm trying to do
is to replicate the 'NotInList' property of combo boxes available in Access.
I can change the colour of the car in the car table through the combo box if
the colour already exists - the trouble comes when I want to add a new
colour! (and it's usually when adding a new car that I want to add a new
colour!) . I am being lazy really using the bindingsource and navigator
controls in .net 2005 rather than coding the updates manually, but as the
behaviour was available in Access I can't believe you can't replicate it in
..net!! Maybe you can't!
 
Hi Otis, thanks for the reply. You got the gist of it. What I'm trying to do
is to replicate the 'NotInList' property of combo boxes available in Access.
I can change the colour of the car in the car table through the combo box if
the colour already exists - the trouble comes when I want to add a new
colour! (and it's usually when adding a new car that I want to add a new
colour!) . I am being lazy really using the bindingsource and navigator
controls in .net 2005 rather than coding the updates manually, but as the
behaviour was available in Access I can't believe you can't replicate it in
.net!! Maybe you can't!
Click to expand...

Hello again Dave,

Having not developed an Access application since 1996, I'm kind of hazy on what
you mean by the 'NotInList' property. However I'll try to be a little more
specific regarding what I said earlier on.

I'm not sure you will be able to automatically cause the new color to be added
to the color table through databinding. That doesn't mean you can't do it it
just means I don't know how to do it.

Now if I were going to do what I think you want to do (and I would do it
manually) I guess I would set the ComboBox.DropDownStyle to
DropDownStyle.DropDown so the user can enter the new color

Now pseudo code here down:

User enters the color
User clicks the go button
Get the color name from the Combobox.TEXT property
Insert that color into your color table and retrieve the row id of the newly
created row from the table.
Add the Color to the Combobox's Items list and add the row id the to
corresponding value items list.

Update the column in the row describing the car with the id of the color the
user entered.

After doing that, the next time you pull that row from the car table you will
get the correct color and the combobox will also contain the new color in it's
item list.

That's how I would do it, but there is probably some one reading this who knows
an easier way through binding.

Even if you do it manually and are using a strong typed dataset there will
probably only be a few lines of code required, since with a strongly typed DS
you have insert, update and delete methods on the tables the dataset includes.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Hi Otis,

Thanks for your input. I've managed to achieve it using various flags etc.
It's functional but far from pretty! The 'NotInList' method of an Access
combobox enables a user to add data to one table and then take the id of the
newly added item as a value in the foreign key table in one relatively
effortless motion! Just thought .net would offer the same capability. Perhaps
it does, but if so it's buried in the help files beyond my reach! Perhaps
I'll post a similar question in a data related group to see if anyone has any
ideas. Thanks again for your time and trouble. Dave
 
Back
Top