How to fix FK Constraint insert Exception-CodePudding

According to my question with weird problem specified here how to fix

System.Data.SqlClient.SqlException: String or binary data would be truncated in table

My problem is, that if I am saving new problem into the database, its ID is always set to 0 (I checked this out in debugging), which then throws

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Alert__Problem_I__17F790F9". The conflict occurred in database "SmartOne", table "dbo.Problem", column 'id'

But in SQL Server Management Studio, the ID is set correctly (ID is defined as an Identity column).

Where both I am using is in my question mentioned below. Thanks for any ideas or advice.

Method that saves Problem:

public void Save(Problem element)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();

        using (SqlCommand command = new SqlCommand("INSERT INTO Problem VALUES "  
            "(@nameOfAlert, @value, @result, @message_ID) ", conn))
        {
            command.Parameters.Add(new SqlParameter("@nameOfAlert", element.NameOfAlert));
            command.Parameters.Add(new SqlParameter("@value", (int)element.Value));
            command.Parameters.Add(new SqlParameter("@result", (int)element.Result));
            command.Parameters.Add(new SqlParameter("@message_ID", element.Message_Id));
            command.ExecuteNonQuery();

            command.CommandText = "Select @@Identity";
        }

        conn.Close();
    }
}

Method that saves an Alert:

public void Save(Alert element)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();

        using (SqlCommand command = new SqlCommand("INSERT INTO [Alert] VALUES (@message_ID, @date, @email, @AMUser_ID, @Problem_ID) ", conn))
        {
            command.Parameters.Add(new SqlParameter("@message_ID", element.Id_MimeMessage));
            command.Parameters.Add(new SqlParameter("@date", element.Date));
            command.Parameters.Add(new SqlParameter("@email", element.Email));
            command.Parameters.Add(new SqlParameter("@AMUser_ID", element.User_ID));
            command.Parameters.Add(new SqlParameter("@Problem_ID", element.Problem_ID));

            command.ExecuteNonQuery();

            command.CommandText = "Select @@Identity";
        }
        conn.Close();
    }
}

SQL Scheme

CREATE TABLE [dbo].[Alert](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [message_ID] [varchar](100) NOT NULL,
    [date] [datetime] NOT NULL,
    [email] [varchar](50) NOT NULL,
    [AMUser_ID] [int] NOT NULL,
    [Problem_ID] [int] NOT NULL);

//Where is ID, it means FK ID

CREATE TABLE [dbo].[Problem](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [nameOfAlert] [varchar](50) NOT NULL,
    [Value_ID] [int] NOT NULL,
    [Result_ID] [int] NOT NULL,
    [message_ID] [varchar](100) NOT NULL);

CodePudding user response：

One problem might be that you're never actually getting back the inserted IDENTITY value from your first insert - thus you aren't using any valid ProblemId value for your second insert.

Try something like this:

public void Save(Problem element)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();

        // define INSERT query - I'd *strongly* recommend specifying all
        // columns you're inserting into!
        // Also: run the "SELECT SCOPE_IDENTITY()" right after the INSERT
        string insertQry = "INSERT INTO dbo.Problem(NameOfAlert, Value, Result, MessageId) "  
                           "VALUES (@nameOfAlert, @value, @result, @message_ID); "  
                           "SELECT SCOPE_IDENTITY();";

        using (SqlCommand command = new SqlCommand(insertQry, conn))
        {
            // also here: define the *datatype* of the parameter, and use 
            // .Value = to set the value.
            // Since you haven't shown what the table looks like, I'm just
            // **guessing** the datatype and max length for the string parameters - adapt as needed!
            command.Parameters.Add("@nameOfAlert", SqlDbType.VarChar, 100).Value = element.NameOfAlert;
            command.Parameters.Add("@value", SqlDbType.Int).Value = (int)element.Value;
            command.Parameters.Add("@result", SqlDbType.Int).Value =  (int)element.Result;
            command.Parameters.Add("@message_ID", SqlDbType.VarChar, 100).Value = element.Message_Id;

            // since your statement now returns the ID value - use "ExecuteScalar"
            var returnedValue = command.ExecuteScalar();

            if (returnedValue != null)
            {
                // if a value was returned - convert to INT
                int problemId = Convert.ToInt32(returnedValue);
            }
        }
        conn.Close();
    }
}

Now, in case the INSERT works, you get back the ProblemId value from the identity column, and you can now use this in your second insert as value for the @ProblemId parameter.

CodePudding user response：

For saving the id into other table, you have to complete the insertion first. if the insertion is not completed then you can not get the problem id (if it is the primary key, which is supposed to be returned by saving the datas). Only after saving the data to the table, you are going to have the problem id then you can use it as FK in the same method.

if i say, there is two table and you are going to use the first table primary key in the second table as FK. Then you need to complete the first table row insertion. after excuting the query for the first table, you will get the primary key of that row and you can use easily in the second table as FK.