C# .Net Fun - ASP.NET Source Code

C# .Net Fun - ASP.NET Source Code

Article Categories:
.Net Framework .Net Framework 1.1 ADO.NET ASP.NET Assemblies Asynchronous Methods C# Collections Delegates Events Pocket PC Regular Expressions Smart Devices SQL Server Visual C# Visual Studio .NET Visual Studio .NET 2003 Windows Forms XML

www.dotnetfun.com:
View Current Page's ASP.NET Source Code C# Reference C# Study Flash Cards Valuable Links Contact Us Site Map Mike's Portfolio

C# Reference Search:

Full Text Search:

Articles by Author:

Code Examples (zipped):

.Net Source Code for /Articles/WindowsForms/DataGridIdentityConflictSQLServer.aspx

This ASP.NET page was written by Mike Gonzalez.
<% @ Page Language="C#" Debug="True" %>
<% @ Register TagPrefix="DotNetFun" TagName="Header" Src="/Header.ascx" %>
<% @ Register TagPrefix="DotNetFun" TagName="Footer" Src="/Footer.ascx" %>
<script language="C#" runat="Server">
	private void Page_Load() {
		ltrCategories.Text = CtrlHeader.ArticleCategory1 + "|" + CtrlHeader.ArticleCategory2 + "|" + 
			CtrlHeader.ArticleCategory3 + "|" + CtrlHeader.ArticleCategory4;
		ltrCategories.Text = DotNetFun.Common.AddCommas(ltrCategories.Text, '|');
	}
</script>
<dotnetfun:header id="CtrlHeader" runat="Server" enableviewstate="False" allowrobotindex="true" pagetitle="DataGrid Identity Increment Conflicts with MS SQL Server" pagedescription="The identity incremental feature of the DataGrid control conflicts with MS SQL Server when hosting a DataSet with an identity column. Learn how to fix this." pagekeywords="datagrid, control, identity, conflicts, sql server, dataset" authorname="Mike Gonzalez" authordate="6-15-2003" articlecategory1="Windows Forms" articlecategory2="ADO.NET" articlecategory3="SQL Server" articlecategory4="Visual Studio .NET" articletitle="DataGrid Identity Increment Conflicts with MS SQL Server" articledescription="The identity incremental feature of the DataGrid control conflicts with MS SQL Server when hosting a DataSet with an identity column. Learn how to fix this." allowviewsource="true"/>
<h2>
	<%# CtrlHeader.ArticleTitle %>
</h2>

	<%# CtrlHeader.ArticleDescription %> 
	Category: <asp:literal id="ltrCategories" runat="Server" enableviewstate="False"/> 
	Posted <%# CtrlHeader.AuthorDate.ToShortDateString() %>, 
	By <%# CtrlHeader.AuthorName %> 

<hr noshade="true" size="5" color="<%# CtrlHeader.SiteDataSet.Tables[0].Rows[0][" navbackgroundcolor"] %>" />

This article may help you if you are building Windows Forms applications that meet all of the following criteria:
<ul>
	<li>An MS SQL Server (or MSDE) database table with an Identity column.</li>
	<li>A DataGrid control.</li>
	<li>A DataSet with an Identity column in a given table.</li>
	<li>The DataGrid uses the DataSet as it's DataSource.</li>
	<li>An SqlDataAdapter component used to manipulate the data stored in the MS SQL Server table.</li>
</ul>
Identity values maintained by a DataGrid hosting a DataSet object are local and do not derive from 
those identity values generated by the data store - in this case, MS SQL Server.
 
 
In the example above, if you click on the new record at the bottom of the DataGrid a new identity is 
automatically generated by the control for this record. If you don't end up creating the record by filling in 
data in the TestText column then navigating out of the record the next identity value is used for when 
you do end up creating a new record in the grid. This can be a problem since identity values generated by the 
DataGrid do not conincide with those generated by MS SQL Server.
 
 
If you use an SqlDataAdapter to update the data in the DataGrid then an INSERT statement is 
automatically generated for you by default. After the INSERT statement occurs a proceeding SELECT 
is used to retrieve a 'refreshed' version of the record from the MS SQL Server database. Now, if 
the SqlDataAdapter</b's>InsertCommand's UpdateRowSource is so to Both or 
FirstReturnedRecord the is is assumed that the values in the DataRow affected by the INSERT 
should be automatically updated - this does not happen!
 
It is up to you to update the affected DataRow yourself. A DataRow typically contains two 
versions of the row after a an INSERT statement is performed against it: Original and Current.
 
<img alt="DataGrid Identity Conflict with MS SQL Server" src="images/DataGridIentityConflict_3.gif" border="0"/>
 
As you can see by the image above, after hitting the Save button the SqlDataAdapter send the 
INSERT statement to MS SQL Server then retrieves an updated version of the record from 
the database via a SELECT statement. The correct ID is retrived from the database (26) but as you can 
see it doesn't appear in the DataGrid - the original value is still there! Eventhough the correctly 
retrieved identity value is set to the Current version of the DataRow it is not so in the 
DataGrid. 
 
The Current version contains the correct value. The Original version does not. The DataGrid 
cell objects bind to the Original version of the DataRow object.
 
So, the key is to update the DataRow's TestID column, in this example, to the correct value so 
we can see it in the DataGrid control. To do that we need to create an SqlDataAdapter 
RowUpdated event handler and update the Original version value to that of the 
Current value like this: 
<xmp style="font-size:10pt; background-color:#FFFFDD">
private void sqlDataAdapter1_RowUpdated(object sender, 
	System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
	if (e.Row.HasVersion(DataRowVersion.Original) && 
		e.Row.HasVersion(DataRowVersion.Current) &&
		e.Row.RowState != DataRowState.Deleted) 
	{
		dsDataSetIdentityProblemTest1.tblTest.FindByTestID(
			int.Parse(e.Row[0, DataRowVersion.Original].ToString())).TestID = 
			int.Parse(e.Row[0, DataRowVersion.Current].ToString());
	}
}
</xmp>
NOTE: in order for you to be able to update an identity value you must ensure that the identity 
column's readonly property is set to false. 
Once that is done, the DataGrid will reflect the correct identity values!

<dotnetfun:footer id="CtrlFooter" runat="Server" enableviewstate="False"/>