SQL BIT (not Null) - default Value

Posted on 2011-10-12
Last Modified: 2012-05-12

I have a .net application that has been runnning fine
I've needed to modify the database to allow for a new column
The new column is called ACTIVE and it's a bit

I want to set it's default Value to true
in the column properties i've added ((1)) against the "Default Value or BInding"  property

If i try and add a new record in management studio, it inserts a default value of true

if i try and run it with the application, it says: cannot insert NULL into column active

I really dont want to edit the code and was hoping the database default value would work?
what am i doing wrong?
Question by:websss
    LVL 21

    Expert Comment

    by:Dale Burrell
    You need to show us the code from your application that is attempting to insert the new record, its the application thats getting it wrong, not the database.
    LVL 39

    Expert Comment

    by:Pratima Pharande
    What is the query you are using to insert value ?

    IF you want default value to get inserted do not include that field in insert statement.

    If you are inserting like this

    Insert into tablename ( col1,col2 , ACTIVE) values ( 'a','b',Null)

    Do it like this only
    Insert into tablename ( col1,col2 ) values ( 'a','b')

    Author Comment


    I'll dig out the code

    Please note, I said that the .net application was running fine
    I then later added a new column in the DB, the code hasn't changed, so it doesn't insert anything for this column
    LVL 15

    Expert Comment

    by:Minh Võ Công

    Author Comment

    Hi Minhvc

    There are lots of contradicting statements on that link.....confusing
    LVL 21

    Accepted Solution

    It must be the application as when you test it direct the default works. Some apps use a data abstraction layer that query the database for the columns and then build the insert statement to match.

    Author Closing Comment

    Turns out someone altered the code but incorrectly, should have looked there first!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now