Parent to multi child insert SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Parent to multi child insert SP

Author

Topic

f786
Starting Member

2 Posts

Posted - 2007-08-14 : 12:56:58

Hello,

I am wondering if there is a way to insert one parent record with multi child records in one transaction? I am using dataset to update my database. I want to use transaction so if one record insert fails all the transctions rollback.

Thanks

Your Input would be greatly appricated.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 13:14:12

Yes it is.
post sample data and table definition of what you are trying to do

Ashley Rhodes

f786
Starting Member

2 Posts

Posted - 2007-08-14 : 14:26:45

Protected Sub save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles save.Click
///This Row contains Data for Purchase Order///
Dim porders As New Purchase.Purchase_OrdersDataTable()
Dim po As Purchase.Purchase_OrdersRow = porders.NewPurchase_OrdersRow()
po.Customer_PO_ID = purchaseOrderno.Text
po.CV_ID = customer.SelectedValue
po.PO_App = False
po.Created = Now
porders.AddPurchase_OrdersRow(po) <<<<< Adds a Purchase Order Row to the datatable
Dim Adapter As PO_functions = New PO_functions()
Adapter.Update(porders) <<<<<< Saves the Purchase Order datatable to the database (Parent)

///These Rows contain Purchase Orders Items(Child)///
Dim items As New Purchase.PO_ItemsDataTable()
Dim i As Integer
Dim Counter As Integer = 0
Dim c As Array = hidden.Value.Split("+")
If Not c.Length = 0 Then
For i = 0 To (c.Length - 2) Step 4
'i = 0
Counter = (Counter + 1)
Dim quanity As String = c(i)
Dim style As String = c(i + 1)
Dim description As String = c(i + 2)
Dim price As String = c(i + 3)
Dim newitem As Purchase.PO_ItemsRow = items.NewPO_ItemsRow()
newitem.Item_ID = Counter
newitem.Customer_PO_ID = pono.Text
newitem.Item_Qty = quanity
newitem.Item_Sty = style
newitem.Item_Desc = description
newitem.Item_Price = Convert.ToDecimal(price)
items.AddPO_ItemsRow(newitem) <<<<<< Adds multiple Purchase Orders Rows to the PO_Items datatable
Next
End If
Dim itemsAdapter As New po_items()
itemsAdapter.Update(Items) <<<<<<< Saves the PO_Items datatable to the database (Child)

End Sub

I hope this will give you an idea what i want to do. If you need more info please let me know.
Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 15:15:22

ooops this is VB, I am not familiar with it. Try posting it on some VB forums.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources