VS 2008 Relational Database Entries

[Specialt1212]

Hello, I created a simple database that has 3 tables & 2 joint tables for data entry purposes.

The tables & fields are listed below. All items that appear in red are the primary keys

Employee
EmployeeID
- FirstName
- LastName

Software
SoftwareID
- SoftwareName

Computer
ComputerID
- ComputerTypes

JtblEmployeeSoftware
EmployeeID
SoftwareID

JtblEmployeeComputer
EmployeeID
ComputerID

I had no problem creating the database or adding it as a datasource but now I need to figure out how to add data to all the individual tables without having to remember the assigned ID field for each table entry. This is a really basic example of what I would like to do but I'm trying to figure out the basics first before I doing anything else on a larger scale.

Ideally for this example I would like to have textboxes for the Employee First / Last name as well as a datagridview to enter Computer & Software information since they may have multiple computers and software applications. And I was hoping to avoid having to manually enter data in the joint table. Is that possible to do or will I be required to enter relational data in the joint tables?

I've include the source so you can look at it in case that will help make more sense.

Download Link - http://www.mediafire.com/?jnbwetbm0bt

[jmcilhinney]

You should have a DataSet containing all the DataTables and DataRelations between them. You can configure the DataRelations to propagate changes from parent to child tables. You can then add, for instance, an Employee record and a Software record and the DataTables will generate temporary IDs, which you can then add to a JtblEmployeeSoftware record. When you save the data, you save the Employee and Software data first, which will update the local records with the IDs generated by the database. Those IDs will be propagated to the child table automatically, so you can then simply save the child data immediately after.

[Specialt1212]

Thank you for the response, but I'm not sure if I understand 100%

Here's an image of all the DataTables and DataRelations within my Dataset.



And here is a picture of how i step up the original application



I can add an employee to the datatable for example

EmployeeID: 4
EmployeeFirstName: John
EmployeeLastName: Test

Then if I click the save button it will save the new record and then I can manually add records to the joint tables showing what computers & Software John Test has access too. This option works great without any issues.

Now lets just say I wanted to enter data in a different manor and for simplicities sake lets just say each employee can only have two type of Software & two types of Computers like seen in the example below.



Could I have a setup like the image shown above where the user would enter only the Employee ID, First Name, Last Name, Software Name 1 & 2, and Computer Name 1 & 2 and have the joint tables auto update based on the information input in the fields?

If I'm not explaining myself clearly please let me know and I'll try and go into more detail. Thank you for the help!

[Specialt1212]

I've been trying to figure this out for two days and haven found an answer. I would greatly appreciate any help provided.

I made some changes to the application to make my example easier.

I've bound the two comboboxes to the Software & Computer fields in the software and computer tables. So each drop down menu displays entries from their respective tables as seen below.





How can I make the Software ID field populate a value based on the selected item from the Software combobox?

[jmcilhinney]

You need to bind the ComboBox twice: once to the parent table and once to the child table.

For the parent table you use complex data-binding, i.e. you set the DataSource, DisplayMember and ValueMember. You set the ValueMember to the name of the PK field. When the user makes a selection, the PK of that item is exposed via the SelectedValue property.

For the child table you use simple data-binding, i.e. you call the DataBindings.Add method. You will bind the SelectedValue of the control to the FK field of the child data source.

[Specialt1212]

Thank you for the response, I wasn't aware of that I could bind a Combobox to two tables, but that will be very useful.

I appreciate your help!

[Specialt1212]

Ok, I think I understood what you said, I wrote it out word for word in case anyone else would like to accomplish this as well. Although if I made any errors please feel free to point it out.

  Main Tables:
  • Employee
     - EmployeeID
     - FirstName
     - LastName		  Joint Tables:
  • Computer		  • JtblEmployeeComputer
     - ComputerID		     - EmployeeID
     - ComputerType 		     - ComputerID
  • Software		  • JtblEmployeeSoftware
     - SoftwareID		     - EmployeeID
     - SoftwareName		     - SoftwareID

Part 1: Software Combobox
- Click on the Software Combobox and expand the properties menu
- Expand the DataBinding section in the properties window which is under the category of Data
- Make sure the following items are listed as seen below
- SelectedValue: SoftwareBindingSource - SoftwareID
- DataSource: SoftwareBindingSource
- DisplayMember: SoftwareName
- ValueMember: SoftwareID

Part 2: Software ID Textbox
- Click on the Software ID Textbox and expand the properties menu
- Expand the DataBinding section in the properties window which is under the category of Data
- Make sure the following items are listed as seen below
- Text: SoftwareBindingSource - SoftwareID

So now when the selected item in the Software Combobox changes the item being displayed in the SoftwareID textbox updates to display its corresponding entry e.g.



Now using the same example above, I'm trying to get the save buttons to work, so I tried modifying the code for the original save control as seen in the example below...

Original Code

    Private Sub EmployeeBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmployeeBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.EmployeeBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
    End Sub

So I modified the code to work with the 1st save button which saves the EmployeeID / FirstName / LastName to the Employee Table which works and saves fine.

Modified code applied to 1st Save button (Button1)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'This code uses button1 to save new data entries to the Employee table
        Me.Validate()
        Me.EmployeeBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
    End Sub

But, I can't seem to get the 2nd save button (button2) to work.

The purpose of this is to Save the 2nd EmployeeID textbox & the SoftwareID textbox to the JtblEmployeeSoftware table as a new entry.

Modified code applied to 2nd Save button (Button2)

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'This code uses button2 to save new data entries to the JtbleEmployeeSoftware table
        Me.Validate()
        Me.JtblEmployeeSoftwareBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
    End Sub

Does anyone know what I'm doing incorrectly?