How to save the data in a datatable back into the database?

Question

0.00/5 (No votes)

See more:

I have filled my datatable with my sqladapter associated with my sqlcommand and sqlconnection. I have changed its contents by a textbox bound to one of its column. I want to save the data in it back into the database and I have done as the following but it doesn't work:

C#

mydatatable.Merge(mydatatable.GetChanges());
mydatatable.AcceptChanges();
mydataadapter.Update(mydatatable);

Of course, it doesn't check if there is any errors in the new changes but suppose there's not any error.
Could you please correct my code? Why doesn't it work? If using such as a datagrid, I can easily update the data using DataAdapter.Update method.
Thank you so much!

Posted 10-Sep-11 11:30am

King Boy

Updated 10-Sep-11 12:08pm

Simon Bang Terkildsen

v2

Add a Solution

Comments

Pradeep Shukla 10-Sep-11 17:42pm

The code seems to be correct, What is the exception you got?

Simon Bang Terkildsen 10-Sep-11 18:21pm

He doesn't get an exception, nothing is commited to the database, because he calls AcceptChanges.
Actually the first two lines in the three lines of posted code must be deleted. See my answer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon Bang Terkildsen · Accepted Answer · 2011-09-10T12:19:00

1) Merging changes from mydatatable into the very same mydatatable makes no sense, nothing will change, so it's not an error, just a waste of CPU cycles. So I suggest you remove mydatatable.Merge(mydatatable.GetChanges());

2) mydatatable.AcceptChanges(); calling AcceptChanges will set the state of all rows in the DataTable to Unchanged. So after this call there are no changes to commit to a database.
Remove mydatatable.AcceptChanges(); and your code should work, assuming you've set the UpdateCommand and InsertCommand of the SqlDataAdapter either yourself or by using OleDbCommandBuilder[^]. If you haven't then have a look at this:
How To Update a SQL Server Database by Using the SqlDataAdapter Object in Visual C# .NET[^]
It states it's for SQL Server but that's just a matter of difference in the SQLs the code is the same no matter which database you use.