Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

default value for a column does not work

Posted on 2010-09-14
5
Medium Priority
?
240 Views
Last Modified: 2012-05-10
hi experts, i have this code

CREATE TABLE [certifica].[Actividad](
      [Codigo] [int] IDENTITY(1,1) NOT NULL,
      [CodigoActividad] [bigint] NOT NULL,
....
      [UsuarioModificacion] [varchar](16) NULL,
      [FechaModificacion] [datetime2](0) NULL,
      [SituacionRegistro] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
      [CodigoActividad] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [certifica].[Actividad] ADD  DEFAULT (getdate()) FOR [FechaCreacion]
GO

ALTER TABLE [certifica].[Actividad] ADD  DEFAULT ('1') FOR [SituacionRegistro]
GO

Insert records right through a web application and does not work the default for the column x
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 33673212
default values only work if you do not specify a value in the field list when inserting.  If you explicitly insert NULL, then NULL will be inserted.
0
 
LVL 2

Expert Comment

by:IngCharlie
ID: 33673253
Change  the column FechaModificacion to datetime and set it to not NULL after this change set the default values
0
 
LVL 2

Expert Comment

by:IngCharlie
ID: 33673327
I ment [FechaCreacion] set it to not NULL and do not set its value in the insert SQL
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33673361
it doens't matter if it is null or not for new values.  Just do not supply a value on the insert.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33673397
>set it to not NULL after this change set the default values
It really doesn't matter whether the column is set to NULL or NOT NULL.  What matters is to not provide any value for the columns when you are inserting data unless you want the columns set to whatever values you provide.
So, since the FechaCreacion should probably be allowed to default to the current datetime in virtually every case, don't even include that column in your INSERT statement.  (However, you may want to have a special process for entering data when there has been a delay and you want the creation date to reflect when it should have been created . . . in which case, you will need a process that does provide the FechaCreacion.
 
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question