CODEWITHSUNDEEP
c#sqldataadapter
cjjeeper
cjjeeper

Reputation: 103

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

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);

Upvotes: 0

Views: 6555

Answers (2)

cjjeeper
cjjeeper

Reputation: 103

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);

Upvotes: 0

Damir Arh
Damir Arh

Reputation: 17865

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.

Upvotes: 4

Related Questions