Post reply

Strange bit field behaviour

  • October 26, 2007 at 8:48 am

    #199505

    Hi

    I've been experiencing a strange phenomenon. Very recently, SQL Server 2000 tables with bit fields have been automatically changing to 'true' whenever text/number is entered into another field in the row???

    I created a test table with just an id, varchar field and bit field defaulted to (0) and nulls disallowed. I start a new row by typing into the varchar field, and as the first letter is typed, the bit field changes to 'true'. This only happens with new records.

    Does anybody know what is going on? I'd be grateful for any help or ideas.

    Many thanks

    Paul

  • October 26, 2007 at 10:01 am

    #745802

    two things i could think of checking are:

    check the table for a trigger...maybe a trigger is changing the bit field...

    check and make sure the field isn't a calculated field, and not a regular column witha default. if the columns was defined like ..mycolumn AS CASE WHEN textfield is null then o else 1 END or something

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 26, 2007 at 10:10 am

    #745807

    You state you created a new table and could reproduce the issue. Can you post the tables DDL so we can see what it looks like.

  • November 27, 2007 at 4:28 am

    #754462

    Dear Both

    Thanks for your replies here - and really apologise for not replying directly. I had to make a quick work-around and run. I've only just had the chance to get back to it.

    On further investigation, SQL Server was not at fault. It is something that the Access 2003 front end is doing to the table. There is no code or triggers involved. I just write a record into the table in Access, and it changes the field to True - goodness knows why. It even does it with a newly created table. I'll investigate further, then post on the Access Forum.

    Very many thanks

    Paul

  • November 27, 2007 at 8:16 am

    #754583

    Just to complete this. The cause of this problem was SP3 for Office 2003. Access.adp bit fields no longer work with SQL Server.

    Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply