Oracle ORA-01722: 无效数字处理方法

浏览数：30 / 时间：2015年06月12日

C# + Oralce 10G

项目中有用参数处理Update语句。参数命名和表字段同名。执行报错： ORA-01722: 无效数字

后修改所有的参数和对应字段不同。解决。

修改前：

StringBuilder strSql = new StringBuilder();
object returnOValue = null;
strSql.Append("update CRM_CONTACTS_RESOURCE set ");
strSql.Append("RESOURCE_NAME=:RESOURCE_NAME,");
strSql.Append("RESOURCE_DESC=:RESOURCE_DESC,");
strSql.Append("OBJECT_ID=:OBJECT_ID,");
strSql.Append("PRIMARY_KEYS=:PRIMARY_KEYS,");
strSql.Append("MODIFIED_BY=:MODIFIED_BY,");
strSql.Append("MODIFIED_DATE=:MODIFIED_DATE");
strSql.Append(" where CONTACTS_ID=:CONTACTS_ID and SERIAL_NO=:SERIAL_NO and TYPE_CODE=:TYPE_CODE");
OracleParameter[] parameters = {
new OracleParameter(":RESOURCE_NAME", OracleDbType.Varchar2,50),
new OracleParameter(":RESOURCE_DESC", OracleDbType.Varchar2,200),
new OracleParameter(":OBJECT_ID", OracleDbType.Varchar2,10),
new OracleParameter(":PRIMARY_KEYS", OracleDbType.Varchar2,50),
new OracleParameter(":MODIFIED_BY", OracleDbType.Varchar2,10),
new OracleParameter(":MODIFIED_DATE", OracleDbType.Date),
new OracleParameter(":CONTACTS_ID", OracleDbType.Decimal,10),
new OracleParameter(":SERIAL_NO", OracleDbType.Decimal,4),
new OracleParameter(":TYPE_CODE", OracleDbType.Varchar2,1)};
parameters[0].Value = contactsRParm.RESOURCE_NAME;
parameters[1].Value = contactsRParm.RESOURCE_DESC;
parameters[2].Value = contactsRParm.OBJECT_ID;
parameters[3].Value = contactsRParm.PRIMARY_KEYS;
parameters[4].Value = contactsRParm.MODIFIED_BY;
parameters[5].Value = contactsRParm.MODIFIED_DATE;
parameters[6].Value = contactsRParm.CONTACTS_ID;
parameters[7].Value = contactsRParm.SERIAL_NO;
if (contactsRParm.TYPE_CODE == "0")//人脉
{
parameters[8].Value = "A";
}
else if (contactsRParm.TYPE_CODE == "1")//外部资源
{
parameters[8].Value = "B";
}

修改后：

StringBuilder strSql = new StringBuilder();
object returnOValue = null;
strSql.Append("update CRM_CONTACTS_RESOURCE set ");
strSql.Append("RESOURCE_NAME=:RESOURCENAME,");
strSql.Append("RESOURCE_DESC=:RESOURCEDESC,");
strSql.Append("OBJECT_ID=:OBJECTID,");
strSql.Append("PRIMARY_KEYS=:PRIMARYKEYS,");
strSql.Append("MODIFIED_BY=:MODIFIEDBY,");
strSql.Append("MODIFIED_DATE=:MODIFIEDDATE");
strSql.Append(" where CONTACTS_ID=:CONTACTSID and SERIAL_NO=:SERIALNO and TYPE_CODE=:TYPECODE");
OracleParameter[] parameters = {
new OracleParameter(":RESOURCENAME", OracleDbType.Varchar2,50),
new OracleParameter(":RESOURCEDESC", OracleDbType.Varchar2,200),
new OracleParameter(":OBJECTID", OracleDbType.Varchar2,10),
new OracleParameter(":PRIMARYKEYS", OracleDbType.Varchar2,50),
new OracleParameter(":MODIFIEDBY", OracleDbType.Varchar2,10),
new OracleParameter(":MODIFIEDDATE", OracleDbType.Date),
new OracleParameter(":CONTACTSID", OracleDbType.Decimal,10),
new OracleParameter(":SERIALNO", OracleDbType.Decimal,4),
new OracleParameter(":TYPECODE", OracleDbType.Varchar2,1)};
parameters[0].Value = contactsRParm.RESOURCE_NAME;
parameters[1].Value = contactsRParm.RESOURCE_DESC;
parameters[2].Value = contactsRParm.OBJECT_ID;
parameters[3].Value = contactsRParm.PRIMARY_KEYS;
parameters[4].Value = contactsRParm.MODIFIED_BY;
parameters[5].Value = contactsRParm.MODIFIED_DATE;
parameters[6].Value = contactsRParm.CONTACTS_ID;
parameters[7].Value = contactsRParm.SERIAL_NO;
if (contactsRParm.TYPE_CODE == "0")//人脉
{
parameters[8].Value = "A";
}
else if (contactsRParm.TYPE_CODE == "1")//外部资源
{
parameters[8].Value = "B";
}