In Master Detail Relationship Child Table ParentId Saving Problem

[abdul4588]

I Have Two Table

1. City(CityId,CityName)
2. Emp(EmpId,Name,Age,Salary,CityId)

Where In City CityId Is PrimaryKey.
In Emp EmpId Is PrimaryKey And CityId Is ForeignKey.

I Want To Know How To Create Relationship Between This Two Table In Easy Way Through Code.

----------------My Code----------------

Imports System.Data.SqlClient
 
Public Class Form1
    Dim Con As New SqlConnection("Data Source = Raees\SQL2008;User Id= ra;Integrated Security=True;Initial Catalog=Vb;")
    Dim Ds As New DataSet()
 
 
    Dim cmdCTSlc As New SqlCommand("Select * From City Order By CityName", Con)
    Dim cmdCTDel As New SqlCommand("Delete From City Where CityId = @CityId", Con)
    Dim cmdCTUpd As New SqlCommand("Update City Set CityName = @CityName Where CityId = @CityId", Con)
    Dim cmdCTIns As New SqlCommand("Insert Into City(CityName) Values(@CityName)", Con)
 
    Dim cmdEMPSlc As New SqlCommand("Select * From Emp Order By Name", Con)
    Dim cmdEMPDel As New SqlCommand("Delete From Emp Where EmpId = @EmpId", Con)
    Dim cmdEMPUpd As New SqlCommand("Update Emp Set Name = @Name,Age = @Age,Salary = @Salary Where EmpId = @EmpId", Con)
    Dim cmdEMPIns As New SqlCommand("Insert Into Emp(Name,Age,Salary,CityId) Values(@Name,@Age,@Salary,@CityId)", Con)
 
 
    Dim DaCT As New SqlDataAdapter(cmdCTSlc)
    Dim DaEMP As New SqlDataAdapter(cmdEMPSlc)
 
    Dim bsCT As New BindingSource
    Dim bsEMP As New BindingSource
    Dim bsRel As New BindingSource
 
    Dim DRel As DataRelation
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Con.Open()
 
        'DaCT.MissingSchemaAction = MissingSchemaAction.AddWithKey
        DaCT.Fill(Ds, "City")
 
        DaCT.UpdateCommand = cmdCTUpd
        DaCT.DeleteCommand = cmdCTDel
        DaCT.InsertCommand = cmdCTIns
        cmdCTUpd.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
        cmdCTUpd.Parameters.Add("CityName", System.Data.SqlDbType.VarChar, 50, "CityName")
        cmdCTUpd.Parameters.Item("CityId").SourceVersion = DataRowVersion.Original
 
        cmdCTDel.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
        cmdCTDel.Parameters.Item("CityId").SourceVersion = DataRowVersion.Original
 
        cmdCTIns.Parameters.Add("CityName", System.Data.SqlDbType.VarChar, 50, "CityName")
 
        ' DaEMP.MissingSchemaAction = MissingSchemaAction.AddWithKey
        DaEMP.Fill(Ds, "Emp")
 
        DaEMP.UpdateCommand = cmdEMPUpd
        DaEMP.DeleteCommand = cmdEMPDel
        DaEMP.InsertCommand = cmdEMPIns
        ' DaEMP.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
 
 
        cmdEMPUpd.Parameters.Add("EmpId", System.Data.SqlDbType.Int, 5, "EmpId")
        cmdEMPUpd.Parameters.Add("Name", System.Data.SqlDbType.VarChar, 50, "Name")
        cmdEMPUpd.Parameters.Add("Age", System.Data.SqlDbType.Int, 5, "Age")
        cmdEMPUpd.Parameters.Add("Salary", System.Data.SqlDbType.Float, 5, "Salary")
        cmdEMPUpd.Parameters.Item("EmpId").SourceVersion = DataRowVersion.Original
 
        cmdEMPIns.Parameters.Add("Name", System.Data.SqlDbType.VarChar, 50, "Name")
        cmdEMPIns.Parameters.Add("Age", System.Data.SqlDbType.Int, 5, "Age")
        cmdEMPIns.Parameters.Add("Salary", System.Data.SqlDbType.Float, 5, "Salary")
        cmdEMPIns.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
 
        cmdEMPDel.Parameters.Add("EmpId", System.Data.SqlDbType.Int, 5, "EmpId")
        cmdEMPDel.Parameters.Item("EmpId").SourceVersion = DataRowVersion.Original
 
 
 
        DRel = New DataRelation("Rel_CT_Emp", Ds.Tables(0).Columns("CityId"), Ds.Tables(1).Columns("CityId"))
 
        Ds.Relations.Add(DRel)
 
 
        'Dim foreignKey As ForeignKeyConstraint = DRel.ChildKeyConstraint()
        'foreignKey.DeleteRule = Rule.Cascade
        'foreignKey.UpdateRule = Rule.None
        'foreignKey.AcceptRejectRule = AcceptRejectRule.Cascade
 
        bsCT.DataSource = Ds
        bsCT.DataMember = "City"
 
        bsEMP.DataSource = Ds
        bsEMP.DataMember = "Emp"
 
        Dim TxtCTNm As New TextBox
        'TxtCTNm.Location = New System.Drawing.Point(10, 25)
        TxtCTNm.Top = 50
        TxtCTNm.Left = 25
        TxtCTNm.Width = 200
        TxtCTNm.DataBindings.Add("Text", bsCT, "CityName")
        Me.Controls.Add(TxtCTNm)
 
        Dim NavigCT As New BindingNavigator
        NavigCT.BindingSource = bsCT
        NavigCT.AddStandardItems()
        Me.Controls.Add(NavigCT)
 
 
        Dim EmpGrd As New DataGrid
 
        EmpGrd.DataSource = bsCT
        EmpGrd.DataMember = "Rel_CT_Emp"
        EmpGrd.Top = 100
        EmpGrd.Left = 25
        EmpGrd.Height = 200
        EmpGrd.Width = 500
 
        Me.Controls.Add(EmpGrd)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Validate()
        bsCT.EndEdit()
        DaCT.Update(Ds, Ds.Tables(0).ToString)
 
 
 
 
        bsEMP.EndEdit()
        DaEMP.Update(Ds, Ds.Tables(1).ToString)
 
        Ds.Clear()
        DaCT.Fill(Ds.Tables(0))
        DaEMP.Fill(Ds.Tables(1))
 
 
    End Sub
End Class

------------------ Code End-----------

My Problem Is That When I Add New Record In Parent(City)
And Add Its Child Record(Emp),In Child Table (Emp)
ParentId(CityId) Saves "Null".

[Frinavale]

You could use one of the DataAdapter's FillSchema methods to create the same schema for the in memory tables (DataTables) as the database tables.

This will enforce the same rules (you will get an exception when you try to save invalid data).

As for saving nulls, after you save the City, you need to retrieve the CityID that it was saved with so that you can save the Emp with the foreign key.

-Frinny

[abdul4588]

Can U Plz Show Me Example Of It.
Means How To Save City First And Set Its CityId In Emp And Then Save Emp...

[Frinavale]

Call the Update method that updates the City table first.

Once you have done that, call the Fill method to retrieve the IDs automatically assigned to the city rows.

Once you have done that, create your Emp rows with the data that you want in it...taking the ID of the City row that you want the Emp row to reference. Then call Update to update the Emp table.

-Frinny

[abdul4588]

Is There Any Way To Check Whether A Emp Table Data Are Proper And
Are Not Going To Generate An Error On Saving,Mean Reference Error Or Any Other Error That Rejects The Emp Data To Post.

In That Case City Will Save Without Emp Data.
And I Want To Save City Data With Atleast One Emp In It.
How To RollBack That Changes If Emp Data Gives Error On Save.

[Frinavale]

Start a transaction (this is an in-memory .NET transaction, not a database transaction) and if anything goes wrong, roll it back. If everything is successful, commit the transaction.

-Frinny

[abdul4588]

Thank For The Help.
But How Can I Keep Record Of Which Employee Is Of What City..

Means If I Insert Two New Cities And Their Emp Records.
How Can I Identify That Which Emp Record Is Of Which City?