Part 14 - Sqlcommandbuilder update not working

Suggested Videos
Part 11 - Dataset in asp.net
Part 12 - Caching dataset in asp.net
Part 13 - What is SqlCommandBuilder



This is continuation to Part 13. Please watch Part 13 from ADO.NET tutorial, before proceeding.

Two common reasons why SqlDataAdapter.Update does not work
1. SqlCommandBuilder object not associated with SqlDataAdapter object. Without this association SqlCommandBuilder object does not know how to generate INSERT, UPDATE and DELETE statements.
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
If the above line is not present in your code, SqlDataAdapter.Update() method will throw an exception - Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

2. The SelectCommand that is associated with SqlDataAdapter, does not return atleast one primary key or unique column. If this is the case you will get an exception - Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.



For  troubleshooting purposes, if you want to see the autogenerated INSERT, UPDATE, and DELETE T-SQL statements, use GetInsertCommand(), GetUpdateCommand() and GetDeleteCommand().
lblInsert.Text = builder.GetInsertCommand().CommandText;
lblUpdate.Text = builder.GetUpdateCommand().CommandText;
lblDelete.Text = builder.GetDeleteCommand().CommandText;