Update datagrid dataset

[peekay]

I have a datagrid set up from an inner join of three tables (orders,ordereditems and costcentres). When I wish to update the dataadapter with the modified dataset I use the dataadapter.updatecommand. I cannot get it right because it seems that I cannot update three tables simultaneously with an update command.
Can someone please help.
Thanks

PK Odendaal

http://www.odendaal.com

 

[chrissie1]

in this case you need to make a dataset with three tables make the relations between them and then use the commandbuilder to create the updatecommand. And now it will allow the update command to complete.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[peekay]

Thanks Christiaan

Do I need to fill these two datasets first before I update them ? and if so - how would I fill then and know that they are filled with the same data as is in the datagrid ?

PK Odendaal

http://www.odendaal.com

 

[MFritts253]

Peekay,
I'm assuming you mean you want to update your datasourse or dataset not your dataadapter. To update your datasource you would specify your da.Updatecommand then do and da.update(dataset). If you just want to update your dataset i would suggest the following. ds.GetChanges gets any of the updates,inserts, or deletes. once you call ds.AcceptChanges then your ds has the changes but if at some point you wanted to insert those records into your database calling acceptchanges right after getchanges isn't good. Because the dataAdapter uses the Rowstate of row in the dataset to determine what needs an Update,Insert, or delete. if you use these commands your datagrid and dataset will match. Fill me in a bit more with what your doing hopefully i can help.
Sincerely,
Michael

 

[peekay]

Hi Michael - thanks

I merely want to update my database with changes/additions to the datagrid. Here is my code that does not work :

daOI.UpdateCommand = New OleDbCommand(SQLupdate)
daOI.UpdateCommand.Connection = cnOrderSoft

daOI.InsertCommand = New OleDbCommand(SQLinsert)
daOI.InsertCommand.Connection = cnOrderSoft

daOI.DeleteCommand = New OleDbCommand(SQLdelete)
daOI.DeleteCommand.Connection = cnOrderSoft

daOI.Update(dsOI, "OrderedItems")

PK Odendaal

http://www.odendaal.com

 

[MFritts253]

daTrial.DeleteCommand = New SqlCommand
daTrial.DeleteCommand.CommandType = CommandType.StoredProcedure
daTrial.DeleteCommand.CommandText = "PKA_InsertPacking"
daTrial.DeleteCommand.Connection = conMain
daTrial.DeleteCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.DeleteCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.DeleteCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")

daTrial.InsertCommand = New SqlCommand
daTrial.InsertCommand.CommandType = CommandType.StoredProcedure
daTrial.InsertCommand.CommandText = "PKA_InsertPacking"
daTrial.InsertCommand.Connection = conMain
daTrial.InsertCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.InsertCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.InsertCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item") '.Value = dsRow.Item(0)
daTrial.InsertCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach") '.Value = dsRow.Item(2)
daTrial.InsertCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked") '.Value = dsRow.Item(3)



daTrial.UpdateCommand = New SqlCommand
daTrial.UpdateCommand.CommandType = CommandType.StoredProcedure
daTrial.UpdateCommand.CommandText = "PKA_InsertPacking"
daTrial.UpdateCommand.Connection = conMain
daTrial.UpdateCommand.Parameters.Add("@EmpID", SqlDbType.VarChar, 3).Value = EmpID
daTrial.UpdateCommand.Parameters.Add("@WorkDate", SqlDbType.DateTime, 8).Value = TDBDate.Value
daTrial.UpdateCommand.Parameters.Add("@Item", SqlDbType.VarChar, 35, "Item")
daTrial.UpdateCommand.Parameters.Add("@TimeEach", SqlDbType.Int, 4, "TimeEach")
daTrial.UpdateCommand.Parameters.Add("@QtyPacked", SqlDbType.Int, 4, "QtyPacked")


dsTrial.GetChanges()

If dsTrial.HasChanges Then
Try




daTrial.Update(dsTrial, "Trial")


Catch ex As Exception

dsTrial.RejectChanges()
MessageBox.Show(ex.Message & ex.HelpLink) '& "Rejected Changes")

End Try
End If
dsTrial.AcceptChanges()

this is how i handle this issue. Sometimes i get a few insert issues but at least you can see my theory on how i hook up stored procedures via the data adapter.
I'm not totally sure if you mean your inserting into 3 tables, but i know that part deals with you sql. Sorry if this doesn't help. But the da.update should execute whatever procedures are hooked up to that specific data adapter.

Sincerely,
Michael