Solved

Saving Dataset changes back to database using vb.net

Posted on 2014-01-28
5
1,343 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
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
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 25

Expert Comment

by:Shaun Kline
Comment Utility
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
Comment Utility
Where do I call da.UpdateCommand?  From within the loop or after the loop when all of the records have been updated?
0
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
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
Comment Utility
got it!    Thanks for the help
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now