Results 1 to 5 of 5

Thread: SQL Update on Bit field (Resolved)

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    SQL Update on Bit field (Resolved)

    I'm having a strange problem with an update query. I have an update line that includes a set statement like this:

    SET Injected = " & injected & "

    The column Injected is a bit field, and the variable injected is a boolean. In this query, I get an error saying the column name is invalid. I assume that I have to enclose the variable in some kind of punctuation, but I can't figure out what.

    I was able to work around it by interpreting the boolean as an integer (1/0) which works fine when put into the statement.

    I assume this is simple, but I haven't done it before, and haven't seen any usefull reference for it.
    Last edited by Shaggy Hiker; Oct 21st, 2004 at 01:59 PM.
    My usual boring signature: Nothing

  2. #2
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    There is no puntuation required for a Bit field, treat it as a numeric.

    A bit field can only be 0 or 1, in VB a boolean variable concatenated to a string will be the word True. So your SQL statement ends up being

    Set Injected = True

    SQL Server does not support the above statement and thinks the word True is another field. I think MS Access supports this statement but don't know for sure

    Anyways, use the following to generate the SQL Statement.

    Set Injected = " & ABS(Injected)

  3. #3
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    A bit field can only be 0 or 1
    Ooops - Bit fields can also be Null of course.

  4. #4

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988
    Bit fields cannot be NULL in T-SQL.

    Your answer surprises me. I expected that SQL would interpret the variable, as it does in an INSERT statement, but obviously not.

    Thanks.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    Bit fields cannot be NULL in T-SQL.
    Sure they can. Since version 7.0 I believe but maybe it was 2K.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width