I am working on a database program for my company, and am having some issues. I'm using Visual Studio 2010 Professional using Visual Basic 2010. My database is set up through SQLServer 2008 R2.

I have a form that is being used to add parts to the database. One of the feilds that we are using is who we get the part from, or the vendor. In some cases, we have 2 or 3 vendors that send us the same part.

My tables are tblPartNum and tblVendors. In tblPartNum I have a column for each vendor named vendorID1 and vendorID2. In tblVendor I have a VendorID column and a VendorName column.

In my form I have inserted a combobox for each of these columns labeled cmbVendorID1 and cmbVendorID2. I'm trying to connect the comboboxes to tblVendor so that when a user clicks the drop down, the list will be pulled from the VendorName but what gets saved into the database is the VendorID. The only way I know how to do this is by binding the data to the comboboxes, which is working when there is a single combobox. When I added cmbVendorID2, everytime I change cmbVendorID1, it is also changing cmbVendorID2.

Can anyone help me so I am able to choose two different vendors?

You would be better served breaking out vendor relationships to parts in a separate table. I would suggest creating a tblPartVendor that contains the part id and the vendor id. This allows you to create zero or more relationships between vendors and parts without touching your schema.

What are you using for data access? What did you bind your combo boxes to?

If you had read my post completely, you would have seen I have already done that. I'm using the VendorID in my tblVendor as a primary key and as an identifier. I need to be able to allow two different vendors to be put into my tblPartNum since some of our parts come from two different places. I'd like to know how to make it so that in my program, I can do that. Or could my problem be in how my relations are set up in my database. I guess I should have asked this as well... Can you attach a primary key from one table to two foreign keys of another?

No, you did not. You created a tblPartNum that has all the details for the parts AND the vendors. You can't do what I just told you to do with only two tables.

You told me what table in your database you got the data from. That's not the same as what you used for data access, and what you used to store the data in memory before binding to it.

Also, it will behoove you to learn what version of VB.NET you're using. 2010 is not a version. Different data access and gui components are available depending on whether you're in 1.1, 2, 3, 3.5 or 4.

Teddy, tblpartnum has no info about the vendors other than the vendor id. All the info for the vendors is in tblVendors.

As for what you had said in your first post, I admit I misread it, but i still get the same problem I had before of needing to put in multiple vendors with the one part number.

As for what vb.net i'm running the version is v.2.0.50727.

And thank you for being patient with me. Been a long project. Anything else you need to know, just ask.

I understand how you linked vendors. I meant you had all the details for the part number AND links to the vendors in the same table.

Keep your part numbers in one table, vendors in another table, and relationships between parts and vendors in its own table. You can then create as many unique combinations of part numbers and vendors as you want:

tblPartVendor
----------
PartID
VendorID
[anything else you want to store to define relationships between parts and vendors can get tacked on here]

Using this structure, it becomes trivial to create a list of vendors for any given part id, or a list of parts for any given vendor id.


If you're using 2.x, I'm guessing you're using ADO.NET for data access?

Is this a windows forms application? ASP.NET?


It will help if you post some code used to retrieve data from the database and bind it to your combobox.

The two of you didn't understand each other!

To tell the truth, I have no clue how to get the code that is used to get the data. I've been using the GUI for all the connections from the database to the program. I should have said in the first place that so far, I really haven't used any code. Been using the GUI for most of it. And I did make a tblPartNum, but I'm still getting the same problem. I am still having the problem of when I cahnge one of the vendors, it changes the other 2 vendor comboboxes. I would give up on the combobox and see if a listbox would help, but there are over 100 vendors, so I'd rather not have a listbox that long. Would it help if I added screenshots of the problem?

There are a couple different ways you might be using a GUI to define data access. I'm afraid we'll need a good deal more information.

Are you fluent in any of the .NET languages?

Fluent? Not anymore. I went to college for it and graduated spring of '09, but haven't been able to do anything with the degree until recently. And I haven't used any visual studio past 2005. My boss got 2010, and a few things have changed making it a little harder to recall everything.

I apreciate the help Teddy has given me, but I've decided to do things differently. Instead of drop down menus on the form, I went with Teddy's idea of a tblPartVendor and used a datagridview of it at the bottom of the form with dropboxes in that to show my vendors. Thanks again Teddy. I do appreciate you working with me through this.