SQL2016环境SAPbobsCOM.Recordset插入时varchar转numeric报错修复咨询

1. 类型转换错误根源：你把数值类型（weight、noOfPackages、amount）用单引号包裹成字符串传入，SQL Server需要将其转换为对应的numeric/int类型。SQL Server 2016和2019对区域设置的兼容性处理不同，若运行环境区域设置用逗号作为小数点分隔符，字符串形式的123.45在2016中无法被正确转换为numeric，而2019可能做了兼容处理。
2. 列数不匹配错误原因：移除单引号后，若数值变量转字符串时包含逗号（区域设置导致小数点显示为逗号），SQL会将逗号识别为值分隔符，导致VALUES子句的数量多于列数，触发报错。

核心解决思路：使用参数化查询替代字符串拼接 #

字符串拼接SQL不仅会引发类型转换和区域适配问题，还存在严重的SQL注入风险。使用SAP Business One的参数化查询可以完美解决这些问题：

if (rsCD.RecordCount > 0)
{
    docNum = rsCD.Fields.Item("DocNum").Value;
    subFileNo = rsCD.Fields.Item("Project").Value;
    containerSize = rsCD.Fields.Item("U_DCC_CNTS").Value;
    hawb = rsCD.Fields.Item("NumAtCard").Value;
    serviceMode = "Clearing";
    weight = (decimal)rsCD.Fields.Item("Quantity").Value; // 用decimal替代double更适合金额/重量数值
    noOfPackages = Convert.ToInt32(rsCD.Fields.Item("FreeTxt").Value);
    cardCode = rsCD.Fields.Item("CardCode").Value;
    cardName = rsCD.Fields.Item("CardName").Value;
    amount = (decimal)rsCD.Fields.Item("DocTotalFC").Value; // 金额必须用decimal避免精度丢失
    contactNo = rsCD.Fields.Item("Phone1").Value;
    location = "NAI-FUNZI";
    rcvdBy = rsCD.Fields.Item("U_RcvdBy").Value;

    #region Insert into FMS2 Cargo details table 
    // Check if entry already exists
    SAPbobsCOM.Recordset rsExists = (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);

    // 参数化查询检查存在性，避免SQL注入
    rsExists.DoQuery("SELECT T0.\"Code\" FROM \"@FMS2\" T0 WHERE T0.\"Code\" = @hawb");
    rsExists.Parameters.Item("@hawb").Value = hawb;
    rsExists.MoveFirst();

    if (rsExists.RecordCount == 0)
    {
        SAPbobsCOM.Recordset rsFMS2 = (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
        
        // 编写参数化INSERT语句
        string insertSql = @"INSERT INTO [dbo].[@FMS2] 
                            (Code, Name, U_SubFileNo, U_ContainerSz, U_AWB, U_ServiceMode, 
                            U_Weight, U_NoOfPackages, U_CardCode, U_CardName, U_Consignee, 
                            U_Amount, U_Contact, U_Location, U_QuotationNo, U_RcvdBy) 
                            VALUES (@hawb, @hawb, @subFileNo, @containerSize, @hawb, @serviceMode, 
                            @weight, @noOfPackages, @cardCode, @cardName, @consigneeName, 
                            @amount, @contactNo, @location, @docNum, @rcvdBy)";
        
        // 绑定参数值
        rsFMS2.Parameters.Item("@hawb").Value = hawb;
        rsFMS2.Parameters.Item("@subFileNo").Value = subFileNo;
        rsFMS2.Parameters.Item("@containerSize").Value = containerSize;
        rsFMS2.Parameters.Item("@serviceMode").Value = serviceMode;
        rsFMS2.Parameters.Item("@weight").Value = weight;
        rsFMS2.Parameters.Item("@noOfPackages").Value = noOfPackages;
        rsFMS2.Parameters.Item("@cardCode").Value = cardCode;
        rsFMS2.Parameters.Item("@cardName").Value = cardName;
        rsFMS2.Parameters.Item("@consigneeName").Value = consigneeName;
        rsFMS2.Parameters.Item("@amount").Value = amount;
        rsFMS2.Parameters.Item("@contactNo").Value = contactNo;
        rsFMS2.Parameters.Item("@location").Value = location;
        rsFMS2.Parameters.Item("@docNum").Value = docNum;
        rsFMS2.Parameters.Item("@rcvdBy").Value = rcvdBy;
        
        // 执行查询
        rsFMS2.DoQuery(insertSql);
        rsFMS2.MoveFirst();

        ErrorLog.LoggerExceptionInfo(new Exception("FMS2 Cargo details HAWB inserted: " + hawb + Environment.NewLine));
    }
    #endregion 
}

额外优化点 #

• 将double类型替换为decimal：金额和重量这类需要精确计算的数值，用decimal可以避免浮点数精度丢失问题，也更符合SQL Server的numeric类型适配。
• 所有SQL查询都改为参数化：包括之前的存在性检查查询，彻底消除SQL注入风险，同时避免字符串拼接带来的各种格式问题。

临时应急修复（不推荐，仅用于快速验证） #

如果暂时无法修改为参数化查询，需要确保数值转字符串时使用不变的小数点格式（强制用点作为分隔符），同时检查VALUES子句的逗号分隔是否正确：

// 将数值转字符串时强制使用点作为小数点，不受区域设置影响
string amountStr = amount.ToString(System.Globalization.CultureInfo.InvariantCulture);
string weightStr = weight.ToString(System.Globalization.CultureInfo.InvariantCulture);

// 数值类型去掉单引号，使用转换后的字符串
rsFMS2.DoQuery("INSERT INTO [dbo].[@FMS2] " +
               "(Code, Name, U_SubFileNo, U_ContainerSz, U_AWB, U_ServiceMode, " +
               "U_Weight, U_NoOfPackages, U_CardCode, U_CardName, U_Consignee, " +
               "U_Amount, U_Contact, U_Location, U_QuotationNo, U_RcvdBy) " +
               "values ('" + hawb + "', '" + hawb + "','" + subFileNo + "', '" + containerSize + "', '" + hawb + "', '" + serviceMode + "', " +
               weightStr + ", " + noOfPackages + ", '" + cardCode + "', '" + cardName + "', '" + consigneeName + "', " +
               amountStr + ", '" + contactNo + "', '" + location + "', '" + docNum + "', '" + rcvdBy + "')");

注意：这种方法只是临时 workaround，仍然存在SQL注入风险，长期来看必须改用参数化查询。

内容的提问来源于stack exchange，提问作者Kinyanjui Kamau