C# DataAdapter, DataTable -> not updating back to SQL Server table

Question

I wrote a simple test to check my dataAdapter code. I connect to the SQL Server database, fill a datatable, change a value in a row, and call da.Update(table) to send the changes back to SQL Server. The table has a primary key. Not at all sure why this isn't working...(see code)

connectionToSQL = new SqlConnection(SQLConnString);
connectionToSQL.Open();

var wktbl = new DataTable();

var cmd = new SqlCommand("SELECT * FROM TAGS$",connectionToSQL);                 
var da = new SqlDataAdapter(cmd);
var b = new SqlCommandBuilder(da);
da.Fill(wktbl);

wktbl.Rows[3][2] = "5";
wktbl.AcceptChanges();

da.Update(wktbl);

You can not update like that. Here after filling the values in the datatable, what ever the changes you do for datatable will purely relates only to it. And you again have to write some query to update similarly for selecting. And call ExecuteNonQuery() to execute the Update. — Sai Kalyan Kumar Akshinthala
– Sai Kalyan Kumar Akshinthala, Commented Nov 17, 2011 at 5:51
@skk: The SqlDataAdapter builds the UPDATE query automatically, and da.Fill should then use that to update the database. @user1051076 It's probably the call to AcceptChanges that breaks this, since it resets all the rowstates to unchanged... — Arjan Einbu
– Arjan Einbu, Commented Nov 17, 2011 at 6:24

Damir Arh · Accepted Answer · 2011-11-17 05:58:08Z

4

Just skip the call to AcceptChanges and the code should work fine. It marks all rows as unmodified so there's nothing left to do for your Update call.

answered Nov 17, 2011 at 5:58

Damir Arh

17.9k2 gold badges49 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Moe Sisko Over a year ago

i agree with Damir. You would generally call AcceptChanges after performing the update.

Arjan Einbu Over a year ago

@MoeSisko: You don't need to call AcceptChanges at all, since Update does that too.

cjjeeper Over a year ago

Ah, yes removing the accept changes worked. Thanks for info around the "modified state". this is actually a test for code that could not get to work previously, so I will work on that and see if I need some more help.

cjjeeper Over a year ago

Okay, I would like to expand my question to my original effort...I select * from an Excel spreadsheet into dt. I want to take those values and update the SQL table. (the SQL table exists because of a manual import to SQL from the original Excel spreadsheet, has a primary key set, user updates the excel sheet, I need to update the SQL values.) I am setting the RowState to modified in an effort to invoke the Update.

cjjeeper · Accepted Answer · 2011-11-17 17:07:02Z

0

Okay, I would like to expand my question to my original effort...I select * from an Excel spreadsheet into dt. I want to take those values and update the SQL table. (the SQL table exists because of a manual import to SQL from the original Excel spreadsheet, has a primary key set, user updates the excel sheet, I need to update the SQL values.) I am setting the RowState to modified in an effort to invoke the Update.

connectionToSQL = new SqlConnection(SQLConnString); connectionToSQL.Open();

            var cmd = new SqlCommand("SELECT * FROM TAGS$",connectionToSQL);                 
            var da = new SqlDataAdapter(cmd);
            var b = new SqlCommandBuilder(da);

            //dt.Rows[3][2] = "20";

            foreach (DataRow r in dt.Rows)
            {
                r.SetModified();
            }

            da.Update(dt);

answered Nov 17, 2011 at 17:07

cjjeeper

1033 gold badges4 silver badges13 bronze badges

2 Comments

Conrad Frix Over a year ago

"I would like to expand my question to my original effort". If you want to change your question you should edit your question or ask a new one. You added an answer and won't be received as you intended.

Conrad Frix Over a year ago

Glad I could help. I saw that you added the new question. You should probably delete this answer.

Collectives™ on Stack Overflow

C# DataAdapter, DataTable -> not updating back to SQL Server table

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related