SQL Date Problem

[Guest]

Hi
I have an ASP aplication that is used for colecting consumer complaint
information. THe info is stored in an SQL Server database. All seems to be
working OK apart from one thing. To record the date that the complaint came
in there is a field in the complaints table called DateReceived chihc has a
default value of (GetDate()). When i add a new record to the complaints
table manually in Visual Studio, the DateReceived field gets set to the date
and time the record was inserted. However when a record gets inserted using
the ASP.net aplication, the date is NULL. I realise i could probably
populate the date field programatically in ASP.net but i don't understand why
it doesn't work this way, this is how i have always done it before using
MySQL.

 

[Guest]

Defaults supply a value (with the INSERT and UPDATE statements) when no value
is supplied. But if your datatable is defined with a field of type date that
allows null, then the following SQL will insert Null even though there is a
default setup for that field:

INSERT INTO CUSTOMERTABLE(ORDER_DATE, ORDER_ID)
VALUES (NULL, 1)

The default will work if you were to omit passing the date value like this:

INSERT INTO CUSTOMERTABLE(ORDER_ID)
VALUES (1)

But if you wanted that the ORDER_DATE, for example, to be set to the current
system date whenever the user left that entry blank on your web form, then
you have to handle that in the INSERT T-SQL statement like this:

INSERT INTO CUSTOMERTABLE(ORDER_DATE, ORDER_ID)
SELECT ISNULL(@ORDER_DATE, GETDATE()), @ORDER_ID

 

[Guest]

Hi clickon,

How do you add a new record programatically?

Elton Wang