Insert incremented ID from the tbl_project to tbl_expense it must have same ID. I use PKey & Fkey the ID must be returned

Question

There is no error in this code. The only concern I face is when I try to save in the database the FK won't get the id of PK. I already setup my database relationship and it connect id - projectid.

if (textID.Text == "" && textProject.Text == "" && textAmount.Text == "")
{
    MessageBox.Show("Please Enter Details..!");
}
else
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tbl_project WHERE Contract_ID = '" + textID.Text + "'", conn);
    DataTable datatbl = new DataTable();
    adapter.Fill(datatbl);
    if (datatbl.Rows.Count == 1)
    {
        MessageBox.Show("Contract ID Already Exist!!");
    }
    else
    {
        SqlDataAdapter adap1 = new SqlDataAdapter("INSERT INTO tbl_project(Contract_ID,Contract_Amount,Contractor,Project_Name,Start_Date,End_Date,Year,Address,Remarks,Status)VALUES('" + textID.Text + "','" + textAmount.Text + "','" + textContract.Text + "','" + textProject.Text + "','" + dateTimePicker1.Value.Date + "','" + dateTimePicker2.Value.Date + "','" + textYear.Text + "','" + textAddress.Text + "','" + textOthers.Text + "','" + comboBox5.Text + "')", conn);
        DataTable fill1 = new DataTable();
        adap1.Fill(fill1);
        SqlDataAdapter adap2 = new SqlDataAdapter("INSERT INTO tbl_expense(CONTRACT,CONTRACT_ID,CONTRACTOR,PROJECT_STATUS,COVERED_PERIOD,END_PERIOD,PROJECT_AMOUNT)VALUES('" + textProject.Text + "','" + textID.Text + "','" + textContract.Text + "','" + comboBox5.Text + "','" + dateTimePicker1.Value.Date + "','" + dateTimePicker2.Value.Date + "','" + textAmount.Text + "')", conn);
        DataTable data2 = new DataTable();
        adap2.Fill(data2);

        MessageBox.Show("Project Details Save...");
        clear();
        refresh();
    }
}

Answer 1

From the information you've provided, it seems you are creating a value in two tables based on Contract_Id column and you are checking if the record already exists, you do not insert the records, otherwise you post the records.

Since, I cannot see the relationship between tbl_project and tbl_expense, I assume you are trying to insert Contract_Id as a foreign key in both the tables.

If that is the case, then I would suggest use below version of the code.

public void InsertIfNotExists()
{
    if (textID.Text == "" && textProject.Text == "" && textAmount.Text == "")
    {
        MessageBox.Show("Please Enter Details..!");
    }
    else
    {
        using(var connection = new SqlConnection(DbConnectionString)) // Pass DbConnectionString for your SQL server instance
        {
            var query = @"
IF NOT EXISTS (SELECT * FROM tbl_project WHERE Contract_ID = @Contract_ID)
BEGIN
    -- Here we are checking if the row already exists for the variable @Contract_ID
    INSERT INTO tbl_project (Contract_ID, Contract_Amount, Contractor, Project_Name, Start_Date, End_Date, Year, Address, Remarks, Status)
    VALUES (@Contract_ID, @Contract_Amount, @Contractor, @Project_Name, @Start_Date, @End_Date, @Year, @Address, @Remarks, @Status);
    -- We are inserting the values since Contract_ID was not matched in the database.

    INSERT INTO tbl_expense (CONTRACT, CONTRACT_ID, CONTRACTOR, PROJECT_STATUS, COVERED_PERIOD, END_PERIOD, PROJECT_AMOUNT)
    VALUES (@CONTRACT, @CONTRACT_ID, @CONTRACTOR, @PROJECT_STATUS, @COVERED_PERIOD, @END_PERIOD, @PROJECT_AMOUNT);
    -- We are inserting expense based on Contract_ID
END;
"
            ;
            using (var cmd = new SqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("@Contract_ID", textID.Text);
                cmd.Parameters.AddWithValue("@Contract_Amount", textAmount.Text);

                cmd.Parameters.AddWithValue("@Contractor", textContract.Text);
                cmd.Parameters.AddWithValue("@Project_Name", textProject.Text);

                cmd.Parameters.AddWithValue("@Start_Date", dateTimePicker1.Value.Date);
                cmd.Parameters.AddWithValue("@End_Date", dateTimePicker2.Value.Date);

                cmd.Parameters.AddWithValue("@Year", textYear.Text);
                cmd.Parameters.AddWithValue("@Address", textAddress.Text);

                cmd.Parameters.AddWithValue("@Remarks", textOthers.Text);
                cmd.Parameters.AddWithValue("@Status", comboBox5.Text);

                cmd.Parameters.AddWithValue("@CONTRACT", textProject.Text);
                cmd.Parameters.AddWithValue("@CONTRACTOR", textContract.Text);

                cmd.Parameters.AddWithValue("@PROJECT_STATUS", comboBox5.Text);
                cmd.Parameters.AddWithValue("@COVERED_PERIOD", dateTimePicker1.Value.Date);

                cmd.Parameters.AddWithValue("@END_PERIOD", dateTimePicker2.Value.Date);

                cmd.Parameters.AddWithValue("@PROJECT_AMOUNT", textAmount.Text);

                connection.Open();

                var rA = cmd.ExecuteNonQuery();

                connection.Close();

                MessageBox.Show(rA > 0 ? "Data Successfully saved!" : "Data already exists!");
                        
            }
        }
    }
}

In the code above, we are formulating the SQL Statement in a way, that you do not have to fetch and compare and then post to the database, saves you from the round trips for a simple operation.

Also, you'll notice, we are using parameterized query, where we are passing the parameters in the SQL statement, as mentioned in the comments by @Always Learning, it is a good thing from guys who try to mess around with your database, this will prevent SQL Injections.