Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL BIT (not Null) - default Value

Posted on 2011-10-12
Medium Priority
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
ID: 36954456
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
ID: 36954459
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

ID: 36954474

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 15

Expert Comment

by:Minh Võ Công
ID: 36954482

Author Comment

ID: 36954498
Hi Minhvc

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

Accepted Solution

Dale Burrell earned 2000 total points
ID: 36954507
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

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

578 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