Only some data is being saved to a sql database

[kenharding]

Hello all,

I'm programming in VB and learning sql databases and I have created a database with the following 3 files:

File1: ProjectHead
ProjectID (primary key)
DataItem1
DataItem2


File2: DataSheetList
DataSheetID (primary key)
DataItem1
DataItem2
ProjectID (foreign key which relates to primary key in ProjectHead)

File3: DataSheetHead
DataHeadID (primary key)
DataItem1
DataItem2
DataSheetID (foreign key which relates to primary key in DataSheetList)

In my program I brows down ProjectHead and when an entry is found DataSheetList is examined and all the entries that have a matching foreign key with ProjectHead's primary key are placed in a list box control (DataItem1 from DataSheetList is put in the listbox). When the user selects an entry in the listbox (there may be several) the corresponding entry in DataSheetHead is loaded into a Detailed View (as apposed to a Data grid view) form.

Getting the correct data to appear on the screen is working fine.

If the user selects the first entry in the listview and edits the data in DataSheetHead that pops up on the screen and saves that data away again all is ok.

However, if the user selects the second entry (or third, or fourth etc) from the listview, the correct data is appearing on the screen but when that data is edited and the save button is pressed no data is being written away.

I have stripped all the data from all of the files and carefully re-populated them to make sure that my keys and foreign keys are pointing to the correct files. When I select the data from the listbox the correct data is being written into the textboxes on my screen. It doesn't matter how many entries I have in the listbox, only the first record is written to the file correctly.

What is confusing me here is that when I brows through the files all the correct data is being written to the screen and when I edit and save the data for record 1 from DataSheetHead it is working fine. It only goes wrong when I try to edit record 2 or 3 and so on.

Please note that for every entry in the listbox, there is only ever one entry in the DataSheetHead file.

Here is my code:

Public Class formOne 
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As  System.EventArgs) Handles MyBase.Load 
 
        Me.DataSheetHeadTableAdapter.Fill(Me.PcmDataSet.DataSheetHead) 
 
        Me.DataSheetListTableAdapter.Fill(Me.PcmDataSet.DataSheetList) 
 
        Me.ProjectHeadTableAdapter.Fill(Me.PcmDataSet.ProjectHead) 
 
    End Sub 
 
    Private Sub ProjectHeadBindingNavigatorSaveItem_Click(ByVal  sender As System.Object, ByVal e As System.EventArgs) _
Handles  ProjectHeadBindingNavigatorSaveItem.Click 
 
        Dim a As Integer = 0 
 
        Me.Validate() 
        Me.DataSheetHeadBindingSource.EndEdit() 
        a =  Me.DataSheetHeadTableAdapter.Update(Me.PcmDataSet.DataSheetHead) 
        MsgBox(a & " data sheet head updated") 
 
    End Sub 
 
End Class

thanks for looking


Ken

 

[cjard]

I'm programming in VB and learning sql databases and I have created a database with the following 3 files

when you say "files" do you mean "tables" ? THings in databases that hold data are called TABLES.. if thats what you mean, please use the correct term in future posts..

In my program I brows down ProjectHead and when an entry is found DataSheetList is examined and all the entries that have a matching foreign key with ProjectHead's primary key are placed in a list box control (DataItem1 from DataSheetList is put in the listbox). When the user selects an entry in the listbox (there may be several) the corresponding entry in DataSheetHead is loaded into a Detailed View (as apposed to a Data grid view) form.

Sounds quite complicated. DId you know that a datarelation can do this for you automatically?

Me.DataSheetHeadBindingSource.EndEdit()
a = Me.DataSheetHeadTableAdapter.Update(Me.PcmDataSet.DataSheetHead)

The problem that things are not being updated is reasonably simple, i think.. You only tell one tableadapter to update the database..

Please note that for every entry in the listbox, there is only ever one entry in the DataSheetHead file

If the relationship from list:head is 1:1 they ought to be in the same table..

 

[kenharding]

Thanks for your reply, cjard.

I take your point about not needing that third table and how it should be incorporated into the second table. This I will do.

However, using datarelations has helped so thank you for pointing me in the right direction.

It's all part of the learning process

Regards,
Ken

 

[cjard]

The section in the DW2 link (see signature), headed "Displaying related data in your app" should help