Update Parent and Child Table

Imad Almuzain 21 Reputation points
2022-10-27T12:49:40.233+00:00

Hello every one;

the below Code is to update Parent and Child table,
the relationship between tables was set to (Relation only) on Data Designer,
But on SQL Server, Update and Delete Rule set to (Cascade)

with this setup Parent table was Updating ok,
but child table return (Added/Modified) changes as nothing and did not get update

I tried all cases to Set (Update-Delete-Accept/Reject) relationship Rules On Dataset Designer but did not solve the issue
Could advise what to do here
thanks in advance

    ParentTableBindingSource.EndEdit  
    Dim My_Parent_Modified As DataTable = DataSet1.ParentTable.GetChanges(DataRowState.Modified)  
    Dim My_Parent_Added As DataTable = DataSet1.ParentTable.GetChanges(DataRowState.Added)  
    Dim My_Parent_Deleted As DataTable = DataSet1.ParentTable.GetChanges(DataRowState.Deleted)  
      
    If My_Parent_ Modified Is Nothing = False Then   
    End if  

    If My_Parent_ Added Is Nothing = False Then  
    End if  

    If My_Parent_Deleted Is Nothing = False Then  
    End if  

    DataSet1.ParentTable.AcceptChanges  
      
    ChildTableBindingSource.EndEdit  
    Dim My_Child_Modified As DataTable = DataSet1.ChildTable.GetChanges(DataRowState.Modified)  
    Dim My_Child_Added As DataTable = DataSet1.ChildTable.GetChanges(DataRowState.Added)  
    Dim My_Child_Deleted As DataTable = DataSet1.ChildTable.GetChanges(DataRowState.Deleted)  
      
    If My_Child_ Modified Is Nothing = False Then  
    End if  

    If My_Child_ Added Is Nothing = False Then  
    End if  

    If My_Child_Deleted Is Nothing = False Then  
    End if  

    DataSet1.ChildTable.AcceptChanges  
  

   
  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,077 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,630 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-10-27T16:22:38.953+00:00

    I don't write ado.net code any more so I cannot tell you why your code is not working as you expected.
    Just plain setting with cascading from SQL server (SSMS), should meet your requirement. Or remove the cascade setting and write code manually like what you are doing now and test whether that works or not.

    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2022-10-27T21:10:21.517+00:00

    My advice is that you first run UPDATE and DELETE statements directly in T-SQL, so that you can verify that things works as intended.

    If it does, the next step is to use Profiler to see which T-SQL statements that your is actually producing.

    The above may look pretty because it saves you from getting dirt on your hands from T-SQL, but T-SQL is not dangerous. Nor is it some sort of ugly low-level language. Rather, it is very powerful in itself. Code like the above may (or may not) have its benefits, but you absolutely need to learn what happens under the covers.

    0 comments No comments

  3. Imad Almuzain 21 Reputation points
    2022-10-28T19:02:55.027+00:00

    changing above code by putting child table code at the beginning and Parent Table at last then work fine

    thanks my friends

    JingyangLi

    ErlandSommarskog

    0 comments No comments