By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,240 Members | 1,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,240 IT Pros & Developers. It's quick & easy.

In Master Detail Relationship Child Table ParentId Saving Problem

P: 4
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----------------
Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.SqlClient
  2.  
  3. Public Class Form1
  4.     Dim Con As New SqlConnection("Data Source = Raees\SQL2008;User Id= ra;Integrated Security=True;Initial Catalog=Vb;")
  5.     Dim Ds As New DataSet()
  6.  
  7.  
  8.     Dim cmdCTSlc As New SqlCommand("Select * From City Order By CityName", Con)
  9.     Dim cmdCTDel As New SqlCommand("Delete From City Where CityId = @CityId", Con)
  10.     Dim cmdCTUpd As New SqlCommand("Update City Set CityName = @CityName Where CityId = @CityId", Con)
  11.     Dim cmdCTIns As New SqlCommand("Insert Into City(CityName) Values(@CityName)", Con)
  12.  
  13.     Dim cmdEMPSlc As New SqlCommand("Select * From Emp Order By Name", Con)
  14.     Dim cmdEMPDel As New SqlCommand("Delete From Emp Where EmpId = @EmpId", Con)
  15.     Dim cmdEMPUpd As New SqlCommand("Update Emp Set Name = @Name,Age = @Age,Salary = @Salary Where EmpId = @EmpId", Con)
  16.     Dim cmdEMPIns As New SqlCommand("Insert Into Emp(Name,Age,Salary,CityId) Values(@Name,@Age,@Salary,@CityId)", Con)
  17.  
  18.  
  19.     Dim DaCT As New SqlDataAdapter(cmdCTSlc)
  20.     Dim DaEMP As New SqlDataAdapter(cmdEMPSlc)
  21.  
  22.     Dim bsCT As New BindingSource
  23.     Dim bsEMP As New BindingSource
  24.     Dim bsRel As New BindingSource
  25.  
  26.     Dim DRel As DataRelation
  27.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  28.         Con.Open()
  29.  
  30.         'DaCT.MissingSchemaAction = MissingSchemaAction.AddWithKey
  31.         DaCT.Fill(Ds, "City")
  32.  
  33.         DaCT.UpdateCommand = cmdCTUpd
  34.         DaCT.DeleteCommand = cmdCTDel
  35.         DaCT.InsertCommand = cmdCTIns
  36.         cmdCTUpd.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
  37.         cmdCTUpd.Parameters.Add("CityName", System.Data.SqlDbType.VarChar, 50, "CityName")
  38.         cmdCTUpd.Parameters.Item("CityId").SourceVersion = DataRowVersion.Original
  39.  
  40.         cmdCTDel.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
  41.         cmdCTDel.Parameters.Item("CityId").SourceVersion = DataRowVersion.Original
  42.  
  43.         cmdCTIns.Parameters.Add("CityName", System.Data.SqlDbType.VarChar, 50, "CityName")
  44.  
  45.         ' DaEMP.MissingSchemaAction = MissingSchemaAction.AddWithKey
  46.         DaEMP.Fill(Ds, "Emp")
  47.  
  48.         DaEMP.UpdateCommand = cmdEMPUpd
  49.         DaEMP.DeleteCommand = cmdEMPDel
  50.         DaEMP.InsertCommand = cmdEMPIns
  51.         ' DaEMP.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
  52.  
  53.  
  54.         cmdEMPUpd.Parameters.Add("EmpId", System.Data.SqlDbType.Int, 5, "EmpId")
  55.         cmdEMPUpd.Parameters.Add("Name", System.Data.SqlDbType.VarChar, 50, "Name")
  56.         cmdEMPUpd.Parameters.Add("Age", System.Data.SqlDbType.Int, 5, "Age")
  57.         cmdEMPUpd.Parameters.Add("Salary", System.Data.SqlDbType.Float, 5, "Salary")
  58.         cmdEMPUpd.Parameters.Item("EmpId").SourceVersion = DataRowVersion.Original
  59.  
  60.         cmdEMPIns.Parameters.Add("Name", System.Data.SqlDbType.VarChar, 50, "Name")
  61.         cmdEMPIns.Parameters.Add("Age", System.Data.SqlDbType.Int, 5, "Age")
  62.         cmdEMPIns.Parameters.Add("Salary", System.Data.SqlDbType.Float, 5, "Salary")
  63.         cmdEMPIns.Parameters.Add("CityId", System.Data.SqlDbType.Int, 5, "CityId")
  64.  
  65.         cmdEMPDel.Parameters.Add("EmpId", System.Data.SqlDbType.Int, 5, "EmpId")
  66.         cmdEMPDel.Parameters.Item("EmpId").SourceVersion = DataRowVersion.Original
  67.  
  68.  
  69.  
  70.         DRel = New DataRelation("Rel_CT_Emp", Ds.Tables(0).Columns("CityId"), Ds.Tables(1).Columns("CityId"))
  71.  
  72.         Ds.Relations.Add(DRel)
  73.  
  74.  
  75.         'Dim foreignKey As ForeignKeyConstraint = DRel.ChildKeyConstraint()
  76.         'foreignKey.DeleteRule = Rule.Cascade
  77.         'foreignKey.UpdateRule = Rule.None
  78.         'foreignKey.AcceptRejectRule = AcceptRejectRule.Cascade
  79.  
  80.         bsCT.DataSource = Ds
  81.         bsCT.DataMember = "City"
  82.  
  83.         bsEMP.DataSource = Ds
  84.         bsEMP.DataMember = "Emp"
  85.  
  86.         Dim TxtCTNm As New TextBox
  87.         'TxtCTNm.Location = New System.Drawing.Point(10, 25)
  88.         TxtCTNm.Top = 50
  89.         TxtCTNm.Left = 25
  90.         TxtCTNm.Width = 200
  91.         TxtCTNm.DataBindings.Add("Text", bsCT, "CityName")
  92.         Me.Controls.Add(TxtCTNm)
  93.  
  94.         Dim NavigCT As New BindingNavigator
  95.         NavigCT.BindingSource = bsCT
  96.         NavigCT.AddStandardItems()
  97.         Me.Controls.Add(NavigCT)
  98.  
  99.  
  100.         Dim EmpGrd As New DataGrid
  101.  
  102.         EmpGrd.DataSource = bsCT
  103.         EmpGrd.DataMember = "Rel_CT_Emp"
  104.         EmpGrd.Top = 100
  105.         EmpGrd.Left = 25
  106.         EmpGrd.Height = 200
  107.         EmpGrd.Width = 500
  108.  
  109.         Me.Controls.Add(EmpGrd)
  110.     End Sub
  111.  
  112.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  113.         Me.Validate()
  114.         bsCT.EndEdit()
  115.         DaCT.Update(Ds, Ds.Tables(0).ToString)
  116.  
  117.  
  118.  
  119.  
  120.         bsEMP.EndEdit()
  121.         DaEMP.Update(Ds, Ds.Tables(1).ToString)
  122.  
  123.         Ds.Clear()
  124.         DaCT.Fill(Ds.Tables(0))
  125.         DaEMP.Fill(Ds.Tables(1))
  126.  
  127.  
  128.     End Sub
  129. 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".
Aug 8 '12 #1
Share this Question
Share on Google+
6 Replies


Frinavale
Expert Mod 5K+
P: 9,731
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
Aug 9 '12 #2

P: 4
Can U Plz Show Me Example Of It.
Means How To Save City First And Set Its CityId In Emp And Then Save Emp...
Aug 11 '12 #3

Frinavale
Expert Mod 5K+
P: 9,731
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
Aug 13 '12 #4

P: 4
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.
Aug 14 '12 #5

Frinavale
Expert Mod 5K+
P: 9,731
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
Aug 15 '12 #6

P: 4
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?
Sep 21 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.