locked
In Master Detail Relationship Child Table ParentId Saving Problem RRS feed

  • Question

  • 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".
    Wednesday, August 8, 2012 11:33 AM

Answers

All replies

  • Hi, my suggestion would be to create a stored procedure in your database that performs the insert and handle the primary key / foreign key relationship there.  After that, calling your SP from VB code becomes very easy.

    In the following thread, there is a good example of this type of stored procedure along with some good tips and references from Arnie and Naomi:
    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/77ce4b34-581b-47c8-aad6-96910ecd8ab5/

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, August 8, 2012 2:50 PM
  •  

    >> I have Two Table <<

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. What little you did post is wrong and shows you never did any research.

    CREATE TABLE Cities
    (city_id CHAR(5) NOT NULL PRIMARY KEY,
     city_name VARCHAR(25) NOT NULL,
     state_code CHAR(2) NOT NULL);

    CREATE TABLE Personnel
    (emp_id CHAR(9) NOT NULL PRIMARY KEY,
     emp_name VARCHAR(35) NOT NULL,
     birth_date DATE NOT NULL,  -- not the age! 

     etc);

    CREATE TABLE Job_Assignments
    (emp_id CHAR(9) NOT NULL 
      REFERENCES Personnel (emp_id)
     job_name VARCHAR(35) NOT NULL,
     salary_amt DECIMAL (12,2) NOT NULL,
     city_id CHAR(5) NOT NULL
      REFERENCES Cities);

    Employees have a relationship with a  location and a job; they are not attributes of the employee. Why did you embed SQL in another language in a SQL Forum? 

    The terms "Master", "parent", "child" etc are all non-relational terms. You need to read a book on data modeling; you are doing everything wrong. 



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, August 8, 2012 5:52 PM
  • CELKO,

    Could you email me? It's edprice at Microsoft. We're going to need to remove some of the jabs in your post, per our guidelines. Interacting with new SQL folks is to be expected.

    However, your content in your post is very good, and your contributions to the forums and with your books have been integral to the community. So I'd like to discuss this with you rather than an admin changing anything.

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Experience Program Manager (Blog, Twitter, Wiki)


    Thursday, August 9, 2012 6:39 PM
  • It's about time.  LOL!!!


    Ryan Shuell

    Wednesday, August 15, 2012 7:19 PM