locked
Unable to update Email ID getting an error The INSERT statement conflicted with the FOREIGN KEY constraint RRS feed

  • Question

  • User-1395830550 posted

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserInstitutionLink_User". The conflict occurred in database "3s_dum", table "dbo.User", column 'Id'. The statement has been terminated. The 'AddUserInstitutionLink' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

    Code:

    public int UserInstutionLink(int UiD, int InsId)
                {
                    int UserInstLink;
                    SqlConnection Cn = new SqlConnection(Conn);
                    Cn.Open();
                    SqlCommand cmd = new SqlCommand("AddUserInstitutionLink", Cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter UILink = new SqlParameter("@uiliD", SqlDbType.Int);
                    UILink.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(UILink);
                    cmd.Parameters.AddWithValue("@UserId", UiD);
                    cmd.Parameters.AddWithValue("@InstId", InsId);
                    cmd.ExecuteNonQuery();
                    UserInstLink = int.Parse(cmd.Parameters["@uiliD"].Value.ToString());
                    Cn.Close();
                    return UserInstLink;
                }

    public void Update_U_Anonymoususerdetails(GridView GridView1,int CaseId)
                {
                    int InsId=0, UserId=0,UserInstLink=0,UserCreditRecord=0;
                    SqlConnection Cn = new SqlConnection(Conn);
                    Cn.Open();
                    for (int i = 0; i < GridView1.Rows.Count; i++)
                    {
                        Label Id = GridView1.Rows[i].FindControl("lblId") as Label;
                        TextBox Name = GridView1.Rows[i].FindControl("txtName") as TextBox;
                        TextBox MName = GridView1.Rows[i].FindControl("txtMName") as TextBox;
                        TextBox LName = GridView1.Rows[i].FindControl("txtLName") as TextBox;
                        TextBox Degree = GridView1.Rows[i].FindControl("txtDegree") as TextBox;
                        TextBox Title = GridView1.Rows[i].FindControl("txtTitle") as TextBox;
                        TextBox Email = GridView1.Rows[i].FindControl("txtEmail") as TextBox;
                        TextBox Institution = GridView1.Rows[i].FindControl("txtInstitution") as TextBox;
                        if (Name.Text.Trim() != null && Name.Text.Trim() !="")
                        {
                            int AId = 0;
                            if (Id != null)
                                AId = Convert.ToInt32(Id.Text);
                            else
                                AId = 0;                       
                            SqlCommand Cmd = new SqlCommand("UpdateOtherAuthors", Cn);
                            Cmd.CommandType = CommandType.StoredProcedure;
                            SqlParameter InID = new SqlParameter("@uiID", SqlDbType.Int);                      
                            InID.Direction = ParameterDirection.Output;
                            Cmd.Parameters.Add(InID);
                            try
                            {
                                Cmd.Parameters.AddWithValue("@Name", Name.Text);
                                Cmd.Parameters.AddWithValue("@MName", MName.Text);
                                Cmd.Parameters.AddWithValue("@LName", LName.Text);
                                Cmd.Parameters.AddWithValue("@Degree", Degree.Text);
                                Cmd.Parameters.AddWithValue("@Title", Title.Text);
                                Cmd.Parameters.AddWithValue("@Email", Email.Text);
                                Cmd.Parameters.AddWithValue("@Institution", Institution.Text);
                                Cmd.Parameters.AddWithValue("@Id", AId);
                                Cmd.Parameters.AddWithValue("@CaseId", CaseId);

                                Cmd.ExecuteNonQuery();
                                UserId = int.Parse(Cmd.Parameters["@uiID"].Value.ToString());
                            }
                            catch (Exception ex)
                            {
                                System.Web.UI.Page page = (System.Web.UI.Page)HttpContext.Current.Handler;

                                page.ClientScript.RegisterStartupScript(this.GetType(), "Window", "alert('Same email id is already exist, Give some other id');", true);
                            }
                            
                        }
                        if (Institution.Text.Trim() != null && Institution.Text.Trim() != "")
                        {
                            
                           SqlCommand cmd = new SqlCommand("AddInstitution", Cn);
                            cmd.CommandType = CommandType.StoredProcedure;
                            SqlParameter oParam = new SqlParameter("@Institution", Institution.Text.ToString());
                            SqlParameter InID = new SqlParameter("@InID",SqlDbType.Int);
                            oParam.Direction = ParameterDirection.Input;
                            InID.Direction = ParameterDirection.Output;
                            cmd.Parameters.Add(oParam);
                            cmd.Parameters.Add(InID);
                            cmd.ExecuteNonQuery();
                            InsId = int.Parse(cmd.Parameters["@InID"].Value.ToString());
                            
                        }
                       UserInstLink= UserInstutionLink(UserId, InsId); // Getting ERROR in this line
                       UserCreditRecord = UserCreditRecordID(UserInstLink, Name.Text, MName.Text, LName.Text, Degree.Text, Title.Text);
                       AssignContentManagementPrivileges(UserId,CaseId);
                       CreateCaseCredits(InsId, CaseId, UserCreditRecord);

                    }
                    Cn.Close();
                }


    Store Procedure:

    ALTER PROCEDURE [dbo].[CreateOtherAuthorsAccount]
    (
    @Name nvarchar(50),

    @LName nvarchar(50),@MName nvarchar(50),
    @Degree nvarchar(50),
    @Email nvarchar(50)
    )

    AS BEGIN
    Declare @login1 nvarchar(50),@login3 nvarchar(50),@count1 int,@login2 nvarchar(50),@count2 int,@count3 int
    set @login1=(SELECT SUBSTRING(@LName, 1, 3))
    set @login2=(SELECT SUBSTRING(@Name, 1, 2))
    set @login3=(SELECT SUBSTRING(@Name, 1, 3))
    Select @count1=COUNT(Login) from [User] where Login=@Name+@login1
    Select @count2=COUNT(Login) from [User] where Login=@Name+@login1+@login2
    Select @count3=COUNT(Login) from [User] where Login=@Name+@login1+@login2+@login3

    if(@count1=0)

     INSERT INTO [User] VALUES('1',@Email,'','','','0','0','0','0','0','0','0','0',@Name+@login1,'EA2A7C69C754A1EAF0D22BD47DA31F663FB0FE13','sMd8L5R/KgM1hDa7/3BWtg==','','',GETDATE(),@Name,@LName,@MName,@Degree)

    else if(@count2 =0)  

          INSERT INTO [User] VALUES('1',@Email,'','','','0','0','0','0','0','0','0','0',@Name+@login1+@login2,'EA2A7C69C754A1EAF0D22BD47DA31F663FB0FE13','sMd8L5R/KgM1hDa7/3BWtg==','','',GETDATE(),@Name,@LName,@MName,@Degree)
       else if (@count3 =0)      

           INSERT INTO [User] VALUES('1',@Email,'','','','0','0','0','0','0','0','0','0',@Name+@login1+@login2+@login3,'EA2A7C69C754A1EAF0D22BD47DA31F663FB0FE13','sMd8L5R/KgM1hDa7/3BWtg==','','',GETDATE(),@Name,@LName,@MName,@Degree)
      else
          
           INSERT INTO [User] VALUES('1',@Email,'','','','0','0','0','0','0','0','0','0',@login2+@Name+@login1+@login2+@login3,'EA2A7C69C754A1EAF0D22BD47DA31F663FB0FE13','sMd8L5R/KgM1hDa7/3BWtg==','','',GETDATE(),@Name,@LName,@MName,@Degree)
    end

    ALTER PROCEDURE [dbo].[UpdateOtherAuthors]
    (
    @Name nvarchar(50),

    @LName nvarchar(50),@MName nvarchar(50),
    @Degree nvarchar(50),
    @Title nvarchar(50),
    @Email nvarchar(50),
    @Institution nvarchar(50),
    @Id int,
    @CaseId int,
    @uiID int output

    )

    AS BEGIN
    Declare @SameId nvarchar(50)
    if(@Id='0')
    begin
    INSERT INTO tbl_AuthorDetails VALUES(@Name,@Degree,@Title,@Email,@Institution,@CaseId,@LName,@MName)
        Select @Id=COUNT(Email) from [User] where Email=@Email
        if(@Id=0)
        begin
        exec CreateOtherAuthorsAccount @Name,@LName,@MName,@Degree,@Email
        end
    end
    else
    begin
    set @SameId=(Select (Email) from  tbl_AuthorDetails where Id=@Id)
    if(@SameId = @Email)
        Update  tbl_AuthorDetails set Name=@Name,Degree=@Degree,Title=@Title,Email=@Email,Institution=@Institution,LName=@LName,MName=@MName where Id=@Id
    else
    begin
        INSERT INTO tbl_AuthorDetails VALUES(@Name,@Degree,@Title,@Email,@Institution,@CaseId,@LName,@MName)
        Update  tbl_AuthorDetails set  CaseId=NULL  where Id=@Id
        exec CreateOtherAuthorsAccount @Name,@LName,@MName,@Degree,@Email
        end
    end
     
    END
    set @uiID= (Select ID from [User] where Email=@Email)  
    RETURN @uiID

    Please some one help me how to update the email id...

    I am getting the error when i am trying to update 2 email id at once with the same name from the gridview

    Wednesday, July 13, 2011 5:59 AM

Answers

  • User-1395830550 posted

    Change the updateotherauthor sp

    INSERT INTO tbl_AuthorDetails VALUES(@Name,@Degree,@Title,@Email,@Institution,@CaseId,@LName,@MName)
        Update  tbl_AuthorDetails set  CaseId=NULL  where Id=@Id
        Select @Id=COUNT(Email) from [User] where Email=@Email
        if(@Id=0)
        begin
        exec CreateOtherAuthorsAccount @Name,@LName,@MName,@Degree,@Email
        end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 13, 2011 8:22 AM