SqlDataAdapter.Update related tables [Answered]RSS

• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  SqlDataAdapter.Update related tables

  Aug 24, 2010 05:15 PM|mitja.GTI|LINK

  Hi!

  
  

  I would like to get 2 related tables from SQL Server to DataSet, insert new rows and save them back to SQL Db.
  

  
  

  Let's say i have 2 related tables as shown below...

  Table1	Table2
  test1ID (Primary Key)	test2ID (Primary Key)
  data	test1ID
  	data

  In Sql i have Diagram with relationship and Update Rule is set to Cascade

  
  

  I am trying to get them to dataset, insert "master" and "child" row and save them back to db.

  I tried different approaches (none of them workes) and this is one of them:

  Dim ds As New DataSet()
          Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NastanitveConnectionString").ConnectionString)
          Dim sql As String = "SELECT * from Table1;Select * from Table2;"
          Dim da As New SqlDataAdapter(sql, conn)
          Dim commandBuilder As New SqlCommandBuilder(da)
  
  
          da.Fill(ds)
          ds.Relations.Add(New DataRelation("ParentChild", ds.Tables(0).Columns("test1ID"), ds.Tables(1).Columns("test1ID")))
  
  
          Dim MasterRow As DataRow = ds.Tables(0).NewRow
          MasterRow("data") = "Parent"
  
  
  
  
          Dim ChildRow1 As DataRow = ds.Tables(1).NewRow
          ChildRow1.SetParentRow(MasterRow)
          ChildRow1("data2") = "Child"
  
  
          ds.Tables(0).Rows.Add(MasterRow)
          ds.Tables(1).Rows.Add(ChildRow1)
  
  
  
  
          da.Update(ds)

  
  Any help on how to update related tables with SqlDataAdapter would be very nice :)
  

  UPDATE dataset sqldataadapter TABLES related

  Please if the post helped you.
  mitja.gti | www.mitjagti.com
• • Reply
  nareshguree2...

  Contributor

  7260 Points

  1902 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 02:59 AM|nareshguree23@gmail.com|LINK

  check here. it might help you

  http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/16586b02-5bf0-40d6-ab01-28d899a9f020

   

• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 04:57 AM|mitja.GTI|LINK

  Thank you for your post.

  
  

  This is not the information i am looking for. I can successfully get data from SQL (Using one DataAdapter as shown above, or using multiple DataAdapters), i can create relation and display "master-details" correctly on page.

  My problem is that i do not know how to update related tables. I can update master table but the childtable i can not.
  

  
  

  Please if the post helped you.
  mitja.gti | www.mitjagti.com
• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 06:44 AM|mitja.GTI|LINK

  Now i got this far, that if i do this

  Dim ds As New DataSet()
  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
  Dim sql As String = "SELECT * from Table1;"
  Dim daMaster As New SqlDataAdapter(sql, conn)
  Dim sql1 As String = "SELECT * from Table2;"
  Dim dachild As New SqlDataAdapter(sql1, conn)
  Dim McommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(daMaster)
  Dim CcommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dachild)
  daMaster.Fill(ds, "T1")
   dachild.Fill(ds, "T2")
  Dim DR As DataRelation = New DataRelation("MasterChild", ds.Tables("T1").Columns("test1ID"), ds.Tables("T2").Columns("test1ID"), True)
  ds.Relations.Add(DR)
  ds.Tables(0).Columns(0).AutoIncrement = True
  ds.Tables(0).Columns(0).AutoIncrementSeed = -1
  ds.Tables(0).Columns(0).AutoIncrementStep = -1
  ds.Tables(1).Columns(0).AutoIncrement = True
  ds.Tables(1).Columns(0).AutoIncrementSeed = -1
  ds.Tables(1).Columns(0).AutoIncrementStep = -1
  
  
  Dim MasterRow As DataRow = ds.Tables(0).NewRow
  MasterRow("data") = "Master"
  ds.Tables(0).Rows.Add(MasterRow)
  Dim MasterRow1 As DataRow = ds.Tables(0).NewRow
  MasterRow1("data") = "Master1"
  ds.Tables(0).Rows.Add(MasterRow1)
  Dim ChildRow1 As DataRow = ds.Tables(1).NewRow
  ChildRow1.SetParentRow(MasterRow1)
  ChildRow1("data2") = "Child"
  ds.Tables(1).Rows.Add(ChildRow1)
  GridView1.DataSource = ds.Tables(0).DefaultView
  GridView1.DataBind()
  GridView2.DataSource = ds.Tables(1).DefaultView
  GridView2.DataBind()

  
  
  

  My Gridview data is displayed correctly like this:

  
  

  Table1		Table2
  TestID1	Data	TestID2	TestID1	Data2
  -1	Master	-1	-2	Child
  -2	Master1

  now if i try to update with

  
  

  daMaster.Update(ds, "T1")
  dachild.Update(ds, "T2")

  
  
  

  
  

  i get error:

  
  

  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table1". The conflict occurred in database "xxx", table "dbo.Table1", column 'test1ID'.The statement has been terminated.

  Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table1". The conflict occurred in database "xxx", table "dbo.Table1", column 'test1ID'.The statement has been terminated.

  Dim ds As New DataSet()        
  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)        
  Dim sql As String = "SELECT * from Table1;"        
  Dim daMaster As New SqlDataAdapter(sql, conn)        
  Dim sql1 As String = "SELECT * from Table2;"        
  Dim dachild As New SqlDataAdapter(sql1, conn)        
  Dim McommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(daMaster)        
  Dim CcommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dachild)        
  daMaster.MissingSchemaAction = MissingSchemaAction.AddWithKey        
  daMaster.Fill(ds, "T1")        
  dachild.MissingSchemaAction = MissingSchemaAction.AddWithKey        
  dachild.Fill(ds, "T2")        
  Dim DR As DataRelation = New DataRelation("MasterChild", ds.Tables("T1").Columns("test1ID"), ds.Tables("T2").Columns("test1ID"), True)        
  ds.Relations.Add(DR)       
   Dim MasterRow As DataRow = ds.Tables(0).NewRow        
  MasterRow("data") = "Master"        
  ds.Tables(0).Rows.Add(MasterRow)        
  Dim MasterRow1 As DataRow = ds.Tables(0).NewRow        
  MasterRow1("data") = "Master1"        
  ds.Tables(0).Rows.Add(MasterRow1)        
  Dim ChildRow1 As DataRow = ds.Tables(1).NewRow        
  ChildRow1.SetParentRow(MasterRow1)        
  ChildRow1("data2") = "Child"        
  ds.Tables(1).Rows.Add(ChildRow1)        
  GridView1.DataSource = ds.Tables(0).DefaultView       
  GridView1.DataBind()        
  GridView2.DataSource = ds.Tables(1).DefaultView        
  GridView2.DataBind()

  
  
  my table is displayed correctly like this:

  Table1		Table1
  TestID1	Data	TestID2	TestID1	Data2
  10	VW	1	10	Golf
  11	Renault	2	11	Clio
  12	Master	3	11	Laguna
  13	Master1	4	13	Child

  again if i update using

  daMaster.Update(ds, "T1")        
  dachild.Update(ds, "T2")

  
  
  

  Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table2_Table1". The conflict occurred in database "xxx", table "dbo.Table1", column 'test1ID'.The statement has been terminated.

  
  

  DOES ANYONE HAVE ANY CLUE WHAT AM I DOING WRONG?!

  Please if the post helped you.
  mitja.gti | www.mitjagti.com
• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 07:37 AM|mitja.GTI|LINK

  This is so weird!

  If i refresh page when using the second example in above poste, my data gets inserted correctly...

  i am so confused... What am i doing wrong? is this a bug?

  
  

  ANYONE?!
  

  Please if the post helped you.
  mitja.gti | www.mitjagti.com
• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 09:59 AM|mitja.GTI|LINK

  I figgured out why the error is displayed and why one time works and another doesnt.

  
  

  If my ID rows in master table are not in sequence like 10, 11, 12, 13, 14

  then the error is shown, after that everything works. And then again if i delete last row and try to insert another 1, same thing again. First it doesnt work and for second and so on it works.

  
  

  Can someone PLEASE help me with this one??
  

  
  

  Please if the post helped you.
  mitja.gti | www.mitjagti.com
• • Reply
  mitja.GTI

  Contributor

  7224 Points

  2025 Posts

  Re: SqlDataAdapter.Update related tables

  Aug 25, 2010 12:39 PM|mitja.GTI|LINK

  OK. 4 Days and i managed to do this by myselfe.

  Here is the working code:

  
  

  Imports System.Data.SqlClient
  Imports System.Data
  
  
  Partial Class test3
      Inherits Language
      Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
      Dim sql1 As String = "SELECT * from Table2;"
      Dim dachild As New SqlDataAdapter(sql1, conn)
      Dim CcommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(dachild)
      Dim sql As String = "SELECT * from Table1;"
      Dim daMaster As New SqlDataAdapter(Sql, conn)
      Dim McommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(daMaster)
  
  
  
  
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          Dim ds As New DataSet
          If Page.IsPostBack = True Then
              ds = ViewState("ds")
          Else
              daMaster.MissingSchemaAction = MissingSchemaAction.AddWithKey
              daMaster.Fill(ds, "T1")
              dachild.MissingSchemaAction = MissingSchemaAction.AddWithKey
              dachild.Fill(ds, "T2")
  
  
              Dim DR As DataRelation = New DataRelation("MasterChild", ds.Tables("T1").Columns("test1ID"), ds.Tables("T2").Columns("test1ID"), False)
              ds.Relations.Add(DR)
  
  
              ViewState("ds") = ds
          End If
  
  
          GridView1.DataSource = ds.Tables(0).DefaultView
          GridView1.DataBind()
          GridView2.DataSource = ds.Tables(1).DefaultView
          GridView2.DataBind()
  
  
      End Sub
  
  
      Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
          Dim ds As DataSet = ViewState("ds")
          Dim MasterRow1 As DataRow = ds.Tables(0).NewRow
          MasterRow1("data") = TextBox1.Text
          ds.Tables(0).Rows.Add(MasterRow1)
  
  
          daMaster.Update(ds, "T1")
  
  
          ViewState("ds") = ds
  
  
          GridView1.DataSource = ds.Tables(0).DefaultView
          GridView1.DataBind()
  
  
  
  
      End Sub
  
  
      Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
          Dim ds As DataSet = ViewState("ds")
          Dim masterrow As DataRow = ds.Tables(0).Rows(GridView1.SelectedIndex)
          Dim ChildRow1 As DataRow = ds.Tables(1).NewRow
          ChildRow1.SetParentRow(masterrow)
  
  
          ChildRow1("data2") = TextBox2.Text
          ds.Tables(1).Rows.Add(ChildRow1)
  
  
          dachild.Update(ds, "T2")
  
  
          ViewState("ds") = ds
  
  
  
  
          GridView2.DataSource = ds.Tables(1).DefaultView
          GridView2.DataBind()
      End Sub

  
  
  

  Please if the post helped you.
  mitja.gti | www.mitjagti.com