none
stored procedure with output parameter RRS feed

  • Question

  • The INSERT statement conflicted with the FOREIGN KEY constraint this error comes  in "FK_student_general" and "FK_general_class" please resolve this ...

    alter procedure [dbo].[sp_regestration]
    @class_id int,
    @class int,
    @sec text,
    @general_id int,
    @name text,
    @email varchar(50),
    @age int,
    @contact int,
    @id_class int,
    @id_location int,
    @location_id int,
    @country text,
    @city text,
    @parent_name text,
    @gen_id int,
    @student_id int out
    AS
    BEGIN
    insert into student values (@student_id,@parent_name,@gen_id);
    select @student_id=SCOPE_IDENTITY();
    insert into general values (@general_id,@name,@email,@age,@contact,@id_class,@id_location);
    select @general_id=SCOPE_IDENTITY();
    insert into location values(@location_id,@country,@city);
    insert into class values (@class_id,@class,@sec);
    END

    execute [dbo].[sp_regestration]
    @class_id=15,
    @class=4,
    @sec='b',
    @general_id=15,
    @name='ramsha',
    @email='shahwaizhasan@gmail.ocm',
    @age=44,
    @contact=1231223,
    @id_class=15,
    @id_location=15,
    @location_id=15,
    @country='pakistan',
    @city='karachi',
    @parent_name='farhks',
    @gen_id=15,
    @student_id=15

    Friday, May 6, 2016 4:19 PM

Answers

  • Your code makes very little sense. Why you're trying to insert @student_id value if that column is an identity column?

    Also, please always specify columns in the INSERT command, e.g.

    insert into Student (parent_name, gen_id)

    values (@parent_name, @gen_id);

    set @student_id = SCOPE_IDENTITY(); // ID of just inserted student


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 6, 2016 5:30 PM
    Moderator

All replies

  • The INSERT statement conflicted with the FOREIGN KEY constraint this error comes  in "FK_student_general" and "FK_general_class"

    Hello,

    A clear error message; you try to insert a foreign key value, which don't exists in the referenced table as primary key.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 6, 2016 4:53 PM
    Moderator
  • Your code makes very little sense. Why you're trying to insert @student_id value if that column is an identity column?

    Also, please always specify columns in the INSERT command, e.g.

    insert into Student (parent_name, gen_id)

    values (@parent_name, @gen_id);

    set @student_id = SCOPE_IDENTITY(); // ID of just inserted student


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 6, 2016 5:30 PM
    Moderator
  • okay thanks i try this
    Friday, May 6, 2016 7:30 PM
  • yes okay .
    Friday, May 6, 2016 7:30 PM