can't get it right multi-tables problem

Question

Hi everyone

I really need assistance for this multiple problem am facing here. Let me take you to my hell:

I've got three tables in my database that are:

1) tbAthlete (athleteID, nationID, gameID, fname, lname)

2)tbNation (nationID, nation)

3)tbGame (gameID,game)

I connected the database to vb and created a (form1) based on a query that use all fields except IDs. that form1 is just good as displaying and navigating throug records. Now i wanted to link that form1 to another form(2) which will allow me to update and add new record. in the form2, country and game need to be combobox as there is no need to enter new record  for them. so when adding new record, only "tbAthlete" table get affected. form1 works as excpected. how do i go for the form2

Any help will be really appreciate

---

it

Answer 1

You can pass the Athlete ID to the new form2 when you create it by using a custom constructor.    This will enable you to access the database and find the record.   In Form2, create a variable for the ID, and create a New sub that looks like this

Dim ID As String

Public Sub New(ByVal AthleteId As String)
  InitializeComponent()
  ID = AthleteId
End Sub

In Form1, pass the ID of the athlete when you create and show form2:

Dim frm As Form2 = New Form2(CurrentAthleteID)
frm.Show

When form 2 loads, the value of ID can be used to access the database to look up the record and change the record details.

An alternative would be to pass three variables - ID, country and event (or a class that encapsulates those variables) so that there would be no need to access the database to look up the current details, unless the user changed something.

Answer 2

Hi Acamar 

Sorry for taking time to respond, I was trying your suggestion but couldn't get it work(I forgot to mention that I am just a novice)so i took other approach. I probably didn't clearly explain what I want my result to look, so let me try again this time.  First of all let me show you my form:

NOTE: I omitted the GameID from tbAthlete and from control in the design.

From  this form, i want to insert new Athlete into the tbAthlete knowing that "tbAthlete" has countryID in it as FK. the bit of code i am using is as follow:

 Private Sub bntSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bntSave.Click

        ComboFillMethod()

        Dim cmd As OleDbCommand
        cmd = New OleDbCommand("INSERT INTO tbAthelete" &
                                                 "(First_name, Last_Name, NationID)" &
                                                 " VALUES (@First_Name, @Last_Name, @NationID)", Objconnect)

        cmd.Parameters.AddWithValue("@First_Name", First_NameTextBox.Text)
        cmd.Parameters.AddWithValue("@Last_Name", Last_NameTextBox.Text)
        cmd.Parameters.AddWithValue("@NationID", cmbCountry.SelectedValue)


        Objconnect.Open()
        cmd.ExecuteNonQuery()
        Objconnect.Close()
    End Sub
End Class

the problem here is with the bound country combobox. I want to able to select the country name in the form and then pass its corresponding ID number to tbAthlete. with my code, when I select any country from the list, my tbAthlete get updated with the first country ID ALWAYS!!!!

In total I have 15 country if this can help.

Thank u

Any help will be appreciated. 

   

---

it

Answer 3

I can't see from your revised question what the link is between form 1 and form 2.   You indicated initially that the Athlete selected in form 1 would be updated in form 2, but now you are saying that you are only adding a new athlete in form 2.

I think you are saying that you are unable to select a different item in the country combobox.  That will happen if the combobox is bound.  Don't bind the combobox and you will be able to select any country.  However, that will mean that the member values are probably not available (depends on your ComboFillMethod).  However, that procedure seems more complex than required in any case - you can simply use the Text property instead.

Answer 4

Hello Bouman,

For these kind of questions it is important if you tell what kind of database. 

If it is about MS access like databases you only can use Ado.Net with datareaders or dataadapters and datasets.

If it is about SQL 2008 you can use

Linq to Sql
Linq to Ententies
the same as Access databases, but then with its own (faster performing) providers.

However, in all situation, create relations in your database, that will give you a boost with this kind of problems. 

---

Success
Cor

Answer 5

Hi guys

It seems I mislead you with the first posting sorry that. the problem is with the combobox "country" which has its foreign key "NationID" within tbAthlete. so what i am trying to do with the form is that: when adding new record to the tbAthlete, user will need to select a specific country for the new athlete and once the country is selected, i want its corresponding NationID to be add to tbAthlete. 

here is the structure of my tables:

tbAthlete:    tbNation:
AthleteID     NationID
NationID      Country
FirstNAME
LastName

Note: the tbNation has specific (15) country that  need to be selected when adding new athlete record. And it is a MS ACCESS DB

I hope this is a bit clearer than previous explanation. Once again sorry for misleading you guys                                           

---

it

Answer 6

What was the result when you removed the binding from the combobox in form 2?

What exactly is the country ID that you are trying to insert in the database?  Is it the text in your example (UK) or is the member value a different ID?