How to Insert Record to Related Table in an ADO.NET Dataset?

[HardWorker]

Hi everybody,

I've been having a problem adding a record to a Child table (Products). The Parent table (Categories) has already data in it. The two tables have one-to-many relationship.
The problem in the code starts when I want to insert a new record to child table (Products) as below:

Dim objConn As SqlConnection
Dim da1 As SqlDataAdapter
Dim da2 As SqlDataAdapter
Dim ds As New DataSet
objConn = New SqlConnection(ConfigurationSettings.Appsettings("NorthwindConnection"))
da1 = New SqlDataAdapter("SELECT * FROM Categories", objConn)
da2 = New SqlDataAdapter("SELECT * FROM Products", objConn)
Try

objConn.Open()
' Add the tables to the DataSet
 da1.Fill( ds,"Categories")
da2.Fill(ds, "Products")
'Create the Data Relationship
ds.Relations.Add("ProdCat",ds.Tables("Categories").Columns("CategoryID"), _
ds.Tables("Products").Columns("CategoryID"))
 ' Add new record to the child table (Products )
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim Row1 As DataRow = ds.Tables("Products").Addnew()
Row1("ProductName") = trim(Textbox2.Text)
Row1("ProductDescription") = trim(Textbox3.Text)
ds.Tables("Products").Rows.Add(Row1)
da2.Update(ds, "Products")

Catch exc As SqlException
MessageBox.Show(ex.Message)
Finally
objConn.Dispose()
End Try

I read the following article, but I couldn't figure out how to insert record to a child table.

http://msdn.microsoft.com/en-us/libr...=vs.71%29.aspx

I would really appreciate if someone could help me out, or if there is any other way to solve this problem maybe by using a completly different code!
Thanks in advance

[jmcilhinney]

If your DataSet contains both the parent and child DataTables and a DataRelation between them then you cannot add a child record to the DataSet if it doesn't already contain the appropriate parent record. If you don't want that then don't add the parent DataTable and DataRelation to the DataSet. Either you want the relation enforced in the DataSet or not. You can't have it both ways.

To clarify, let's say that you have a parent record with ID 1 and you want to add a child record with ParentID 1. If you have just a lone DataTable in your app then you can add the child record to it with that ParentID without issue, then use an adapter to save that to the database. If you have a DataSet with a parent DataTable, a child DataTable and a DataRelation however, you cannot add a record to the child DataTable with a ParentID of 1 unless there is a record in the parent DataTable with an ID of 1. It doesn't matter whether it's in the database or not.

[HardWorker]

Thank you for your reply. I really appreciate your comment. I did try to use a single Dataset with a child table, but it did not work while it does work with the Parent table. However, I will try to do it again with a simplistic example in a new thread.

Cheers

[jmcilhinney]

Please don'tstar a new thread. Why would you start a new thread for exactly the same issue? I think you should start by explaining exactly what happens when you execute your code. If we know what the actual error is then we know exactly what we're looking for.

[HardWorker]

Finally it works. The child record can be added with a single Dataset.

Ok there is no need for a new thread. I just was hoping to close this thread and the new thread will include a clear example, especially I realised there was a few mistake in the code above. However, the problem has been solved right now and again thank you for your help.