c#中编写Oracle的SQL语句命令并传递参数的问题.

[ff167]

最近才用oracle,发生了点问题

连接等用的是Oracle.DataAccess.Client类

以下是对同一份表(MEMBER)的操作,ID是NUMBER(10),MEMBERNAME是VARCHAR2(50)

DB db = new DB();
OracleParameter[] OraParams = new OracleParameter[1];
OraParams[0] = new OracleParameter("ID", OracleDbType.Int32, 10, 2, ParameterDirection.Input);
DataTable dt = db.GetRecord("select * from et_member where ID = :ID", OraParams);

这样的查询语句,是正常的,能查出正确的结果.

然而
DB db = new DB();
OracleParameter[] OraParams = new OracleParameter[2];
OraParams[0] = new OracleParameter("ID", OracleDbType.Int32, 2, 10, ParameterDirection.Input);
OraParams[1] = new OracleParameter("MemberName", OracleDbType.Varchar2, 50, "xxxy", ParameterDirection.Input);
db.ExecuteNonQuery("update et_member set MemberName=:MemberName where ID = :ID", OraParams);

这样写的语句,却报ORA01722 无效数字错误.

如果两个参数都是字符串(当然WHERE里要修改),程序就又没有错了.怎么回事?是NUMBER对应的类型问题还是什么问题?

[北京的雾霾天]

看下下是不是参数顺序的问题，这样改一下：

DB db = new DB();
OracleParameter[] OraParams = new OracleParameter[2];
OraParams[0] = new OracleParameter("MemberName", OracleDbType.Varchar2, 50, "xxxy", ParameterDirection.Input);
OraParams[1] = new OracleParameter("ID", OracleDbType.Int32, 2, 10, ParameterDirection.Input);
db.ExecuteNonQuery("update et_member set MemberName=:MemberName where ID = :ID", OraParams);

可能Oracle的参数只是使用占位符，而不是命名参数。就是按位置顺序来匹配。

[amandag]

参数顺序的问题，占位符用?即可

[amandag]

DB db = new DB();
OracleParameter[] OraParams = new OracleParameter[2];
OraParams[1] = new OracleParameter("?", OracleDbType.Varchar2, 50, "xxxy", ParameterDirection.Input);
OraParams[0] = new OracleParameter("?", OracleDbType.Int32, 2, 10, ParameterDirection.Input); //ID在后
db.ExecuteNonQuery("update et_member set MemberName=? where ID = ?", OraParams);

[ff167]

刚去查了下

Looks like your parameters are bound in the wrong order. ODP.NET binds by position by default.

Fix the order or set OracleCommand.BindByName = true.

David

回去试下看看先,先谢谢.等下再报告

现在又遇到ORA-12516数据库重启中...不过觉得是某些资源没释放一类的问题 - -