Why default value is not inserting in column when allow null is true?

Question

0.00/5 (No votes)

See more:

I have a table address in which a column called "faxno" which allows null and has default value as 0.When we insert a record programmatically using dataset we are getting the faxno value as "Null" but not as "0".

I didn't understand why this is happening?

Is there any possiblity to insert default value instead of Null?

Posted 16-Jan-13 22:47pm

hemantwithu

Updated 16-Jan-13 22:54pm

__TR__

v2

Add a Solution

Comments

[no name] 17-Jan-13 4:53am

are u assigning a value to the faxno? or just ignoring the column while inserting?

OneInNineMillion 17-Jan-13 4:53am

Please edit your OP to remove the code block. That would make it easier to read.

OneInNineMillion 17-Jan-13 5:00am

Maybe it interprets 0 as Null?
Sorry but I don't know nearly enough to properly answer your question.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Answer 1 · 2013-01-16T23:06:00

This is because the default value will be applied only when you don't insert any data into that column.
Lets look at the below table. Here FaxNo column has a default constraint with default value set to 0.

SQL

CREATE TABLE Address
(
	ID INT IDENTITY(1,1),
	[Name] VARCHAR(100),
	FaxNo VARCHAR(100) DEFAULT 0
)

Insert some data into the Address table. In the first insert statement we are inserting NULL into FaxNo column and in second insert statement we are only inserting the Name.

SQL

INSERT INTO Address ([Name], FaxNo) VALUES ('Bob', NULL)

INSERT INTO Address ([Name]) VALUES ('James')

SQL

SELECT * FROM Address

Querying the address table will give the following output.

SQL

1	Bob	NULL
2	James	0

milenalukic · Answer 2 · 2013-01-20T12:52:00

Solution 4

Hi,

Set the table not to allow nulls and it will take the default value when a value is not assigned on insert.

Posted 20-Jan-13 12:52pm

milenalukic

Comments

fjdiewornncalwe 21-Jan-13 10:03am

Yes, this would work if there wasn't already a bunch of data in there, but as a fax number, I really think the use of NULL is better than the use of 0 as it is more logically used in code that consumes this data. (+5 from me)

milenalukic 22-Jan-13 5:42am

Well in that case you have 2 choices:

If you want null values you do not require a default in which case remove your default of 0

If you prefer to have 0 instead of null, first update your data and set all faxno to 0 where they are null. Then remove the null check box and leave the default of 0.

The whole idea of having a default value is not to have nulls.

solanki.net · Answer 3 · 2013-01-16T22:59:00

Solution 2

u can try like that

SQL

create table tbDefault(id int,name numeric(10,2) constraint ct_default default 0.0 )
insert into tbDefault(id) values(1)
select * from tbDefault

i hope this will help u.

Posted 16-Jan-13 22:59pm

solanki.net

Sanjeev Alamuri · Answer 4 · 2013-01-16T22:57:00

Refer these links

http://stackoverflow.com/questions/92082/add-column-with-default-value-to-existing-table-in-sql-server[^]
http://stackoverflow.com/questions/6791675/how-to-set-a-default-value-for-an-existing-column[^]
http://blog.sqlauthority.com/2011/04/09/sql-server-add-new-column-with-default-value/[^]

Why default value is not inserting in column when allow null is true?

4 solutions

Solution 3

Solution 4

Solution 2

Solution 1

Add your solution here

Preview 0