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
– user2884405, Commented Feb 28, 2014 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
– Michael Petrotta, Commented Feb 28, 2014 at 22:58
Are you tried to use OracleDbType for your parameters type instead of SqlDbType? — Steve
– Steve, Commented Feb 28, 2014 at 22:59
Steve; yes I need to use oracle data type instead of sql because I'm using Oracle database — user2884405
– user2884405, Commented Feb 28, 2014 at 23:38

Steve · Accepted Answer · 2014-02-28 23:14:43Z

1

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

edited Feb 28, 2014 at 23:14

answered Feb 28, 2014 at 23:08

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2884405 Over a year ago

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

Steve Over a year ago

I see, it will be usefull to see the datatype of the fields in the table forum also

Hossein POURAKBAR · Accepted Answer · 2024-05-16 15:41:17Z

0

Perhaps this is your answer: You should remove all character colon (:) from your comments. It is not normal that some drivers are not able to parse and exclude whatever is inside the comments.

answered May 16, 2024 at 15:41

Hossein POURAKBAR

1,2552 gold badges18 silver badges35 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related