locked
Parent Child Datagrid view problem RRS feed

  • Question

  • I've been able to sucessfully add rows programatically to a binded datagridview, both to the parent and child, and I'm able to save to the database with no problems. If I have one parent row and multiple child rows I can successfully modify the records and again save with no problem. When I have more than one parent row added and i focus on one of the parent rows, the child table does not filter records that are related to the parent record. In the sorce code I first populate the dataset.table with the records I want to add and then I set the bindingsource.datasource to eqaul the dataset.table. Is there something else I need to do in order to enforce the parent child relationship?

     

    Any help would be much appreciated.

    Tuesday, May 13, 2008 2:00 PM

Answers

  • Hi Jd001001,

    After the foreign key constraint has been added, the DataSet will not let you violate it. In reality that means you cannot add a record of the child table unless its foreign key value is already in the parent table.

    Best regards,
    Riquel

    Thursday, May 15, 2008 3:43 AM
    Moderator
  • Hi Jim,

    Read here about this scenario.

    Best regards,
    Riquel
    • Marked as answer by Jd001001 Friday, June 20, 2008 8:51 PM
    • Marked as answer by Jd001001 Friday, June 20, 2008 8:51 PM
    Wednesday, May 21, 2008 5:38 AM
    Moderator

All replies

  • Hi Jd001001,

    After the foreign key constraint has been added, the DataSet will not let you violate it. In reality that means you cannot add a record of the child table unless its foreign key value is already in the parent table.

    Best regards,
    Riquel

    Thursday, May 15, 2008 3:43 AM
    Moderator
  • HI Riquel,

     

    Thanks for your post. I just realised that I discribed the problem incorrectly. It's the filtering between the parent and child datagridview that is not working correctly. When I focus on a parent record, only child records related to that parent should show but all child records show instead. I've verified the bindingsource properties of both the header and child tables and I believe they are setup properly. Please find attached the code I use to populate the tables. All records do save correctly in the database. It's just not displaying correctly.

     

    Dim rCount As Integer = 0

    Dim rCount1 As Integer = 0

     

     

    Me.ShipmentHeaderBindingSource.EndEdit()

     

    Dim MyConn As New SqlConnection(ABCS.My.Settings.ABCSConnectionString)

    MyConn.Open()

    Dim MyCmd As New SqlCommand("Select * from PODetail where POHeaderID = " & POHeaderDataGridView.Item(0, e.RowIndex).Value, MyConn)

    Dim MyReader As SqlDataReader = MyCmd.ExecuteReader()

    Do While MyReader.Read

    ' Next check to see if podetailid previously added

    Dim rFound As Boolean = False

    For i = 0 To Me.ShipmentDetailDataGridView.RowCount - 1

    If Me.ShipmentDetailDataGridView.Item(2, i).Value = MyReader.Item(0) Then

    rFound = True

    End If

    If Me.ShipmentDetailDataGridView.Item(0, i).Value < 0 Then

    rCount = Me.ShipmentDetailDataGridView.Item(0, i).Value

    End If

    Next

    ' next setup color detail id

    For i = 0 To Me.ShipmentColorDetailDataGridView.RowCount - 1

    If Me.ShipmentColorDetailDataGridView.Item(0, i).Value < 0 Then

    rCount1 = Me.ShipmentColorDetailDataGridView.Item(0, i).Value

    End If

    Next

    rCount -= 1

    rCount1 -= 1

    If rFound = False Then

    Dim newShipDetailRow As ShipmentDataSet.ShipmentDetailRow

    newShipDetailRow = Me.ShipmentDataSet.ShipmentDetail.NewShipmentDetailRow

    newShipDetailRow.ShipmentID = ShipmentIDTextBox.Text

    newShipDetailRow.ShipmentDetailID = rCount

    newShipDetailRow.PODetailID = MyReader.Item(0)

    newShipDetailRow.ProductID = MyReader.Item(2)

    newShipDetailRow.Qty = MyReader.Item(3)

    newShipDetailRow.Price = MyReader.Item(4)

    newShipDetailRow.Total = MyReader.Item(6)

    newShipDetailRow.LandedCostPerc = MyReader.Item(7)

    newShipDetailRow.LandedCost = MyReader.Item(8)

    Me.ShipmentDataSet.ShipmentDetail.Rows.Add(newShipDetailRow)

    ' now setup color/size if required.

    Try

    Dim myconn2 As New SqlConnection(ABCS.My.Settings.ABCSConnectionString)

    myconn2.Open()

    Dim MyCmd2 As New SqlCommand("Select * from POColorDetail where PODetailID = " & MyReader.Item(0), myconn2)

    Dim MyReader2 As SqlDataReader = MyCmd2.ExecuteReader()

    Do While MyReader2.Read

    Dim newShipColorDetailRow As ShipmentDataSet.ShipmentColorDetailRow

    newShipColorDetailRow = Me.ShipmentDataSet.ShipmentColorDetail.NewShipmentColorDetailRow

    newShipColorDetailRow.ShipmentColorDetailID = rCount1

    newShipColorDetailRow.ShipmentDetailID = rCount

    newShipColorDetailRow.POColorID = MyReader2.Item(0)

    newShipColorDetailRow.ColorID = MyReader2.Item(2)

    For i = 1 To 20

    If Not IsDBNull(MyReader2.Item(i + 2)) Then

    Select Case i

    Case 1

    newShipColorDetailRow.QtySize01 = MyReader2.Item(i + 2)

    Case 2

    newShipColorDetailRow.QtySize02 = MyReader2.Item(i + 2)

    Case 3

    newShipColorDetailRow.QtySize03 = MyReader2.Item(i + 2)

    Case 4

    newShipColorDetailRow.QtySize04 = MyReader2.Item(i + 2)

    Case 5

    newShipColorDetailRow.QtySize05 = MyReader2.Item(i + 2)

    End Select

    End If

    Next

    newShipColorDetailRow.Total = MyReader2.Item(23)

    Me.ShipmentDataSet.ShipmentColorDetail.Rows.Add(newShipColorDetailRow)

    rCount1 -= 1

    Loop

    Me.ShipmentColorDetailBindingSource.DataSource = Me.ShipmentDataSet.ShipmentColorDetail

    Catch

    End Try

    End If

    Loop

    Me.ShipmentDetailBindingSource.DataSource = Me.ShipmentDataSet.ShipmentDetail

    MyConn.Close()

     

    Thanks for your help

    Best Regards

    Jim

    Tuesday, May 20, 2008 10:31 AM
  • Hi Jim,

    Read here about this scenario.

    Best regards,
    Riquel
    • Marked as answer by Jd001001 Friday, June 20, 2008 8:51 PM
    • Marked as answer by Jd001001 Friday, June 20, 2008 8:51 PM
    Wednesday, May 21, 2008 5:38 AM
    Moderator
  • Hi Riquel,

    Thanks for the reference to the KB Article. I however still have the problem. Two Questions

    1) In the article the DATASET is being filled by the datatable adapter, while I am manually updating the dataset (See code). Does this pose a problem.

    2) Also I am manually setting the primary key with negative integers. As far as I can tell they keys correspond correctly between parent and child.

    Please advise
    Friday, June 20, 2008 2:03 PM
  • Hi Riquel

    Please disregard my last email. The solution worked. I was not setting up the datasource as indicated.\


    Jim
    Friday, June 20, 2008 8:50 PM