System.Data.OracleClient.OracleException: ORA-01036: 非法变量名/编号 [英] System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

问题描述

我正面临这个错误，它告诉我我正在使用一个非法的变量或数字，它在我的代码中突出显示了这一行Line 34:rowsAffected = command.ExecuteNonQuery();.我想我在需要根据 Oracle 格式更改的参数中存在问题但不确定.我确实用 p.Course_id 替换了所有的 @，然后是 ?p.course，p_course_id，就像我在我在 oracle 中的存储过程，但它们都不起作用.我仍然遇到同样的错误.请帮我解决这个问题.谢谢

I'm facing this error that telling me I'm using an illegal variable or number and it highligh this line in my code Line 34:rowsAffected = command.ExecuteNonQuery();. I think I have the issue in the parameters that need to be changed based on Oracle format but not sure. I did replace all the @ with p.Course_id, then ?p.course, p_course_id as I did in my stored procedure in oracle but none of them work. I'm still getting same error. Please help me sort out this issue. Thank you

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;



public class PostForum
{
    public static int INSERTforum(int course_Id, string question, string posterName, DateTime blog_date)
    {
        int rowsAffected = 0;

        using (OracleConnection connection = ConnectionManager.GetDatabaseConnection())
        {
            OracleCommand command = new OracleCommand("INSERTforum", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("@course_Id", SqlDbType.Int).Value = course_Id;
            command.Parameters.Add("@question", SqlDbType.VarChar).Value = question;
            command.Parameters.Add("@posterName", SqlDbType.VarChar).Value = posterName;
            command.Parameters.Add("@blogdate", SqlDbType.DateTime).Value = blog_date;


            rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;

    }
}

这是我的存储过程

CREATE OR REPLACE PROCEDURE INSERTforum(
       p_course_id IN forum.COURSE_ID%TYPE,
       p_question IN forum.QUESTION%TYPE,
       p_postername IN forum.POSTERNAME%TYPE,
       p_blogdate IN forum.BLOG_DATE%TYPE)
AS
BEGIN

  INSERT INTO forum ("COURSE_ID", "QUESTION", "POSTERNAME", "BLOG_DATE") 
  VALUES (p_course_id, p_question,p_postername, p_blogdate);

  COMMIT;

END;
/

推荐答案

我认为您的问题是由于在您的 Add 方法调用中使用了无效的枚举而引起的

I think that your problem is raised by the use of an invalid enum in your Add methods calls

如果您运行此代码，您可能会注意到 OracleType for Int32 与 SqlDbType

If you run this code, you could notice that the OracleType for Int32 is not the same of SqlDbType

OracleType e = OracleType.Int32;
int i = (int)e;
Console.WriteLine(i.ToString());   // Output = 28
SqlDbType z = SqlDbType.Int;
i = (int)z;
Console.WriteLine(i.ToString());   // Output = 8

因此，我建议为您的 ADO.NET 提供程序使用正确的枚举.

So, I suggest to use the correct enum for your ADO.NET provider.

有趣的是，使用 SqlDbType 而不是 OracleType 调用 Add 是被接受的，并且不会引发编译器时错误.发生这种情况是因为 Add 方法有一个重载，它接受一个对象作为第二个参数(它用于在构造参数时直接传递一个值).

It is interesting to note that calling Add with SqlDbType instead of OracleType is accepted and don't raise a compiler time error. This happens because the Add method has an overload that accepts an object as second parameter (It is used to pass directly a value when constructing the parameter).

另一种方法是使用 OracleParameterCollection

   command.Parameters.AddWithValue("@course_Id", course_Id);
   command.Parameters.AddWithValue("@question", question);
   command.Parameters.AddWithValue("@posterName", posterName);
   command.Parameters.AddWithValue("@blogdate", blog_date);

这篇关于System.Data.OracleClient.OracleException: ORA-01036: 非法变量名/编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！