what''s wrong with this?
-
what's wrong with this stored procedure when i test this in query analyzer; a message comes that
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_UserGroups_Groups'. The conflict occurred in database 'Security', table 'Groups', column 'groupId'. The statement has been terminated. The 'SP_InsertNewUser' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
CREATE PROCEDURE dbo.SP_InsertNewUser
@userId int,
@userName nvarchar(50),
@userPassword nvarchar(50),
@branchId nvarchar(50),
@groupId nvarchar(50),
@employeeId nvarchar(50),
@employeeName nvarchar(50),
@address1 nvarchar(50),
@address2 nvarchar(50),
@city nvarchar(50),
@state nvarchar(50),
@zip nvarchar(50),
@country nvarchar(50),
@mobile nvarchar(50),
@phone nvarchar(50),
@hireDate nvarchar(50)
as
Begin
INSERT INTO Users(userId, userName, userPassword, branchId)
VALUES(@userId, @userName, @userPassword, @branchId)
INSERT INTO Employee(employeeId, branchId, employeeName, userId, address1, address2, city, state, zip, country, mobile, phone, hireDate)
Values (@employeeId, @branchId, @employeeName, @userid, @address1, @address2, @city, @state, @zip, @country, @mobile, @phone, @hireDate)
INSERT INTO UserGroups(userId, branchId, groupId)
VALUES(@userId, @branchId, @groupId)
End
RETURN @@IDENTITY
GO
i'm also pasting diagram
-
Off the top of my head, I'd say you're trying to insert a row with a group id that doesn't yet exist in the table referenced by the foreign key constraint. Is there a 'groups' table somewhere that needs to have a row inserted first?
And then again, I might be wrong ...
David Webb -
no groupId exists in the table "Groups". it already has data inserted in it!!!
-
If the data is already there, then make sure that the order of your inserts is correct.
-
plz explain what do you mean by order of insertion?? do u mean order of attributes or order of tables?
if this is order of tables then plz tell me in what order insertion should happen??
and why am i getting this message?
The 'SP_InsertNewUser' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
-
any idea?
-
The constraint error that you are getting is telling you that you are trying to insert an invalid GroupID into your UserGroups table. The GroupID does not exist in your Groups table. I see that you say it does, but this is a very specific SQL Server error. It can only mean one thing. Check your data again.
The second message is telling you that the RETURN command must return an integer value. Since your INSERT failed, @@Identity is NULL. This is a warning message only. You may want to use the ISNULL function in combination with @@Identity to prevent this.
On another note, you should add some error-handling to your stored procedure. There are many posts on SSC regarding proper error-handling in stored procedures. Here is another good link from a SQL Server MVP:http://www.sommarskog.se/error-handling-II.html
-
ive tried to get this data through my application also. there is a drop down list which is getting data directly from database and i've to just select it but problem remains there
plz explain it with example
You may want to use the ISNULL function in combination with @@Identity to prevent this.
-
There are a lot of things here.
Whether the Groups table has data or not does not matter here since GroupId seems to be coming from the parameter list in your stored proc. so make sure there is a value in @GroupId. Check for
IF @GroupId IS NULL
RETURN -1
ELSE
BEGIN
--do all your inserts here.
END
also, What are you trying to return with @@IDENTITY?
On another note, pls try not to name your objects with "sp_..". You could see a slight peformance hit as SQL Server will try to look for the object under master DB first thinking its a system stored proc (sp_ means system related stored proc) and having not found there it will then look under the db you are querying against. So all this overhead could cause some performance hit.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Also, instead of using @@IDENTITY, you should use SCOPE_IDENTITY().
Check Books Online for descriptions of each.
--------------------
Colt 45 - the original point and click interface
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply