Solve Cannot Insert the Value NULL Into Column... Error
GridView is powerful control but sometimes you can stack with simple problem without solution. If you try to use column of type <asp:TemplateField> (template column) to edit database with some data source control like SqlDataSource, AccessDataSource, XMLDataSource, ObjectDataSource or SiteMapDataSource, it is possible to get an error like this:
Cannot insert the value NULL into column 'ColumnName', table 'DatabaseName.SchemaName.TableName'; column does not allow nulls. UPDATE fails. The statement has been terminated.
What is obvious, the column name doesn't allow NULL values and that updated value is not sent to data source control. There are few possible reasons for that.
User is submitted empty value in required field
Maybe you clicked to "update" link but some required TextBox is empty. In that case, you can use form validation or error handling to inform user that specific column is required.
In EditItemTemplate section, Eval() method is used instead of Bind()
Bind method works in both directions, for reading and writing. Eval method only send data to page, but you can't receive updated data back. So, if your EditItemTemplate of GridView looks similar to this:
<EditItemTemplate>
<b>Title:</b> <asp:TextBox ID="tbTitle" Text='<%# Eval("Title")%>' Width="100%" runat="server" /><br />
...
</EditItemTemplate>
Change it to use Bind method, like this:
<EditItemTemplate>
<b>Title:</b> <asp:TextBox ID="tbTitle" Text='<%# Bind("Title")%>' Width="100%" runat="server" /><br />
...
</EditItemTemplate>
DataBind() method is called before update
If you wrote something in required field and you used Bind method in EditItemTemplate, you can also check if you somewhere called GridView's DataBind method before updating. If GridView's DataBind() method is called, then it resets values so data source control gets NULLs instead of new values. So, check if you wrote some unnecessary DataBind() call, it is usually in Page_Load method.
So, I hope this article helped you to solve GridView update problem in minutes, instead of losing complete day to find out what caused this error like it happened to me :). Happy coding!
Related articles:
1. GridView Hidden Column Problem (And Two Common Solutions)
