asp.net+oracle问题：系统提示错误“ORA-01036: 非法的变量名/编号 ”
代码如下：
OracleConnection conn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand( "INSERT INTO " + tableName + " " +
" (PKID, Username, OraclePassword, Email, PasswordQuestion, " +
" PasswordAnswer, IsApproved, " +
" OracleComment, CreationDate, LastPasswordChangedDate, LastActivityDate, " +
" ApplicationName, IsLockedOut, LastLockedOutDate, " +
" FailedPWDAttemptCount, FailedPWDAttemptWindowStart, " +
" FailedPWDAnswerAttemptCount, FailedPWDAnswerAttemptWinStart) " +
" Values(:OPKID, :OUsername, :OOraclePassword, :OEmail, :OPasswordQuestion, :OPasswordAnswer, :OIsApproved, :OOracleComment, " +
":OCreationDate, :OLastPasswordChangedDate, :OLastActivityDate, :OApplicationName, :OIsLockedOut, :OLastLockedOutDate, " +
":OFailedPWDAttemptCount, :OFailedPWDAttemptWindowStart, :OFailedPWDAnswerAttemptCount, :OFailedPWDAnswerAttemptWinStart) ", conn);
cmd.Parameters.Add( "OPKID ", OracleType.VarChar,255).Value = providerUserKey.ToString();
cmd.Parameters.Add( "OUsername ", OracleType.VarChar, 255).Value = username;
cmd.Parameters.Add( "OOraclePassword ", OracleType.VarChar, 255).Value = EncodePassword(OraclePassword);
cmd.Parameters.Add( "OEmail ", OracleType.VarChar, 128).Value = email;
cmd.Parameters.Add( "OPasswordQuestion ", OracleType.VarChar, 255).Value = passwordQuestion;
cmd.Parameters.Add( "OPasswordAnswer ", OracleType.VarChar, 255).Value = EncodePassword(passwordAnswer);
cmd.Parameters.Add( "OIsApproved ", OracleType.Number, 1).Value = (isApproved.ToString().ToLower() == "true ") ? 1 : 0; // 此处存放的是sql中的bool数据类型
cmd.Parameters.Add( "OOracleComment ", OracleType.VarChar, 255).Value = " ";
cmd.Parameters.Add( "OCreationDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OLastPasswordChangedDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OLastActivityDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OApplicationName ", OracleType.VarChar, 255).Value = pApplicationName;
cmd.Parameters.Add( "OIsLockedOut ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OLastLockedOutDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OFailedPWDAttemptCount ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OFailedPWDAttemptWindowStart ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OFailedPWDAnswerAttemptCount ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OFailedPWDAnswerAttemptWinStart ", OracleType.DateTime).Value = createDate;
try
{
conn.Open();
int recAdded = cmd.ExecuteNonQuery();
catch(OracleException ex)
{
throw ex;
}
生成的cmd.commandText如下：
INSERT INTO Users (PKID, Username, OraclePassword, Email, PasswordQuestion, PasswordAnswer, IsApproved, OracleComment, CreationDate, LastPasswordChangedDate, LastActivityDate, ApplicationName, IsLockedOut, LastLockedOutDate, FailedPWDAttemptCount, FailedPWDAttemptWindowStart, FailedPWDAnswerAttemptCount, FailedPWDAnswerAttemptWinStart) Values(:OPKID, :OUsername, :OOraclePassword, :OEmail, :OPasswordQuestion, :OPasswordAnswer, :OIsApproved, :OOracleComment,:OCreationDate, :OLastPasswordChangedDate, :OLastActivityDate, :OApplicationName, :OIsLockedOut, :OLastLockedOutDate,:OFailedPWDAttemptCount, :OFailedPWDAttemptWindowStart, :OFailedPWDAnswerAttemptCount, :OFailedPWDAnswerAttemptWinStart)
报错说是：系统提示错误“ORA-01036: 非法的变量名/编号 ”
请各位大侠帮帮忙了！我在这里先谢谢了！
[解决办法]
参数这么多,难免会出错的啊,建议写个通用的方法吧:)
[解决办法]
太长了,跟踪一下
[解决办法]
太杂了，参数都删了，一个一个添，不出错就加，出错就改。
sqlheper我们用这个类，我不知道对于oracle是否支持
[解决办法]
你的参数写错了，差冒号。
cmd.Parameters.Add( "OPKID ", OracleType.VarChar,255).Value = providerUserKey.ToString();
cmd.Parameters.Add( "OUsername ", OracleType.VarChar, 255).Value = username;
cmd.Parameters.Add( "OOraclePassword ", OracleType.VarChar, 255).Value = EncodePassword(OraclePassword);
cmd.Parameters.Add( "OEmail ", OracleType.VarChar, 128).Value = email;
cmd.Parameters.Add( "OPasswordQuestion ", OracleType.VarChar, 255).Value = passwordQuestion;
cmd.Parameters.Add( "OPasswordAnswer ", OracleType.VarChar, 255).Value = EncodePassword(passwordAnswer);
cmd.Parameters.Add( "OIsApproved ", OracleType.Number, 1).Value = (isApproved.ToString().ToLower() == "true ") ? 1 : 0; // 此处存放的是sql中的bool数据类型
cmd.Parameters.Add( "OOracleComment ", OracleType.VarChar, 255).Value = " ";
cmd.Parameters.Add( "OCreationDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OLastPasswordChangedDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OLastActivityDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OApplicationName ", OracleType.VarChar, 255).Value = pApplicationName;
cmd.Parameters.Add( "OIsLockedOut ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OLastLockedOutDate ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OFailedPWDAttemptCount ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OFailedPWDAttemptWindowStart ", OracleType.DateTime).Value = createDate;
cmd.Parameters.Add( "OFailedPWDAnswerAttemptCount ", OracleType.Number,1).Value = 0;
cmd.Parameters.Add( "OFailedPWDAnswerAttemptWinStart ", OracleType.DateTime).Value = createDate;
上面这些参数必须要和你的SQL语句中的参数一样。
[解决办法]
汗。。。，参数这么多，不如直接做成储存过程算了。不过同意楼上的说法。
