IS NULL in WHERE Clause causes db reader 1 Exception

ml42

I am having trouble with a WHERE Clause in SQL DataCopmpare 7.1 that has worked in 6 and earlier:

The table I am comparing has a schema like this:

CREATE TABLE [UserPreferences]
(
[PreferenceID] int IDENTITY (1,1) PRIMARY KEY,
[UserID] int NULL,
[PrefName] varchar(50) NOT NULL,
[PrefValue] varchar(50) NOT NULL
)

Individual preferences are stored for each user with their ID in the UserID field, and Global preferences are stored with UserID = NULL

When I use the WHERE clause UserID IS NULL, I get an exception message next an informational balloon saying "Exception thrown by db reader 1"

This defect was introduced in 7.x. Does the Toolkit have this issue, too? (I have not updated my code that uses the toolkit to 7.x yet)

Michelle T

Thank you for reporting this! Unfortunately the Toolkit does have the same problem, yes. I've reproduced it and sent the relevant information to the developers, but as we've only just released a point release of Data Compare I'm not sure how soon a fix will be available.

staytrue2yourself

I have just started experiencing this issue after the latest minor release. i was able to work around the issue by wrapping the clause with paranthesis:
ml42, try this: (UserID IS NULL)
thanks,
Mike

ml42

Enclosing the were clause in paranthesis works great. Thanks.

RDAVIDSON

I am having problems with this as well.

I tried with () and without the ().

Here is the WHERE clause:
(order_number IN (73271, 71693, 73271, 72337, 99775, 99776, 72337, 71827, 71827, 637310))

petermdrew

I'm experiencing this problem with SQL Data Compare 7.2.

I'm trying the same thing as RDAVIDSON - my WHERE clause contains an IN () statement. Tried the brackets around the where clause and no luck.

Any advice?