INSERT statement conflict with foreign key

Question

I have 2 tables.'Member' and 'ForgotPassword' table. My problem that i am facing is:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ForgotPassword_User". The conflict occurred in database "MyDatabase", table "dbo.Member", column 'userID'. The

1st Table is the 'Member' Table: userID is the primary key and I set it autoincrement.

2nd Table is the 'ForgotPassword' Table: I set userID as not a primary key but foreign key as reference to the 'Member' table and I set it as auto increment.

What I want is the

userID on Member table to be inserted to the ForgotPassword Table

when it runs the second sql line.Any of you guys know what is the problem?

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Collections;



namespace DataAccessLayer
{
    public class Member3
    {
        string name;
        string email;
        string gender;
        string dateOfBirth;
        string nationality;
        string race;
        string address;
        string postalCode;
        string mobileNo;
        string password;
        string dateRegistered;
        string securityQuestion;
        string securityAnswer;

        public Member3(string name, string email, string gender, string dateOfBirth, string nationality, string race, string address, string postalCode, string mobileNo, string password, string dateRegistered, string securityQuestion, string securityAnswer)
        {
            this.name = name;
            this.email = email;
            this.gender = gender;
            this.dateOfBirth = dateOfBirth;
            this.nationality = nationality;
            this.race = race;
            this.address = address;
            this.postalCode = postalCode;
            this.mobileNo = mobileNo;
            this.password = password;
            this.dateRegistered = dateRegistered;
            this.securityQuestion = securityQuestion;
            this.securityAnswer = securityAnswer;
        }

        public string registerMember()
        {
            string status = "";
            string ConnString = Properties.Settings.Default.ConnectionString;

             string sql = "INSERT INTO Member (userName,Email,Gender,DateOfBirth,Nationality,Race,Address,PostalCode,MobileNumber,Password,DateRegistered) VALUES(@name, @email, @gender, @dateOfBirth, @nationality, @race, @address, @postalCode, @mobileNo, @password, @dateRegistered) SELECT IDENT_CURRENT('Member.userID) AS NewUser_ID";

            string sql2 = "INSERT INTO ForgotPassword (userID,SecurityQuestion, SecurityAnswer) VALUES(@NewUser_ID, @securityQuestion, @securityAnswer)";



                using (SqlConnection cn = new SqlConnection(ConnString))
                {
                   
                    SqlCommand cmd;

                    cmd = new SqlCommand((sql), cn);

                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@email", email);
                    cmd.Parameters.AddWithValue("@gender", gender);
                    cmd.Parameters.AddWithValue("@dateOfBirth", dateOfBirth);
                    cmd.Parameters.AddWithValue("@nationality", nationality);
                    cmd.Parameters.AddWithValue("@race", race);
                    cmd.Parameters.AddWithValue("@address", address);
                    cmd.Parameters.AddWithValue("@postalCode", postalCode);
                    cmd.Parameters.AddWithValue("@mobileNo", mobileNo);
                    cmd.Parameters.AddWithValue("@password", password);
                    cmd.Parameters.AddWithValue("@dateRegistered", dateRegistered);
                    
                   



                    try
                    {
                        cn.Open();
                        cmd.ExecuteNonQuery();
                        


                    }
                    catch (Exception ex)
                   
                    {
                       
                         status = ex.Message;
                         throw;
                         
                    }



                        cmd = new SqlCommand(sql2,cn);
                        cmd.Parameters.AddWithValue("@securityQuestion", securityQuestion);
                        cmd.Parameters.AddWithValue("@securityAnswer", securityAnswer);

                   try
                    {
                        //cn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                            status = ex.Message;
                            throw;
                    }

                    finally
                    {
                        cmd.Dispose();
                        status = "Member have been registered successfully!";

                    }
                }
                return status;

            }
           
        }
    }

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

You are doing it wrong.Follow these steps.

You need to return the inserted key from Member table as Ident_Current as an output parameter. See here.
remove the autoincrement of user id on forgotpassword table.
Insert the userid returned as output parameter in step-1 into the ForgotPAssword table

edited May 23 '17 at 10:32

Community

1
1

answered Dec 31 '13 at 06:05

Ehsan

31,833
6
56
65

Do you have any reference or demonstrate me because I am really new to programming – Swaggrammer Dec 31 '13 at 06:07
i have added the example link. – Ehsan Dec 31 '13 at 06:09
Did you mean like this?I have edited my sql and sql2 line.I do hope you can help me step by step and im sorry for troubling as im new – Swaggrammer Dec 31 '13 at 08:57
almost, a part from that you need to save the value you have fetched in sql1 to some variable that you can pass in your sql2 – Ehsan Dec 31 '13 at 09:03
I get the logic and implement it.But i cant get it. Can you demonstrate me? – Swaggrammer Dec 31 '13 at 09:30

score 0 · Answer 2 · answered Dec 31 '13 at 06:19

add SELECT SCOPE_IDENTITY() to the end of your query

string sql = "INSERT INTO Member   (userName,Email,Gender,DateOfBirth,Nationality,Race,Address,PostalCode,MobileNumber,Password,DateRegistered) VALUES(@name, @email, @gender, @dateOfBirth, @nationality, @race, @address, @postalCode, @mobileNo, @password, @dateRegistered) SELECT SCOPE_IDENTITY()";

and get your new Id using

    int NewId= cmd.ExecuteScalar();

after that pass it as parameter to your sql2 query

score 0 · Answer 3 · answered Dec 31 '13 at 06:22

Alternative to @No One, you can achieve entire thing in your sp and call that sp with required parameters thats all. Just an example not actual query.

Create proc MyProc
(@Param1 varchar(20), @Param2 varchar(20))
AS
BEGIN
Declare @Id as int  -- in case your PK is auto increment
Insert into Table1(B,C) Vaues(@Param1,@Param2)
Set @Id = @@Identity
Insert into Table2(FK_Table1,D)Values(@Id,@Param2) -- Here Id will be the FK , which you are 
                                         getting from the PK of the main table. 
END

Now your both tables are synced and you wont get any such errors.

In your cs embedd two more lines

cmd.CommandText = "MyProc";
cmd.CommandType = CommandType.StoredProcedure // instead of .Text

INSERT statement conflict with foreign key

3 Answers3