Databinding - Parent/Child tables and Primary Key

[dsk96m]

So i have a form that I am using databinding on for my main table, but during the save, I need to be able to insert into another table values of the primary key. Basically here are my tables:


Flightplan (Main) Table
id - primary key
flightid
flightdate
...
chargeID

FlightCharge Table
id
flightid
chargeid

Charge Table
id - primary key
chargenumber
description

So my form is creating a new flight. A flight can have multiple charges and charges can have multiple flights, so i have the go between table. In my form, they put in the information for the flight (flightid,flightdate, etc), they select the charges they want associated with the flight and then save. Obviously the primary key of the flight doesnt exist yet, so i was planning on doing:

Me.Validate()
Me.FlightplanBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.FlightTestAppDataSet)

Then getting the primary key of the newly inserted row and then inserting that and the charge id into the gobetween table. Is this the right way, and if it is how do I get the primary key of the newly inserted row? It is an autoincrement primary key.

If this isnt the right way to do it, what is a better way of doing it. I used the datasource wizard to create the dataset.

 

[dsk96m]

I think I got ID, is this better?

        If Not Me.ValidateChildren() Then
            Exit Sub
        End If

        id = Convert.ToInt32(FlightplanTableAdapter.InsertFlight(Me.Flight_numberTextBox.Text, Me.Main_program_idComboBox.SelectedValue, Me.Current_program_idComboBox.SelectedValue, Format(Me.Flight_dateDateTimePicker.Text, "Short Date"), Format(Me.Flight_dateDateTimePicker.Text, "Short Date") & " " & Me.Brief_timeDateTimePicker.Text, Format(Me.Flight_dateDateTimePicker.Text, "Short Date") & " " & Me.Takeoff_time_plannedDateTimePicker.Text, Nothing, Nothing, Me.Dial_in_idComboBox.SelectedValue, Me.Passcode_idComboBox.SelectedValue, Me.Software_configTextBox.Text))

        For Each row In chargetable.Rows
            FlightchargeTableAdapter.Insert(id, row(1))
        Next

        Me.FlightplanBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.FlightTestAppDataSet)

 

[jmcilhinney]

ADO.NET takes care of this for you. You should have all three DataTables in your DataSet. When you add a FlightPlan or Charge record to the appropriate DataTable, it generates a temporary PK value. You can then add a record to your FlightCharge table using those temporary values. When you save to the database, you save the FlightPlan and Charge records first. The database generates the final PK values, the DataTables are updated in the DataSet and those values are propagated to the FlightCharge DataTable too. When you then save the FlightCharge DataTable, it saves the correct PK values.

This is very simple if you're using SQL Server or some other "proper" database. It simply requires an extra SELECT statement in your InsertCommand, which the wizard can add automatically. If you're using Access it will take a bit more effort. It depends on what provider you're using.

 

[dsk96m]

In the charge table is a list of selectable charges. They don't enter new ones. So I have a combobox that has the possible selections. When the user selects one of these items, i put them in a temp datatable. Is that not correct?

I have all three tables in the dataset. I will be adding items to the flightplan, but not the charge. the charge table is just a lookup basically. So how do I deal with this? There can be several charges.

so in flightplan for example, the id would be like 1 then in flightcharge i would have fields 1,1 1,3, etc. where the first number is the id of the flightplan that they are adding and 1 and 3 are the charge ids that they want to associate with that flight plan.

I am using an sql server. So I am not quite sure how to do this.

 

[dsk96m]

how do i use that temporary pk value for flightplan to add it to flightcharge?

 

[jmcilhinney]

how do i use that temporary pk value for flightplan to add it to flightcharge?

You don't have to care that it's temporary. You just use it as a PK. As long as you retrieve the PKs when you insert and you save in the correct order then it will be seamless. That should be happening by default so test it out first and come back with specifics if there's an issue.

 

[dsk96m]

What about what i said in my post prior to asking about the temp pk?

 

[dsk96m]

If you can't tell, I am sort of a newbie to .net. I would be able to do this the old school way with sql statements, but I am trying to learn .net. I am reading books, but im getting confused. I appreciate your patience with me.

If we can start from scratch. I will start with my sql db tables. There are three. FlightPlan, flightcharge, and chargenumber. There is a many to many relationship between flightplan and chargenumber.

There are the fields in the tables:
Flightplan (id,flight_number, main_program_id, current_program_id, flight_date, brief_time, takeoff_time_planned, takeoff_time_actual, landing_time, dial_in_id, passcode_id, and software config)
chargenumbers (id, chargenumber,description)
flightcharge (id,flight_plan_id, charge_number_id)
See DataSet Designer image below



So the chargenumber table is prefilled with values, basically a lookup table.

So I want to create a form that a user can add a new flight and add charge numbers to associate with the flight. Do my tables look like they are set up correctly. So I drag the flightplan object from the data sources list using details instead of datagrid. I then add a combobox that i data bound to chargenumbers with the displaymember as chargenumber and value as id. This is where I am confused as what to do next.

I mean i understand what you meant by i need to save the flightplan first, then the flightcharge. I dont need to save chargenumbers bc we arent adding anything to it. So what do I do next.

This is the form as how I had it, but i assume it isnt correct.



so you can see from the form, i have a combobox bound to chargenumber. The way I have it now, it just adds the item to the listbox then i save it later.

I dont think I am going about it the right way. Can you help me, step me through how to go about doing this. Once I know how to do it then I can do it for everything else. I have to do this alot, like for the flight emails. those are setup pretty much the same way. I have to do things like this many more times throughout my program, this is just the first one i am getting to.

I would appreciate any help.

 

[dsk96m]

I understand how to do it if it is just a parent child, but it is the go between table that is messing with me. I want a combo box that has the available charge numbers, they can add them, show it in a list, and on saves saves it to the flightcharge.

 

[dsk96m]

I am getting closer, but still having issues with databinding on forms with many to many relationships and adding new records. I have a combox box with options for them to choose. Then a button to add it, but not sure how to add it or where to add it.

Please help

 

[dsk96m]

How do I do that with a combobox of selectable values and add several. Where do I save it.

 

[dsk96m]

so I am here:

    Private Sub FlightplanBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles FlightplanBindingNavigatorSaveItem.Click
        Me.Validate()

        Me.FlightplanBindingSource.EndEdit()
        Me.FlightplanTableAdapter.Update(Me.FlightTestAppDataSet)

        Me.FlightchargeBindingSource.EndEdit()
        Me.FlightchargeTableAdapter.Update(Me.FlightTestAppDataSet)

        Me.TableAdapterManager.UpdateAll(Me.FlightTestAppDataSet)

        FlightTestAppDataSet.AcceptChanges()

    End Sub

If a flight already exists, this works. If i am creating a new flight and I add charges, then save, i get an insert error on Me.FlightchargeTableAdapter.Update(Me.FlightTestAppDataSet). This is the error "The INSERT statement conflicted with the FOREIGN KEY constraint "fk_flightcharge". The conflict occurred in database "FlightTestApp", table "dbo.flightplan", column 'id'.
The statement has been terminated." It has something to do with the temp pk i think. Am I saving in the wrong order?

 

[dsk96m]

So on this form. The info at the top is bound to flightplan. The grid on the left is flight charge and the grid on right is chargenumbers (filtered to current flight). If I add a charge number id to the grid on the left, you can see it puts in temp key for both id and flightplanid (which matches the id at the top right -2) so it is working correctly. When I add something to that grid is shows up in the right hand side grid. I have two problems.

First, I dont want to show the left grid, I want to use the drop down under charge numbers to add items to the flight charge table, like if i was doing it manually by entering it in the left grid, but cant figure out how to do this.
Second, this is a new record (flight), so when i save, I get the error that i posted in my last post.

I am close, I just cant get the last few steps.