can't insert into ...because of nulls problme!

Hello,
I am trying to insert some records into a table from an SP but get this
message:
Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6
Cannot insert the value NULL into column 'CurEntryDate', table
'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I am inserting into only a few fields. How can I negotiate this?
Thanks,
RichYou cannot, unless you pass a value for every non-nullable column in the
table.
The column CurEntryDate sounds like a column that captures the insertion
time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
in the INSERT statement. If being passed through a stored procedure, declare
a local variable that has the current date/time value and pass it as an
input parameter.
You may also consult your database designer and see if he can set a default
on this column to eliminate the need to pass this value in every INSERT
statement.
Anith|||Thanks. What I did was to temporarily allow nulls in these fields. and the
n
restored the table back. Is this practice OK?
"Anith Sen" wrote:

> You cannot, unless you pass a value for every non-nullable column in the
> table.
> The column CurEntryDate sounds like a column that captures the insertion
> time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value
> in the INSERT statement. If being passed through a stored procedure, decla
re
> a local variable that has the current date/time value and pass it as an
> input parameter.
> You may also consult your database designer and see if he can set a defaul
t
> on this column to eliminate the need to pass this value in every INSERT
> statement.
> --
> Anith
>
>|||If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that
without any success. My guess is that if a constraint was ever defined it wa
s
defined for a reason.
If you've now gone and inserted data through a backdoor, this just might
cause problems for you later on - i.e. with queries written against
non-nullable columns which now contain null values.
ML
http://milambda.blogspot.com/|||On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:

>Thanks. What I did was to temporarily allow nulls in these fields. and th
en
>restored the table back. Is this practice OK?
Hi Rich,
No, it definitely isn't.
There is probably a reason why this column is defined to not allow
NULLs. If you overide that restriction, you're probably entering invalid
data and endagnering the integrity of your database.
Would you also temporarly disable a CHECK constraint that allows only
'Male' and 'Female' in the gender column to enable the value 'Water' to
be entered there?
Hugo Kornelis, SQL Server MVP|||> Thanks. What I did was to temporarily allow nulls in these fields. and
> then
> restored the table back. Is this practice OK?
The practice doesn't make sense. Don't you think the NOT NULL constraints
are there for a reason? Why bother having them at all if you're just going
to remove them every time you insert data? Isn't this kind of like not
wearing your seatbelt?|||Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told
,
I am not at the guru level. I am going to have to pay my dues with a lot of
heart ache. The task was one of these add-hoc things, and I just started on
assuming the duties of this project which was already in motion for a while.
So I rushed! then I slowed down and created fake data ('' for varchars and
an agreed upon fake date for date fields) to accommodate the constraints.
That did the trick. The first reply said I can't insert records if nulls ar
e
not allowed unles I have some values. So I created the values. Anyway, I
really appreciate the replies. Even though I was suggesting ideas of an
amateur nature these replies will help to keep me on the proper course.
Rich
"Rich" wrote:

> Hello,
> I am trying to insert some records into a table from an SP but get this
> message:
> Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line
6
> Cannot insert the value NULL into column 'CurEntryDate', table
> 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I am inserting into only a few fields. How can I negotiate this?
> Thanks,
> Rich|||In case of lacking documentation maybe one of the users can help. And there'
s
got to be at least some shread of documentation. A leaflet, a greasy paper
napkin, something.
ML
http://milambda.blogspot.com/|||Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask
questions like this when we start out.
An amateur would have made the change and not bothered asking anyone if it
made sense.
One additional note though...
If you had removed the constraint in order to insert the null values, you
would not have been able to put the constraint back without first changing
the null values to actual values. If your data does not follow the rules of
the constraint you are trying to make, then the database will not let you
create the constraint.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@.microsoft.com...
> Thank you all for your input on this matter. I undid what I was going to
> try. Anyway, No, there is no documentation. I am on my own! Truth be
told,
> I am not at the guru level. I am going to have to pay my dues with a lot
of
> heart ache. The task was one of these add-hoc things, and I just started
on
> assuming the duties of this project which was already in motion for a
while.
> So I rushed! then I slowed down and created fake data ('' for varchars
and
> an agreed upon fake date for date fields) to accommodate the constraints.
> That did the trick. The first reply said I can't insert records if nulls
are
> not allowed unles I have some values. So I created the values. Anyway, I
> really appreciate the replies. Even though I was suggesting ideas of an
> amateur nature these replies will help to keep me on the proper course.
> Rich
> "Rich" wrote:
>
Line 6|||> Your idea was reasonable for someone new to database programming. You had
> some reservations and asked the right folks a question. We all have to as
k
> questions like this when we start out.
> An amateur would have made the change and not bothered asking anyone if it
> made sense.
I strongly agree. No one is born smart.
ML
http://milambda.blogspot.com/