Click here to Skip to main content
12,827,807 members (47,644 online)
Rate this:
Please Sign up or sign in to vote.
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 23:47pm
Updated 16-Jan-13 23:54pm
shabari7 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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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.
	[Name] VARCHAR(100),

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.
INSERT INTO Address ([Name], FaxNo) VALUES ('Bob', NULL)

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


Querying the address table will give the following output.
1	Bob	NULL
2	James	0
hemantwithu 17-Jan-13 9:18am
Can any one explain why this is happening ?
__TR__ 18-Jan-13 1:24am
As I mentioned in my answer, the default value will be added to the record if no other value is specified.
INSERT INTO Address ([Name], FaxNo) VALUES ('Bob', NULL)
In the above insert statement we are passing NULL to FaxNo, hence the default value is not added into the record.

INSERT INTO Address ([Name]) VALUES ('James')
Here we are not specifying any value for the column FaxNo, hence the default value was added.
Rate this: bad
Please Sign up or sign in to vote.

Solution 4


Set the table not to allow nulls and it will take the default value when a value is not assigned on insert.
Marcus Kramer 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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

u can try like that
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.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170326.1 | Last Updated 21 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100