System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

Question

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;

    }
}

Here is my stored procedure

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;
/

when I remove all the @ it still gives me the same error and it adds another error wrong number or types of arguments in call to 'INSERTFORUM' — user2884405, Feb 28 '14 at 22:56
To help with that, we'd need to see the definition (or at least the parameter list, with types) for the `INSERTforum` stored procedure. — Michael Petrotta, Feb 28 '14 at 22:58
Are you tried to use OracleDbType for your parameters type instead of SqlDbType? — Steve, Feb 28 '14 at 22:59
Steve; yes I need to use oracle data type instead of sql because I'm using Oracle database — user2884405, Feb 28 '14 at 23:38

Steve · Answer 1 · 2014-02-28T23:14:43.580

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

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

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

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).

An alternative is to use AddWithValue of the OracleParameterCollection

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

Thank you for your help. However, the first code I couldn't understand to be honest, but eh second solution I used and nothing changed it still giving me same error.I have added stored procedure that I have hopefully it can clarify my error. thx — user2884405, Feb 28 '14 at 23:39
I see, it will be usefull to see the datatype of the fields in the table `forum` also — Steve, Feb 28 '14 at 23:42

System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

1 Answers1

Linked