C# ASP.NET插入数据库外键数据失败，求技术支持

核心错误原因 #

你遇到的「将 varchar 值转换为数据类型 int 时失败」错误，根源是错误地把int.TryParse的返回值（布尔类型）传给了需要整数的SQL参数。int.TryParse返回true/false仅表示解析是否成功，并非解析后的数值，数据库将布尔值当作字符串处理时就会触发类型转换错误。

同时代码还存在几个隐藏问题：

• 多个TryParse复用同一个变量r，后续解析会覆盖前面的结果，导致参数值错误
• INSERT语句中的子查询完全冗余，直接传入外键参数即可
• 数据库连接未正确释放，存在连接泄漏风险
• 未处理解析失败场景（如用户输入非数字时会默认用0，应添加验证提示）

修正后的代码 #

protected void SaveButton_Click(object sender, EventArgs e)
{
    // 每个解析使用独立变量，避免值被覆盖
    if (!int.TryParse(ProjectInformationIdtxt.Text, out int projectId))
    {
        lblmsg.Text = "请输入有效的项目ID";
        return;
    }

    if (!int.TryParse(PastExperienceDropDown.SelectedItem.Value, out int pastExperienceDomain))
    {
        lblmsg.Text = "请选择有效的领域经验值";
        return;
    }

    if (!int.TryParse(PastTechnologyDropDown.SelectedItem.Value, out int pastExperienceTech))
    {
        lblmsg.Text = "请选择有效的技术经验值";
        return;
    }

    if (!int.TryParse(MultipleDropDown.SelectedItem.Value, out int multipleTech))
    {
        lblmsg.Text = "请选择有效的多技术平台值";
        return;
    }

    if (!int.TryParse(CompletenessDropDown.SelectedItem.Value, out int clarityReq))
    {
        lblmsg.Text = "请选择有效的需求清晰度值";
        return;
    }

    if (!int.TryParse(AverageAgeDropDown.SelectedItem.Value, out int avgAppAge))
    {
        lblmsg.Text = "请选择有效的应用平均年龄值";
        return;
    }

    if (!int.TryParse(QualityDropDown.SelectedItem.Value, out int appQuality))
    {
        lblmsg.Text = "请选择有效的应用质量值";
        return;
    }

    if (!int.TryParse(ResourceDropDown.SelectedItem.Value, out int resourceCap))
    {
        lblmsg.Text = "请选择有效的资源能力值";
        return;
    }

    if (!int.TryParse(UsageDropDown.SelectedItem.Value, out int prodToolsUsage))
    {
        lblmsg.Text = "请选择有效的生产力工具使用率";
        return;
    }

    if (!int.TryParse(InterdependenceDropDown.SelectedItem.Value, out int moduleInterdep))
    {
        lblmsg.Text = "请选择有效的模块依赖值";
        return;
    }

    if (!int.TryParse(ComplexityDropDown.SelectedItem.Value, out int dbChangeComplexity))
    {
        lblmsg.Text = "请选择有效的数据库变更复杂度";
        return;
    }

    if (!int.TryParse(TestEnvironmentDropDown.SelectedItem.Value, out int testEnvStability))
    {
        lblmsg.Text = "请选择有效的测试环境稳定性";
        return;
    }

    if (!int.TryParse(ComplexityTestingDropDown.SelectedItem.Value, out int testingComplexity))
    {
        lblmsg.Text = "请选择有效的测试复杂度";
        return;
    }

    if (!int.TryParse(CriticalityDropDown.SelectedItem.Value, out int businessCriticality))
    {
        lblmsg.Text = "请选择有效的业务关键性";
        return;
    }

    if (!decimal.TryParse(ProdFactorTextBox.Text, out decimal prodFactor))
    {
        lblmsg.Text = "请输入有效的生产力系数";
        return;
    }

    if (!int.TryParse(PermanentTextBox.Text, out int permanentRatio))
    {
        lblmsg.Text = "请输入有效的常驻人员比例";
        return;
    }

    if (!int.TryParse(RotationalTextBox.Text, out int rotationalRatio))
    {
        lblmsg.Text = "请输入有效的轮换人员比例";
        return;
    }

    if (!int.TryParse(OffshoreTextBox.Text, out int offshoreRatio))
    {
        lblmsg.Text = "请输入有效的离岸人员比例";
        return;
    }

    int overRide = OverRideCheckBox.Checked ? 1 : 0;
    string remarks = RemarksTextBox.Text;

    // 将连接放入using块，自动释放连接资源
    using (SqlConnection con = new SqlConnection("你的数据库连接字符串"))
    {
        con.Open();
        // 简化INSERT语句，移除冗余子查询
        string query1 = @"INSERT INTO Project_Productivity 
                          (ProjectInformationId_FK, PastExperienceInDomain, PastExperienceInTechnology, 
                           MultipleTechnologiesOrPlatforms, ClarityOfRequirements, AverageAgeOfApplication, 
                           QualityOfApplication, ResourceCapability, UsageOfProductivityTools, 
                           InterdependenceWithOtherModules, ComplexityInDatabaseChanges, 
                           TestEnvironmentStability, ComplexityOfTesting, BusinessCriticality, 
                           IsOverrideCalculation, ProductivityFactor, PermanentRatio, RotationalRatio, 
                           OffshoreRatio, Remarks) 
                          VALUES (@ProjectInformationId_FK, @PastExperienceInDomain, @PastExperienceInTechnology, 
                                  @MultipleTechnologiesOrPlatforms, @ClarityOfRequirements, @AverageAgeOfApplication, 
                                  @QualityOfApplication, @ResourceCapability, @UsageOfProductivityTools, 
                                  @InterdependenceWithOtherModules, @ComplexityInDatabaseChanges, 
                                  @TestEnvironmentStability, @ComplexityOfTesting, @BusinessCriticality, 
                                  @IsOverrideCalculation, @ProductivityFactor, @PermanentRatio, @RotationalRatio, 
                                  @OffshoreRatio, @Remarks)";

        using (SqlCommand cmd = new SqlCommand(query1, con))
        {
            // 传入解析后的正确数值
            cmd.Parameters.AddWithValue("@ProjectInformationId_FK", projectId);
            cmd.Parameters.AddWithValue("@PastExperienceInDomain", pastExperienceDomain);
            cmd.Parameters.AddWithValue("@PastExperienceInTechnology", pastExperienceTech);
            cmd.Parameters.AddWithValue("@MultipleTechnologiesOrPlatforms", multipleTech);
            cmd.Parameters.AddWithValue("@ClarityOfRequirements", clarityReq);
            cmd.Parameters.AddWithValue("@AverageAgeOfApplication", avgAppAge);
            cmd.Parameters.AddWithValue("@QualityOfApplication", appQuality);
            cmd.Parameters.AddWithValue("@ResourceCapability", resourceCap);
            cmd.Parameters.AddWithValue("@UsageOfProductivityTools", prodToolsUsage);
            cmd.Parameters.AddWithValue("@InterdependenceWithOtherModules", moduleInterdep);
            cmd.Parameters.AddWithValue("@ComplexityInDatabaseChanges", dbChangeComplexity);
            cmd.Parameters.AddWithValue("@TestEnvironmentStability", testEnvStability);
            cmd.Parameters.AddWithValue("@ComplexityOfTesting", testingComplexity);
            cmd.Parameters.AddWithValue("@BusinessCriticality", businessCriticality);
            cmd.Parameters.AddWithValue("@IsOverrideCalculation", overRide);
            cmd.Parameters.AddWithValue("@ProductivityFactor", prodFactor);
            cmd.Parameters.AddWithValue("@PermanentRatio", permanentRatio);
            cmd.Parameters.AddWithValue("@RotationalRatio", rotationalRatio);
            cmd.Parameters.AddWithValue("@OffshoreRatio", offshoreRatio);
            cmd.Parameters.AddWithValue("@Remarks", remarks);

            try
            {
                cmd.ExecuteNonQuery();
                lblmsg.Text = "Project Productivity - Saved Successfully";
            }
            catch (SqlException ex)
            {
                // 捕获SQL异常，比如外键不存在的情况
                lblmsg.Text = $"保存失败：{ex.Message}";
            }
        }
    }
}

额外建议 #

1. 外键验证：插入前先检查Project_Information表中是否存在对应的ProjectInformationId_PK，避免触发外键约束错误
2. 前端验证：在页面上对输入框/下拉框做验证（如限制仅输入数字），减少后端错误触发概率
3. 强类型参数：优先使用cmd.Parameters.Add("@ParamName", SqlDbType.Int).Value = value;代替AddWithValue，避免类型推断错误

内容的提问来源于stack exchange，提问作者Harshitha Murthy