Strange 'Concurrency Exception' with VS2005 and SQL 2005

Hi All,

I am getting a very strange 'Concurrency Exception' updating records with VS
2005 (language VB) on a SQL 2005 Database Table. To handle data I use strong
typed Dataset with TableAdapters and the interaction with the SQL Database
is handled by the stored procedures automatically generated by the
TableAdapter configuration wizard.
Please note that I manually correct the bugs of the wizard (invalid data
types for @IsNull_Parameters).


In the table [IndexDetails] I have several nullable nvarchar fields (with
lenght varying from 50 to 3500). Since these fields are nullable, the values
for the new rows are set to NULL (using the method DataColumn.SetNull).
The problem arise when I want to modify (manually binding data from a row to
a win form) the values previously set to NULL. In fact a 'Concurrency
Exception' is thrown when I update a row containing the modified value of a
field (previously set to NULL).
To verify that it is not a real 'Concurrency Exception' I manually (using
the SQL Management Console) set to a generic 'N/A' value some of the NULL
fields. As result I was able to correctly update the rows (modifying the
'N/A' fields), but I was still getting the error when trying to change the
fields with NULL value.
Does anybody have an idea if I am doing something wrong managing the NULL
fields (I use only the Set_ColumnName_Null methods exposed by the strong
typed DataTable) or it is a problem of VS 2005 (there are known problems of
VS2005 handling NULL values, and this could be one of them).

The following are the definition of my table, the stored procedure set as
update command of the TableAdapter, and a sample of code I use to modify and
update
records.

Thank you very much for help.

========SQL Table==============

CREATE TABLE [dbo].[IndexDetails](
[Ticker] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[HasCurrentMembers] [bit] NOT NULL,
[HasMembersHistory] [bit] NOT NULL,
[MembersHistoryStartDate] [datetime] NULL,
[CurrentMembersCount] [int] NOT NULL,
[MonthlyReviewFrequency] [int] NOT NULL,
[LockedMembersNumber] [bit] NOT NULL,
[WeightingMethod] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[OfficialHistoryStartDate] [datetime] NULL,
[OfficialIssueDate] [datetime] NULL,
[BaseDate] [datetime] NULL,
[BaseValue] [decimal](18, 5) NULL,
[CalculationMethod] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[CurrencyConversionMethod] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[ParentIndexName] [nvarchar](250) COLLATE Latin1_General_CI_AS NULL,
[ParentIndexTicker] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[HasMultipleCurrencies] [bit] NOT NULL CONSTRAINT
[DF_IndexDetails_HasMultipleCurrencies] DEFAULT (0),
[StatedObjective] [nvarchar](3500) COLLATE Latin1_General_CI_AS NULL,
[SelectionProcess] [nvarchar](3500) COLLATE Latin1_General_CI_AS NULL,
[AdditionalNotes] [nvarchar](3500) COLLATE Latin1_General_CI_AS NULL,
[UpdatedBy] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_IndexDetails_UpdatedOn]
DEFAULT (getdate()),
CONSTRAINT [PK_IndexDetails] PRIMARY KEY CLUSTERED
(
[Ticker] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[IndexDetails] WITH NOCHECK ADD CONSTRAINT
[FK_IndexDetails_Anagrafica] FOREIGN KEY([Ticker])
REFERENCES [dbo].[Anagrafica] ([Ticker])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[IndexDetails] CHECK CONSTRAINT [FK_IndexDetails_Anagrafica]
GO
ALTER TABLE [dbo].[IndexDetails] WITH NOCHECK ADD CONSTRAINT
[FK_IndexDetails_Users] FOREIGN KEY([UpdatedBy])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[IndexDetails] CHECK CONSTRAINT [FK_IndexDetails_Users]


==========Update stored procedure

CREATE PROCEDURE dbo.sp_IndexDetails_UpdateCommand
(
@Ticker nvarchar(50),
@HasCurrentMembers bit,
@HasMembersHistory bit,
@MembersHistoryStartDate datetime,
@CurrentMembersCount int,
@MonthlyReviewFrequency int,
@LockedMembersNumber bit,
@WeightingMethod nvarchar(50),
@OfficialHistoryStartDate datetime,
@OfficialIssueDate datetime,
@BaseDate datetime,
@BaseValue decimal(18, 5),
@CalculationMethod nvarchar(250),
@CurrencyConversionMethod nvarchar(250),
@ParentIndexName nvarchar(250),
@ParentIndexTicker nvarchar(50),
@HasMultipleCurrencies bit,
@StatedObjective nvarchar(3500),
@SelectionProcess nvarchar(3500),
@AdditionalNotes nvarchar(3500),
@UpdatedBy uniqueidentifier,
@UpdatedOn datetime,
@Original_Ticker nvarchar(50),
@Original_HasCurrentMembers bit,
@Original_HasMembersHistory bit,
@IsNull_MembersHistoryStartDate int,
@Original_MembersHistoryStartDate datetime,
@Original_CurrentMembersCount int,
@Original_MonthlyReviewFrequency int,
@Original_LockedMembersNumber bit,
@IsNull_WeightingMethod int,
@Original_WeightingMethod nvarchar(50),
@IsNull_OfficialHistoryStartDate int,
@Original_OfficialHistoryStartDate datetime,
@IsNull_OfficialIssueDate int,
@Original_OfficialIssueDate datetime,
@IsNull_BaseDate int,
@Original_BaseDate datetime,
@IsNull_BaseValue int,
@Original_BaseValue decimal(18, 5),
@IsNull_CalculationMethod int,
@Original_CalculationMethod nvarchar(250),
@IsNull_CurrencyConversionMethod int,
@Original_CurrencyConversionMethod nvarchar(250),
@IsNull_ParentIndexName int,
@Original_ParentIndexName nvarchar(250),
@IsNull_ParentIndexTicker int,
@Original_ParentIndexTicker nvarchar(50),
@Original_HasMultipleCurrencies bit,
@IsNull_StatedObjective int,
@Original_StatedObjective nvarchar(3500),
@IsNull_SelectionProcess int,
@Original_SelectionProcess nvarchar(3500),
@IsNull_AdditionalNotes int,
@Original_AdditionalNotes nvarchar(3500),
@Original_UpdatedBy uniqueidentifier,
@Original_UpdatedOn datetime
)
AS
SET NOCOUNT OFF;
UPDATE [IndexDetails] SET [Ticker] = @Ticker, [HasCurrentMembers] =
@HasCurrentMembers, [HasMembersHistory] = @HasMembersHistory,
[MembersHistoryStartDate] = @MembersHistoryStartDate, [CurrentMembersCount] =
@CurrentMembersCount, [MonthlyReviewFrequency] = @MonthlyReviewFrequency,
[LockedMembersNumber] = @LockedMembersNumber, [WeightingMethod] =
@WeightingMethod, [OfficialHistoryStartDate] = @OfficialHistoryStartDate,
[OfficialIssueDate] = @OfficialIssueDate, [BaseDate] = @BaseDate, [BaseValue]
= @BaseValue, [CalculationMethod] = @CalculationMethod,
[CurrencyConversionMethod] = @CurrencyConversionMethod, [ParentIndexName] =
@ParentIndexName, [ParentIndexTicker] = @ParentIndexTicker,
[HasMultipleCurrencies] = @HasMultipleCurrencies, [StatedObjective] =
@StatedObjective, [SelectionProcess] = @SelectionProcess, [AdditionalNotes] =
@AdditionalNotes, [UpdatedBy] = @UpdatedBy, [UpdatedOn] = @UpdatedOn WHERE
(([Ticker] = @Original_Ticker) AND ([HasCurrentMembers] =
@Original_HasCurrentMembers) AND ([HasMembersHistory] =
@Original_HasMembersHistory) AND ((@IsNull_MembersHistoryStartDate = 1 AND
[MembersHistoryStartDate] IS NULL) OR ([MembersHistoryStartDate] =
@Original_MembersHistoryStartDate)) AND ([CurrentMembersCount] =
@Original_CurrentMembersCount) AND ([MonthlyReviewFrequency] =
@Original_MonthlyReviewFrequency) AND ([LockedMembersNumber] =
@Original_LockedMembersNumber) AND ((@IsNull_WeightingMethod = 1 AND
[WeightingMethod] IS NULL) OR ([WeightingMethod] =
@Original_WeightingMethod)) AND ((@IsNull_OfficialHistoryStartDate = 1 AND
[OfficialHistoryStartDate] IS NULL) OR ([OfficialHistoryStartDate] =
@Original_OfficialHistoryStartDate)) AND ((@IsNull_OfficialIssueDate = 1 AND
[OfficialIssueDate] IS NULL) OR ([OfficialIssueDate] =
@Original_OfficialIssueDate)) AND ((@IsNull_BaseDate = 1 AND [BaseDate] IS
NULL) OR ([BaseDate] = @Original_BaseDate)) AND ((@IsNull_BaseValue = 1 AND
[BaseValue] IS NULL) OR ([BaseValue] = @Original_BaseValue)) AND
((@IsNull_CalculationMethod = 1 AND [CalculationMethod] IS NULL) OR
([CalculationMethod] = @Original_CalculationMethod)) AND
((@IsNull_CurrencyConversionMethod = 1 AND [CurrencyConversionMethod] IS
NULL) OR ([CurrencyConversionMethod] = @Original_CurrencyConversionMethod))
AND ((@IsNull_ParentIndexName = 1 AND [ParentIndexName] IS NULL) OR
([ParentIndexName] = @Original_ParentIndexName)) AND
((@IsNull_ParentIndexTicker = 1 AND [ParentIndexTicker] IS NULL) OR
([ParentIndexTicker] = @Original_ParentIndexTicker)) AND
([HasMultipleCurrencies] = @Original_HasMultipleCurrencies) AND
((@IsNull_StatedObjective = 1 AND [StatedObjective] IS NULL) OR
([StatedObjective] = @Original_StatedObjective)) AND
((@IsNull_SelectionProcess = 1 AND [SelectionProcess] IS NULL) OR
([SelectionProcess] = @Original_SelectionProcess)) AND
((@IsNull_AdditionalNotes = 1 AND [AdditionalNotes] IS NULL) OR
([AdditionalNotes] = @Original_AdditionalNotes)) AND ([UpdatedBy] =
@Original_UpdatedBy) AND ([UpdatedOn] = @Original_UpdatedOn));

SELECT Ticker, HasCurrentMembers, HasMembersHistory,
MembersHistoryStartDate, CurrentMembersCount, MonthlyReviewFrequency,
LockedMembersNumber, WeightingMethod, OfficialHistoryStartDate,
OfficialIssueDate, BaseDate, BaseValue, CalculationMethod,
CurrencyConversionMethod, ParentIndexName, ParentIndexTicker,
HasMultipleCurrencies, StatedObjective, SelectionProcess, AdditionalNotes,
UpdatedBy, UpdatedOn FROM IndexDetails WHERE (Ticker = @Ticker)
GO


===========Sample VB code to manage records==========

With _IndexRow
.HasCurrentMembers = CurrentMembersChk.Checked
.HasMembersHistory = MembHistoryChk.Checked
If CurrentMembersChk.Checked = True Then
.CurrentMembersCount = MembCntUD.Value
Else
.CurrentMembersCount = 0
End If
If MembHistoryChk.Checked = True Then
.MembersHistoryStartDate = HistoryStartDTP.Value.Date
Else
.SetMembersHistoryStartDateNull()
End If
.MonthlyReviewFrequency = ReviewFreqUD.Value
.LockedMembersNumber = LockedNumberChk.Checked

If StartDTChk.Checked = False Then
.SetOfficialHistoryStartDateNull()
Else
.OfficialHistoryStartDate = OfficialStartDTP.Value.Date
End If
If IssueDTChk.Checked = False Then
.SetOfficialIssueDateNull()
Else
.OfficialIssueDate = OfficialIssueDTP.Value.Date
End If
If BaseDTChk.Checked = False Then
.SetBaseDateNull()
Else
.BaseDate = OfficialBaseDTP.Value.Date
End If
If IsNumeric(OfficialBaseValTxt.Text) = False Then
.SetBaseValueNull()
Else
.BaseValue = Double.Parse(OfficialBaseValTxt.Text)
End If

If CurrConversionTxt.Text.Trim <> "" Then
.CurrencyConversionMethod = CurrConversionTxt.Text
Else
.SetCurrencyConversionMethodNull()
End If
End With

=======Method to update Database==============

Public Function UpdateAnagraficaIndices() As Integer
Dim Res As Integer=0
With _MKXDS
.EnforceConstraints = False
Res += TA_AnagraficaIndices.Update(.IndexDetails)
'[THE EXCEPTION IS THROWN EXECUTING THE LINE ABOVE]


Res += TA_AnagraficaIndicesRevisions.Update(.IndexMembersReviews)
Res += TA_AnagraficaIndicesCurrencies.Update(.IndexCurrencies)
.EnforceConstraints = True
End With
Return Res
End Function