Search code examples
c#sql-server-2016sapb1

Error converting data type varchar to numeric for SAPbobsCOM.Recordset insert statement

I have a program that runs fine in SQL 2019 but throws an error when I try to run it in another environment in SQL 2016.

The column where the error is thrown is the amount column.

enter image description here

When the amount column value has a decimal point, this throws an error in the SQL 2016 environment: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error converting data type varchar to numeric.

Changing from double to decimal does not help. Removing the single quotes brings another error: There are fewer columns in the INSERT statement than values specified in the VALUES clause

What do I need to do to fix this?

Code:

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 = (double)rsCD.Fields.Item("Quantity").Value;
    noOfPackages = Convert.ToInt32(rsCD.Fields.Item("FreeTxt").Value);
    cardCode = rsCD.Fields.Item("CardCode").Value;
    cardName = rsCD.Fields.Item("CardName").Value;
    amount = (double)rsCD.Fields.Item("DocTotalFC").Value;                               
    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);

    rsExists.DoQuery("SELECT T0.\"Code\" FROM \"@FMS2\" T0 WHERE T0.\"Code\" = '" + hawb + "'");
    rsExists.MoveFirst();

    if (rsExists.RecordCount == 0)
    {
        SAPbobsCOM.Recordset rsFMS2 = (SAPbobsCOM.Recordset)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
        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 + "', " +
                       "'" + weight + "', " +
                       "'" + noOfPackages + "', " +
                       "'" + cardCode + "', " +
                       "'" + cardName + "', " +
                       "'" + consigneeName + "', " +
                       "'" + amount + "', " +           // -< Error thrown here
                       "'" + contactNo + "', " +
                       "'" + location + "', " +
                       "'" + docNum + "', " +
                       "'" + rcvdBy + "')");
        rsFMS2.MoveFirst();

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

  • Building SQL using string concatenation is just wrong. Use parameterized queries.

    That being said, I am guessing from the comments that your application uses a culture that uses , as decimal separator:

    Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("fr-FR");
double amount = 563.39;
Console.WriteLine("SELECT CAST('" + 563.39 + "' AS NUMERIC(19, 6))");

// SELECT CAST('563,39' AS NUMERIC(19, 6))
// ----------------^

    The above query will produce the "Error converting data type varchar to numeric" error. Various workarounds are possible but I would suggest:

    Console.WriteLine("SELECT CAST('" + (563.39).ToString(CultureInfo.InvariantCulture) + "' AS NUMERIC(19, 6))");
// SELECT CAST('563.39' AS NUMERIC(19, 6))