Saving Dataset changes back to database using vb.net

I am trying to save changes that I have made to a dataset/data table back to the database.  I am populating the data table as follows:

  Dim cn As New SqlConnection(Global_Values.sConn)
  Dim da As New SqlDataAdapter(sSQL, cn)
  Dim ds As New DataSet

  If cn.State = System.Data.ConnectionState.Closed Then
           cn.Open()
  End If

  Dim x_Age As String

  da.Fill(ds, "search_result")

  For Each row As DataRow In ds.Tables("search_result").Rows
          row("Age") = Calc_Age(row("Date_Of_Birth")) <----- calls function to calc age
           row.AcceptChanges()
  Next row

 now how do I update the records in the database with the changes I just made?  Each row has a unique column named Rec_ID.  The column in the database that I want to update for each record is Age.

Ant help would be appreciated as I am new to this.
Mike_StevensAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
You should do it after the loop. However, do not use AcceptChanges, as it sets the DataRowState flag for each record as unchanged. This flag is what is used by both methods mentioned above to determine which rows should be updated in the underlying datasource.
0
 
Shaun KlineLead Software EngineerCommented:
Here is one method: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(v=vs.110).aspx

It uses a SqlCommandBuilder object that builds the insert, update and delete SQL statements based on the query used to build the select statement.
0
 
Shaun KlineLead Software EngineerCommented:
Another option is to create a SQLCommand object for your update statement and attach it to the UpdateCommand property of your SQLDataAdapter. Example here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand(v=vs.110).aspx
0
 
Mike_StevensAuthor Commented:
Where do I call da.UpdateCommand?  From within the loop or after the loop when all of the records have been updated?
0
 
Mike_StevensAuthor Commented:
got it!    Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.