SqlDataAdapter Bug??

21 Oct 2006 7:04 PM

Jav

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

27 Oct 2006 6:38 PM

Bill

Jav,

I ran into this exact same senario today by accident. My plan is to not
allow nulls on the unique identifier rows but I'm concerned that the IsNull
parameters are using their related column data types instead of int or bit.
I found that the mapping in the data adapter command object is using a dbType
of int but in either case I think the only two values being passed are 1 or 0.

I'm working around the problem now by modifying the generated stored procs
but hope to find a better solution. I don't really have any other reason to
go back into the gererated stored proc other than to fix the IsNull problem
at the moment.

Have you found a fix or any better way to deal with this?

Show quote

"Jav" wrote:

> 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