想向用戶表插入記錄，然后回傳最新的自增id，但是提示錯誤
public int Add(Tb_UserManager model)
{
using (OracleDataAccessManagerHelper ODAMH = new OracleDataAccessManagerHelper(OracleConnectString))
{
string sSQL = @"var MaxID int;
exec :MaxID:=SEQ_ATABLE.NextVal;
Insert into Tb_UserManager( ID, COMPANYID, USERNAME, USERPWD, SEX, TELPHONE, ROLEID, ISENABLE, ADDTIME, ADDDATE)
values(:MaxID,:COMPANYID,:USERNAME,:USERPWD,:SEX,:TELPHONE,:ROLEID,:ISENABLE,:ADDTIME,:ADDDATE); Select :MaxID as b from dual; ";
OracleParameter[] OP = new OracleParameter[9];
OP[0] = ODAMH.CreateOracleParameter("COMPANYID", OracleDbType.Int32, 4, model.CompanyID);
OP[1] = ODAMH.CreateOracleParameter("USERNAME", OracleDbType.NVarchar2, 20, model.UserName);
OP[2] = ODAMH.CreateOracleParameter("USERPWD", OracleDbType.Varchar2, 20, model.UserPwd);
OP[3] = ODAMH.CreateOracleParameter("SEX", OracleDbType.Int32, 4, model.Sex);
OP[4] = ODAMH.CreateOracleParameter("TELPHONE", OracleDbType.Varchar2, 20, model.TelPhone);
OP[5] = ODAMH.CreateOracleParameter("ROLEID", OracleDbType.Int32, 4, model.RoleID);
OP[6] = ODAMH.CreateOracleParameter("ISENABLE", OracleDbType.Int32, 4, model.IsEnable);
OP[7] = ODAMH.CreateOracleParameter("ADDTIME", OracleDbType.Date, 4, model.AddTime);
OP[8] = ODAMH.CreateOracleParameter("ADDDATE", OracleDbType.Date, 4, model.AddDate);
return Convert.ToInt32(ODAMH.ExecuteScalar(sSQL, OP));
}
}
執行時候報錯 ORA-00900: invalid SQL statement
但是用SQL Developer 執行 下面腳本就沒有問題
var MaxID int;
exec :MaxID:=SEQ_ATABLE.NextVal;
insert into TB_USERMANAGER( ID)
values(:MaxID);
Select :MaxID from dual;
腳本與上面C# 寫的類似
請教各位大神了。
uj5u.com熱心網友回復：
用select SEQ_ATABLE.NextVal from dual取出序列下一個值再用一次insert插入
uj5u.com熱心網友回復：
你應該使用plsql的匿名塊。begin
insert 。。。
end;
uj5u.com熱心網友回復：
把序列的值拼進sqlInsert into Tb_UserManager( ID, COMPANYID, USERNAME, USERPWD, SEX, TELPHONE, ROLEID, ISENABLE, ADDTIME, ADDDATE)
(select SEQ_ATABLE.NextVal, :COMPANYID,:USERNAME,:USERPWD,:SEX,:TELPHONE,:ROLEID,:ISENABLE,:ADDTIME,:ADDDATE from dual);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/96328.html
標籤：開發