[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]在将varchar 值'初始状态'转换成数据类型int时失败要怎么解决？

Question

insertStr = "insert into "+ tableName +" (RELATION_ID,ORGANIZATION_ID,SAMPLE_NO,LOT_NAME,ITEM_CODE,QUANTITY,C_SMPL_VOLUME,C_SAMPLE_UNIT,C_AGENCY,SUPPLIER_LOT,C_MATERIAL_SOURCE,SPEC_ID,GRADE,NUM_CONTAINERS,SPEC_VERS,C_PACK_SPEC,C_SPEC,C_STORAGE_CONDITION,C_TEST_PREPOSE,C_LOT_STATUS,C_MANUFACTURER,PRODUCTION_DATE,T_PH_RETEST_DATE,EXPIRY_DATE,CREATELOT_STATUS,RESULT_STATUS,SEQ,TEST_ID, "
insertStr = insertStr + "RESULT_ID,TEST_DESC,TEST_UNIT,CHECK_ITEM,CHECK_RESULT,TYPE_SEQ,TYPE_CODE,C_ERP_COMMENT,C_REAGENT_GRADE, CREATE_ON)"
insertStr = insertStr + " VALUES ('"+ xmlArrRet[1,1] +"','"+ xmlArrRet[1,2] +"','"+ xmlArrRet[1,3] +"','"+ xmlArrRet[1,4] +"','"+ xmlArrRet[1,5] +"','"+ xmlArrRet[1,6] +"','"+ xmlArrRet[1,7] +"' "
insertStr = insertStr + " ,'" + xmlArrRet[1,8] +"','"+ xmlArrRet[1,9] +"','"+ xmlArrRet[1,10] +"','"+ xmlArrRet[1,11] +"','"+ xmlArrRet[1,12] +"','"+xmlArrRet[1,13]&"_"&xmlArrRet[1,15] +"','"+xmlArrRet[1,14] +"','"+xmlArrRet[1,15] +"','"+ xmlArrRet[1,16] +"','" + xmlArrRet[1,17] +"','" + xmlArrRet[1,18] +"','"+ xmlArrRet[1,25] +"','" + xmlArrRet[1,26] +"','"+ xmlArrRet[1,27] +"','"+ xmlArrRet[1,19] +"','" + xmlArrRet[1,20] +"','" + xmlArrRet[1,21] +"','" + xmlArrRet[1,28] +"','" + xmlArrRet[1,29] +"','" + "初始状态"+"','" + "初始状态"+"','"+ xmlArrRet1[a,1] +"','"+ xmlArrRet1[a,2] +"' "

insertStr = insertStr + " ,'" + xmlArrRet1[a,3] +"','"+ xmlArrRet1[a,4] +"' ,'"+ xmlArrRet1[a,5] +"','"+ xmlArrRet1[a,6] +"','"+ xmlArrRet1[a,7] +"','"+ xmlArrRet1[a,8] +"','"+ xmlArrRet1[a,9] +"' "

insertStr = insertStr + ",SYSDATETIME() ) "
status = SQL(insertStr)

    IF (status) THEN 
        MsgBox("中间表数据写入成功",100) 
    ELSE 
        MsgBox("中间表数据没有更新",100) 
    ENDIF 
NEXT

Answer 1

这个错误是由于在将varchar值转换为int数据类型时，数据中存在非数字字符，导致SQL Server无法进行转换。为了解决这个问题，你需要检查数据中的所有值，并确保它们都是有效的int类型值。如果数据中的某些值确实是非数字字符，你需要将这些值进行更正或删除它们。你还可以使用CAST或CONVERT函数强制将varchar值转换为int数据类型。例如，使用以下代码可以将varchar值转换为int数据类型：

CAST('123' AS INT)

或

CONVERT(INT, '123')