Update SQL Server Table with existin DataSet or DataTable


I have an existing dataset that I already have from an execution of a stored procedure. I have converted that to a DataTable.

dtblTestTable = dsTestDataSet.Tables[0];

Open in new window

And I get the row DataRow from that DataTable.

datarowTESTRow = dtblTestTable.Rows[0];

Open in new window

Now I do some work with that information then I need to update it or basically update the SQL Server table that this table which is from the dataset. And I get the row DataRow from that DataTable.

I have something like this:

datarowTESTRow["TESTFIELD"] = "'Y'";

Open in new window

That doesn't update the table in the SQL Server database.

I want/need to use the existing Dataset or DataTable (which was from the DataSet) already pulled.

How can I perform that aspect?

Any help would be greatly appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you have to use BeginEdit in the datarow.
example here.
It is important to understand the disconnected nature of the DataSet and DataTable. Once you've populated them with data, they just exist in memory with absolutely no link back to your database, You can manipulate the data in them as you wish, but this only affects the in-memory copy of the data.

To update the database with these new changes, you either loop through the records in the DataTable and manually update, or use a DataAdapter which can update the database based on your DataTable. The DataTable keeps a RowState (Added, Modified, Deleted) which the DataAdapter will rely on to determine how to update the database.
davismAuthor Commented:
How could I create a SQLAdapter with a existing disconnected recordsset and Fill it the same?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to be in Edit mode to call AcceptChanges
davismAuthor Commented:
That didn't work.

I ended up creating a stored procedure.

But I'm still interested in options there. The Edit and AcceptChanges didn't work.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not running a SQL statement directly?
UPDATE TableName
WHERE ID = 123456

Open in new window

So instead of calling the stored procedure (that's a good solution by the way) you execute the SQL statement.
You must not call AcceptChanges before you update with the DataAdapter. Otherwise the update method will fail to determine what's new and what's modified,... because all RowStates will be set to Unmodified.

The DataAdapter.update method is nice in some scenarios because it processes the UPDATE, INSERT and DELETEs for you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davismAuthor Commented:
I went with the stored procedure.

Vitor, one does not put inline SQL into code like and UPDATE. That is bad practice and even Microsoft indicates that and for good reason.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.