Mike_Stevens
asked on
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_Value s.sConn)
Dim da As New SqlDataAdapter(sSQL, cn)
Dim ds As New DataSet
If cn.State = System.Data.ConnectionStat e.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_Birt h")) <----- 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.
Dim cn As New SqlConnection(Global_Value
Dim da As New SqlDataAdapter(sSQL, cn)
Dim ds As New DataSet
If cn.State = System.Data.ConnectionStat
cn.Open()
End If
Dim x_Age As String
da.Fill(ds, "search_result")
For Each row As DataRow In ds.Tables("search_result")
row("Age") = Calc_Age(row("Date_Of_Birt
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.
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
ASKER
Where do I call da.UpdateCommand? From within the loop or after the loop when all of the records have been updated?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
got it! Thanks for the help
It uses a SqlCommandBuilder object that builds the insert, update and delete SQL statements based on the query used to build the select statement.