Treating null field as zero for an update query

Question

I'm using the SQL Express 2010 query builder. I need to be able to increment a field.

In my behind code, I make a call such as

tableAdapter.IncrementLikeCount(id);

If I just use an increment, the like field can be null, so I want to either a. treat the null as zero in that field OR b. set to 1, if null, and increment otherwise.

The most current thing I tried is option b with the following code in the query builder:

UPDATE       [dbo].[myTable]
SET                [LikeCount] = IIF(ISNULL([LikeCount]), 1, LikeCount + 1)
WHERE        ([ID] = @Original_ID)

However, this does not work. The query builder keeps rewriting the expression inside the ISNULL without the square brackets and with a comma, as the following:

UPDATE       [dbo].[myTable]
SET                [LikeCount] = IIF(ISNULL(LikeCount,), 1, LikeCount + 1)
WHERE        ([ID] = @Original_ID)

Is there a clean, simple way of doing this?

Byrnes

Answer 1

The ISNULL statement needs a default to fall back to, like

ISNULL(LikeCount, 0)

where the 0 is the value that LikeCount becomes IF in fact it is null.

So, try

UPDATE       [dbo].[myTable]
SET          [LikeCount] = (ISNULL(LikeCount, 0) + 1)
WHERE        ([ID] = @Original_ID)

UPDATE

As to the query you posted in your comment:

UPDATE Documents 
SET docLikeCount = ISNULL(docLikeCount, 0) + 1
WHERE docID = @Original_docID

Reconstruction

I tried: UPDATE Documents SET docLikeCount = IIF(ISNULL(docLikeCount, 0), 0, docLikeCount + 1) WHERE (docID = @Original_docID) .... and also as ...UPDATE Documents SET docLikeCount = ISNULL(docLikeCount, 1, docLikeCount + 1) WHERE (docID = @Original_docID) ... still getting syntax error. – Byrnes