Oracle ORA-01722: 無效數字 處理方法

標籤：des   ar   cti   new   sql   res   type   oracle   

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";
}