I'm confused about "Null" fields and Default values

I have defined a table with a "DateCreated" field. I put "(getdate())"
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.