I'm having problem with Inserting record in stored procedure after deleting record, it says that null can't be inserted on primary key value. If I use same insert statement as query in sql server management, record is inserted with no problem. After manually
inserting record, inserting with stored procedure works. I'm using sql server 2008 r2 express.
it says that null can't be inserted on primary key value.
Form the description above, it seems like that you are inserting null on primary keys. From this link, we find that all columns defined within a PRIMARY KEY constraint must be defined as
NOT NULL. It is one of the main characteristic of a primary key. For details about it, please check
SQL by Design: How to Choose a Primary Key.
If it doesn't work, please post the corresponding code.
Best wishes,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
DELETE FROM RadniNalog
WHERE NalogId=@stariNalog
INSERT INTO RadniNalog ([Zaprimatelj], [BrojRadnogNaloga], [StrankaID], [AparatID], [OpisKvara], [Pribor])
VALUES (@zaprimatelj, @noviBrojNaloga, @strankaId, @aparatId, @opisKvara, @pribor)
@Catherine Shan - MSFT
Yes, I know I can't insert NULL value on primary key, it is defined as NOT NULL. As I said, after deleting, i can't insert record from stored procedure, if I use same insert code and run it as query in sql server managament, record is inserted with no problem.
After deleting, I have also tried to remove delete part from procedure and run procedure only for inserting and same problem.
DELETE FROM RadniNalog WHERE
NalogId=@stariNalog INSERT INTO
RadniNalog([Zaprimatelj],[BrojRadnogNaloga],[StrankaID],[AparatID],[OpisKvara],[Pribor]) VALUES
(@zaprimatelj,@noviBrojNaloga,@strankaId,@aparatId,@opisKvara,@pribor)
There are two possible reasons.
1. You are not inserting a value for NalogId. I assume that is the Primary Key, and cannot be NULL. Is it an IDENTITY field? Are you sure it's an IDENTITY field? Are you turning on IDENTITY_INSERT somewhere in the code before the insert?
AspRoMar
I have also tried to remove delete part from procedure and run procedure only for inserting and same problem.
2. You are not correctly passing the parameter values to the procedure, and are passing NULL for a column that cannot be NULL. Check the code that calls the procedure in a debugger and look at the values you are passing.
If this doesn't help solve your problem, post the entire code of your stored procedure, the complete structure of the table involved, and the code you use to call the stored procedure.
Thank you all for your help, I have noticed what is the problem with my code, after deleting I was using IDENT_CURRENT to fetch last identity value for using with parameter @noviBrojNaloga, stupid me:)
AspRoMar
Member
2 Points
8 Posts
Can't INSERT with Stored Procedure after DELETING record
Jan 09, 2013 06:38 AM|LINK
Hi,
I'm having problem with Inserting record in stored procedure after deleting record, it says that null can't be inserted on primary key value. If I use same insert statement as query in sql server management, record is inserted with no problem. After manually inserting record, inserting with stored procedure works. I'm using sql server 2008 r2 express.
Thanks in advance.
NadeemZee
Participant
942 Points
178 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 09, 2013 06:43 AM|LINK
can you post your code from delete to update or insert so i can check the problem..
Do FEAR (Face Everything And Rise)
Please mark as Answer if my post helps you..!
Catherine Sh...
All-Star
23382 Points
2490 Posts
Microsoft
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 10, 2013 08:34 AM|LINK
Hi,
Form the description above, it seems like that you are inserting null on primary keys. From this link, we find that all columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. It is one of the main characteristic of a primary key. For details about it, please check SQL by Design: How to Choose a Primary Key.
If it doesn't work, please post the corresponding code.
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store
AspRoMar
Member
2 Points
8 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 10, 2013 10:48 AM|LINK
@NadeemZee
Here is the code:
@Catherine Shan - MSFT
Yes, I know I can't insert NULL value on primary key, it is defined as NOT NULL. As I said, after deleting, i can't insert record from stored procedure, if I use same insert code and run it as query in sql server managament, record is inserted with no problem. After deleting, I have also tried to remove delete part from procedure and run procedure only for inserting and same problem.
Thanks
shivani.gupt...
Participant
883 Points
315 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 10, 2013 11:45 AM|LINK
May be "@zaprimatelj, @noviBrojNaloga, @strankaId, @aparatId, @opisKvara, @pribor"
any of primary key value dosent not getting anyting from the front end.. it would be coming null from front end..
hope this will help you
My Blog: http://shivaniaspnet.blogspot.in
santosh.pooj...
Member
297 Points
89 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 10, 2013 11:55 AM|LINK
Strange but have you test the stored procedure using exe yourstoredprocedure in query panel or from .net.
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 10, 2013 01:29 PM|LINK
There are two possible reasons.
1. You are not inserting a value for NalogId. I assume that is the Primary Key, and cannot be NULL. Is it an IDENTITY field? Are you sure it's an IDENTITY field? Are you turning on IDENTITY_INSERT somewhere in the code before the insert?
2. You are not correctly passing the parameter values to the procedure, and are passing NULL for a column that cannot be NULL. Check the code that calls the procedure in a debugger and look at the values you are passing.
If this doesn't help solve your problem, post the entire code of your stored procedure, the complete structure of the table involved, and the code you use to call the stored procedure.
AspRoMar
Member
2 Points
8 Posts
Re: Can't INSERT with Stored Procedure after DELETING record
Jan 13, 2013 05:40 PM|LINK
Thank you all for your help, I have noticed what is the problem with my code, after deleting I was using IDENT_CURRENT to fetch last identity value for using with parameter @noviBrojNaloga, stupid me:)