ADO.NET tutorial for beginners

Part 14 Sqlcommandbuilder update not working

Text version of the video
http://csharp-video-tutorials.blogspot.com/2013/10/part-14-sqlcommandbuilder-update-not.html

Slides
http://csharp-video-tutorials.blogspot.com/2013/11/part-14-sqlcommandbuilder-update-not.html

All ADO .NET Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-adonet-video-tutorial.html

All ADO .NET Slides
http://csharp-video-tutorials.blogspot.com/p/adonet-slides.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

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