Click here to Skip to main content
Rate this: bad
good
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 22:47pm
Edited 16-Jan-13 22:54pm
__TR__28.9K
v2
Comments
shabari7 at 17-Jan-13 4:53am
   
are u assigning a value to the faxno? or just ignoring the column while inserting?
OneInNineMillion at 17-Jan-13 4:53am
   
Please edit your OP to remove the code block. That would make it easier to read.
OneInNineMillion at 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
good
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.
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.
INSERT INTO Address ([Name], FaxNo) VALUES ('Bob', NULL)
 
INSERT INTO Address ([Name]) VALUES ('James')
SELECT * FROM Address
Querying the address table will give the following output.
1	Bob	NULL
2	James	0
  Permalink  
Comments
hemantwithu at 17-Jan-13 9:18am
   
Can any one explain why this is happening ?
__TR__ at 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
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Marcus Kramer at 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 at 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
good
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.
  Permalink  

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

  Print Answers RSS
0 George Jonsson 359
1 Sergey Alexandrovich Kryukov 355
2 CPallini 335
3 BillWoodruff 324
4 OriginalGriff 237
0 OriginalGriff 5,050
1 CPallini 4,225
2 Sergey Alexandrovich Kryukov 3,639
3 George Jonsson 2,911
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 21 Jan 2013
Copyright © CodeProject, 1999-2014
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