Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 16-Jan-13 22:54pm
v2
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.

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
 
Share this answer
 
Comments
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.
Hi,

Set the table not to allow nulls and it will take the default value when a value is not assigned on insert.
 
Share this answer
 
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.
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900