Ownsight

?找回密码
?立即注册
搜索
热搜: 活动 交友 discuz
查看: 510|回复: 0
打印 上一主题 下一主题

oracle ORA-01036 非法的变量名/编号,(解决)

[复制链接]

803

主题

903

帖子

3201

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
3201
跳转到指定楼层
楼主
发表于 2018-6-30 15:54:50 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
转自:https://www.cnblogs.com/meimao5211/p/3382968.html

下边的代码就会造成??ORA-01036 非法的变量名/编号

C#代码??收藏代码
cmd.CommandText = "SELECT * FROM kk.kkyh WHERE id = @comboBox1 and password = @textBox1 ";??

cmd.Parameters.Add("@comboBox1", OracleType.VarChar).Value = comboBox1.Text;??
cmd.Parameters.Add("@textBox1", OracleType.VarChar).Value = textBox1.Text;??


把SQL中的@改成:,把.Add中的@去掉,就正常了



C#代码??收藏代码
cmd.CommandText = "SELECT * FROM kk.kkyh WHERE id = :comboBox1 and password = :textBox1 ";??

cmd.Parameters.Add("comboBox1", OracleType.VarChar).Value = comboBox1.Text;??
cmd.Parameters.Add("textBox1", OracleType.VarChar).Value = textBox1.Text;??


黑色头发??http://heisetoufa.iteye.com





===============================================================



最近写程序,用EnterpriseLibrary往Oracle中插入数据,出现错误ORA-01036:非法的变量名/编号,代码如下:
DbConnection m_con = g_db.CreateConnection();
m_con.Open();
DbCommand m_com = g_db.GetSqlStringCommand("insert into USER_DEPARTMENT(DEPARTNAME,PARENTDEPID) values(@departname,@parentdepid)");
? ?? ?? ?? ?g_db.AddInParameter(m_com, "@departname", DbType.String, this.TextBox1.Text);
? ?? ?? ?? ?g_db.AddInParameter(m_com, "@parentdepid", DbType.String, this.TreeView1.SelectedNode.Value);
g_db.ExecuteNonQuery(m_com);
m_con.Close();
若换成
DbCommand m_com = g_db.GetSqlStringCommand("insert into USER_DEPARTMENT(DEPARTNAME,PARENTDEPID) values('" + this.TextBox1.Text + "','" + this.TreeView1.SelectedNode.Value + "')");
则没有问题,上网查也没找到解决办法,经过一番探索,终于找到原因了,Oracle中好象不支持@,把@换成:就可以了,代码如下:
DbConnection m_con = g_db.CreateConnection();
m_con.Open();
DbCommand m_com = g_db.GetSqlStringCommand("insert into USER_DEPARTMENT(DEPARTNAME,PARENTDEPID) values(:departname,:parentdepid)");
? ?? ?? ?? ?g_db.AddInParameter(m_com, ":departname", DbType.String, this.TextBox1.Text);
? ?? ?? ?? ?g_db.AddInParameter(m_com, ":parentdepid", DbType.String, this.TreeView1.SelectedNode.Value);
g_db.ExecuteNonQuery(m_com);
m_con.Close();

另外,Oracle中如果不用begin...end,SQL语句中不要有分号(;)。





========================================================



publicbool ModifyDrawBill(MDrawBill mBill)
{
StringBuilder strSQLText =new StringBuilder();
strSQLText.Append("update DrawBill set ");
OracleParameter[] param =
{
new OracleParameter("PKID", OracleType.Int32),
new OracleParameter("StationId", OracleType.VarChar),
new OracleParameter("BillBegin", OracleType.VarChar),
new OracleParameter("BillEnd", OracleType.VarChar),
new OracleParameter("SumCount", OracleType.Int32),
new OracleParameter("DrawDate", OracleType.DateTime),
new OracleParameter("IsConfirm", OracleType.Int32),
new OracleParameter("AppUser", OracleType.NVarChar),
new OracleParameter("CheckUser", OracleType.NVarChar),
new OracleParameter("Memo", OracleType.NVarChar)
};
//@@lwd 20110803
param[0].Value = mBill.PKID;
if (mBill.StationId !=null)
{
strSQLText.Append("StationId=:StationId,");
param[1].Value = mBill.StationId;
}
if (mBill.BillBegin !=null)
{
strSQLText.Append("BillBegin=:BillBegin,");
param[2].Value = mBill.BillBegin;
}
if (mBill.BillEnd !=null)
{
strSQLText.Append("BillEnd=:BillEnd,");
param[3].Value = mBill.BillEnd;
}
if (mBill.SumCount !=null)
{
strSQLText.Append("SumCount=:SumCount,");
param[4].Value = mBill.SumCount;
}
if (mBill.DrawDate !=null)
{
strSQLText.Append("DrawDate=rawDate,");
param[5].Value = mBill.DrawDate;
}
if (mBill.IsConfirm !=null)
{
strSQLText.Append("IsConfirm=:IsConfirm,");
param[6].Value = mBill.IsConfirm;
}
if (mBill.AppUser !=null)
{
strSQLText.Append("AppUser=:AppUser,");
param[7].Value = mBill.AppUser;
}
if (mBill.CheckUser !=null)
{
strSQLText.Append("CheckUser=:CheckUser,");
param[8].Value = mBill.CheckUser;
}
if (mBill.Memo !=null)
{
strSQLText.Append("Memo=:Memo,");
param[9].Value = mBill.Memo;
}
if (strSQLText.ToString().EndsWith(","))
{
strSQLText = strSQLText.Remove(strSQLText.ToString().Length -1, 1);

}
strSQLText.Append(" where PKID=KID ");
return OracleHelper.ExecuteNonQuery(OracleHelper.Connection_String, CommandType.Text, strSQLText.ToString(), param) >0;//提示“RA-01036: 非法的变量名/编号” }



==================================================





string sql = " select rownum as? ?logrow,Id,UserId,UserLogCategoryId,SystemLogCategoryId,Time,ReadT,Title,Content,Permission from LogT where UserId=:UserId and Title like :Title ";
? ?? ?? ?? ?if (Title == null) Title = "";
? ?? ?? ?? ?Title = "%" + Title + "%";
OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":UserId", UserId) , new OracleParameter(":Title", Title)};


======================================================================================================

oracle中错误ORA-01036: 非法的变量名/编号


C#

public Result AddUser(UserEntity user)
? ?? ? {
? ?? ?? ???Result result = new Result(true);
? ?? ?? ???DbCommand dbCmd = db.GetSqlStringCommand("PK_TY_USER.INS_USER");
? ?? ?? ???db.AddInParameter(dbCmd, "id", DbType.Int32, user.Id);
? ?? ?? ???db.AddInParameter(dbCmd, "names", DbType.String, user.Name);
? ?? ?? ???db.AddInParameter(dbCmd, "sex", DbType.String, user.Sex);
? ?? ?? ???db.AddInParameter(dbCmd, "nav", DbType.String, user.Native_Place);
? ?? ?? ???db.AddInParameter(dbCmd, "birday", DbType.DateTime, user.Birthday);
? ?? ?? ???db.AddInParameter(dbCmd, "age", DbType.Int32, user.Age);
? ?? ?? ???try
? ?? ?? ???{
? ?? ?? ?? ?? ?db.ExecuteNonQuery(dbCmd);
? ?? ?? ???}
? ?? ?? ???catch (Exception ex)
? ?? ?? ???{

? ?? ?? ?? ?? ?result.Success = false;
? ?? ?? ?? ?? ?result.Error = ex;
? ?? ?? ?? ?? ?throw ex;
? ?? ?? ???}

? ?? ?? ???return result;
? ?? ? }



oracle



procedure INS_USER(id in integer,names in varchar2,sex in varchar2,nav in varchar2,birday in date,age in integer) is
? ?? ?? ?? ?? ?? ?begin
? ?? ?? ?? ?? ?? ?? ???insert into ty_user values(id,names,sex,nav,birday,age);
? ?? ?? ?? ?? ?? ?end;



oracle中测试能过



==========================================================================
最近写程序时,往Oracle中插入数据,出现错误ORA-01036:非法的变量名/编号,代码如下:

DbConnection conn = GetCon();
? ?? ?? ?? ?conn.Open();
? ?? ?? ?? ?DbCommand cmd = conn.CreateCommand();
? ?? ?? ?? ?string sqlrt= "insert into T_User(Id,Name,Password,E_Mail) VALUES(@id,@name,@password,@email)";

? ?? ?? ?? ?? ? cmd.CommandText = sqlrt;
? ?? ?? ?? ?? ? cmd.Parameters.Add(new SqlParameter("@id", Convert.ToInt32(txtId.Text)));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new SqlParameter("@name", txtName.Text));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new SqlParameter("@password", txtPassword.Text));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new SqlParameter("@email", txtEmail.Text));
? ?? ?? ?? ?int num = cmd.ExecuteNonQuery();
? ?? ?? ?? ?if (num > 0)
? ?? ?? ?? ?{
? ?? ?? ?? ?? ? Response.Write("插入成功");
? ?? ?? ?? ?? ? DataLoad();
? ?? ?? ?? ?}

解决方案是:

? ?? ?? ? DbConnection conn = GetCon();
? ?? ?? ?? ?conn.Open();
? ?? ?? ?? ?DbCommand cmd = conn.CreateCommand();
? ?? ?? ?? ?string sqlrt= "insert into T_User(Id,Name,Password,E_Mail) VALUES(:id,:name,:password,:email)";

? ?? ?? ?? ???cmd.Parameters.Add(new OracleParameter(":id", Convert.ToInt32(txtId.Text)));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new OracleParameter(":name", txtName.Text));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new OracleParameter(":password", txtPassword.Text));
? ?? ?? ?? ?? ? cmd.Parameters.Add(new OracleParameter(":email", txtEmail.Text));

在Oracle数据库中,参数替换不能使用"@",要使用":",将程序中的@替换成就可以解决这个问题了

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表