Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Saving Dataset changes back to database using vb.net

Posted on 2014-01-28
5
Medium Priority
?
1,587 Views
Last Modified: 2014-01-28
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.
0
Comment
Question by:Mike_Stevens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39814884
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
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39814892
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
 

Author Comment

by:Mike_Stevens
ID: 39814920
Where do I call da.UpdateCommand?  From within the loop or after the loop when all of the records have been updated?
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 39815010
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
 

Author Comment

by:Mike_Stevens
ID: 39815111
got it!    Thanks for the help
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question