G
Guest
When using VS2005 SqlDataAdapter to generate StoredProcs for SQL Server 2000,
I have noticed a problem that can only be a bug. I would not be surprised to
know that it is already known.
In a nutshell, if the underlying table has any uniqueidentifier columns that
are marked "Allow Nulls", the dataAdapter code generation will fail for the
Update and Delete methods.
The parameters for the Update and Delete methods include such lines as:
@IsNull_user_id uniqueidentifier,
@Original_user_id uniqueidentifier,
@IsNull_lastuseddate smalldatetime,
@Original_lastuseddate smalldatetime,
These obviously only appear for columns that are marked "Allow Nulls" in the
underlying Table. In the example above, while the smalldatetime parameters
will cause no difficulty, the same is not true for the uniqueidentifier
parameters. Both Update and Delete methods use these parameters in the
WHERE clause as follows:
AND ((@IsNull_user_id = 1 AND [user_id] IS NULL) OR ([user_id] =
@Original_user_id))
AND ((@IsNull_lastuseddate = 1 AND [lastuseddate] IS NULL) OR
([lastuseddate] = @Original_lastuseddate))
It is the expression @IsNull_user_id = 1 (user_id is UniqueIdentifier) that
causes the problem whereas the equivalent @IsNull_lastuseddate = 1
(lastuseddate is smalldatetime) works fine.
The error in VS simply says that Update method could not be generated. If
you user the Query Analyser to run the generated script, you see the actual
error which reads:
Server: Msg 206, Level 16, State 2, Procedure Ins_InsEpis_Delete, Line 142
Operand type clash: uniqueidentifier is incompatible with tinyint
Jav
I have noticed a problem that can only be a bug. I would not be surprised to
know that it is already known.
In a nutshell, if the underlying table has any uniqueidentifier columns that
are marked "Allow Nulls", the dataAdapter code generation will fail for the
Update and Delete methods.
The parameters for the Update and Delete methods include such lines as:
@IsNull_user_id uniqueidentifier,
@Original_user_id uniqueidentifier,
@IsNull_lastuseddate smalldatetime,
@Original_lastuseddate smalldatetime,
These obviously only appear for columns that are marked "Allow Nulls" in the
underlying Table. In the example above, while the smalldatetime parameters
will cause no difficulty, the same is not true for the uniqueidentifier
parameters. Both Update and Delete methods use these parameters in the
WHERE clause as follows:
AND ((@IsNull_user_id = 1 AND [user_id] IS NULL) OR ([user_id] =
@Original_user_id))
AND ((@IsNull_lastuseddate = 1 AND [lastuseddate] IS NULL) OR
([lastuseddate] = @Original_lastuseddate))
It is the expression @IsNull_user_id = 1 (user_id is UniqueIdentifier) that
causes the problem whereas the equivalent @IsNull_lastuseddate = 1
(lastuseddate is smalldatetime) works fine.
The error in VS simply says that Update method could not be generated. If
you user the Query Analyser to run the generated script, you see the actual
error which reads:
Server: Msg 206, Level 16, State 2, Procedure Ins_InsEpis_Delete, Line 142
Operand type clash: uniqueidentifier is incompatible with tinyint
Jav