Insert to db from new strongly typed datarow

D

DannyT

Active member
Joined
Apr 14, 2005
Messages
36
I have a strongly typed dataset, called "Customers" for example.
My user chooses to create new customer, i have no existing dataset, so i create a new instance of my Customers dataset.
Code: 
dim dsCustomers as new Customers
and instantiate a new CustomersDataRow.
Code: 
dim drCustomer as new dsCustomers.Tables(0).NewRow()
At this point ive not Filled my ds as I know im creating a new record and dont want to make an unneccessary call to the db.
I populate my NewRow with the required data.

How do I now insert this row into the actual database? Im trying to pass it to my attempt at a data access layer (which currently has no reference to the original dataset). I seem to be stuck with a detached datarow and cant figure out how to commit it to the database?

Im sure this shouldnt be as difficult as im making it but i seem to be having a slow day so any help much appreciated.

Regards,

Dan
 
Okay this got the better of me so I resorted to filling the dataset, I then needed to maintain a reference to that dataset which I have done, now Im sending the dataset to a commitToDb function I realise I need a reference to the original dataadapter I filled it from?! Is this correct?

How do I update a dataset to the database using a new dataadapter? Im sorely missing a trick somewhere as using strongly datasets seems to be very tightly coupling my users interaction with the data itself.
 
Note: I use a SQL database most of the time, so I will use the SQL objects in my description.

You need to fill in the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter and then call the SqlDataAdapter.Update() method to update the database with the changes to your DataSet. If you dont want to manually create the SqlDataAdapter command properties, you can always have the SqlCommandBuilder create them for you.

Todd
 
ah thank you tfowler! I had explored that before but dismissed it, after your post I went back and realised where i was going wrong.

For the benefit of others, here is how to update a dataset with a new dataadapter:

Code: 
Public Function ExecuteUpdateDS(ByVal ds as DataSet, ByVal sql as String) as Integer
    Dim da As New OleDbDataAdapter create new dbAdapter
    da.SelectCommand = New OleDbCommand(sql, myConnection)  same select command that was used in the initial dataset population
    Dim autogen As New OleDbCommandBuilder(da)  CommandBuilder, auto generates Update, Insert and Delete commands
    Dim rowsUpdated as Integer= da.Update(ds, "Customers")  commit update
    ds.AcceptChanges()  accept modifications to dataset
    ExecuteUpdateDS = rowsUpdated
End Function
 
If you have a strongly typed dataset you can access this functionality in a more typesafe manor - something like....
Code: 
ds.Customers.Customer.AddCustomerRow(...)
 
Thanks plausibly, I am doing that, the above is just a generic function that I can throw any dataset at once updates have been made. Im currently evaluating strongly typed datasets vs custom classes.
 
Back
Top