SqlDataSource generates incorrect SQL statements

SqlDataSource generates incorrect SQL statements by .NET Master

Closed

as External Help for as External

Type:	Bug
ID:	93937
Opened:	6/16/2004 2:04:45 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When a SqlDataSource is configured to use a table which contains nullable fields, and optimistic concurrency is specified, incorrect SQL statements are generated for the Update and Delete commands. The SQL statements which are generated ignore the fact that the current/new value for the nullable fields could be null, and that in SQL NULL=NULL is always UNKNOWN. The only way to test for NULL is to use IS NULL. For a non-nullable field, this is not an issue, because one side of the equal sign will always be non-null.

This bug makes SqlDataSources totally fail if optimistic concurrency is specified. The only work-around is to either turn optimisitc concurrency off, or to hand code the Update and Delete commands. Another alternative is to SET ANSI_NULLS to OFF

Details (expand)

English

Community Technology Preview May 2004

Data

Windows XP Professional

See the attached SQL table definition, and aspx page containing a SqlDataSource configured to use the table.

Delete: "DELETE FROM [TestTable] WHERE [Id] = @Id AND [Name] = @Name AND [Description] = @Description"

Update: "UPDATE [TestTable] SET [Name] = @Name, [Description] = @Description WHERE [Id] = @Id AND [Name] = @original_Name AND [Description] = @original_Description"

Delete: "DELETE FROM [TestTable] WHERE [Id] = @Id AND [Name] = @Name AND ([Description] = @Description OR ([Description] IS NULL AND @Description IS NULL))"

Update: "UPDATE [TestTable] SET [Name] = @Name, [Description] = @Description WHERE [Id] = @Id AND [Name] = @original_Name AND ([Description] = @original_DescriptionDescription OR ([Description] IS NULL AND @original_Description IS NULL))"

1 attachments

Posted by Microsoft on 7/1/2004 at 3:15 PM

Thanks for reporting the issue. We are currently investigating and will revert back with a resolution.

The Web Platform And Tools Team

Posted by Microsoft on 7/6/2004 at 10:10 AM

This is a known issue and occurs because in most database systems, comparing NULL = NULL always returns false. CommandBuilder works around this by passing in extra parameters to the command indicating whether the original value of the column was null, thereby eliminating a null-to-null comparison. The plan is to fix it in v-next.

Workaround is to use a stored procedure or ObjectDataSource.