ORA-01036: illegal variable name/number

8 posts, 2 answers
  1. Manan
    Manan avatar
    7 posts
    Member since:
    May 2009

    Posted 02 Jun 2009 Link to this post

    Hi,

    I have tried the following code of RADScheduler to insert data into the tables. The tables are correctly made and the update function is working fine. I cannot find any error in my program perhaps someone could show me one.

    Thank you,
    Manan Podar

    The code for the insert function-

    public

     

    override void Insert(RadScheduler owner, Appointment appointmentToInsert)

     

    {

     

    if (!PersistChanges)

     

    {

     

    return;

     

    }

     

    using (DbConnection conn = OpenConnection())

     

    {

     

    using (DbTransaction tran = conn.BeginTransaction())

     

    {

     

    DbCommand cmd = DbFactory.CreateCommand();

     

    cmd.Connection = conn;

    cmd.Transaction = tran;

    PopulateAppointmentParameters(cmd, appointmentToInsert);

    cmd.CommandText =

    " INSERT INTO DbProvider_Classes (Subject, Start_Date, End_Date, TeacherID) VALUES (:Subject, :Start_Date, :End_Date, :TeacherID)";

     

     

     

    if (DbFactory is OracleClientFactory)

     

    {

     

    //cmd.CommandText += Environment.NewLine + "SELECT SCOPE_IDENTITY()";

     

    cmd.CommandText +=

    Environment.NewLine + "SELECT sequence1.nextval identity FROM dual";

     

     

    //cmd.CommandText = "SELECT seq_sub_form_id.nextval identity FROM dual";

     

    }

     

     

    else

     

    {

    cmd.ExecuteNonQuery();

    cmd.CommandText =

    "SELECT ::ID";

     

    }

     

    int identity = Convert.ToInt32(cmd.ExecuteScalar());//error point

     

    FillClassStudents(appointmentToInsert, cmd, identity);//table class students is a table wo a primary key

    tran.Commit();

    }

    }

    }

  2. Answer
    Jesper Krejberg Petersen
    Jesper Krejberg Petersen avatar
    70 posts
    Member since:
    Dec 2006

    Posted 02 Jun 2009 Link to this post

    Check "PopulateAppointmentParameters" to see that all of the 4 parameters are added to "cmd" and ONLY the 4.
    Your SQL and the number of parameters have to match exactly.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Manan
    Manan avatar
    7 posts
    Member since:
    May 2009

    Posted 02 Jun 2009 Link to this post

    As you said I checked the PopulateAppointmentParameters and it removed that particular error. But now it gives me another problem. The function gives an "ORA-00933: SQL command not properly ended". I have checked the SQL in SQLDeveloper and it works fine. So I cant quite get how this particular error arises. Please help.

    Thank you,

    Manan Podar
  5. Jesper Krejberg Petersen
    Jesper Krejberg Petersen avatar
    70 posts
    Member since:
    Dec 2006

    Posted 03 Jun 2009 Link to this post

    I am not an Oracle expert, but try to replace
     
    cmd.CommandText +=

    Environment.NewLine + "SELECT sequence1.nextval identity FROM dual";

    with

    cmd.ExecuteNonQuery(); 
    cmd.CommandText = "SELECT sequence1.nextval identity FROM dual";

    And... You may find better help with your Oracle issues in an Oracle  forum :)

    /6

     

  6. Manan
    Manan avatar
    7 posts
    Member since:
    May 2009

    Posted 03 Jun 2009 Link to this post

    Hey,

    Thanks for the reply. I tried that too but the "illegal variable name/number" comes up again at the cmd.executescalar() statement. I'll post in the oracle forum too. Please help if you can.

    Thank you,
    Manan Podar
  7. Answer
    Jesper Krejberg Petersen
    Jesper Krejberg Petersen avatar
    70 posts
    Member since:
    Dec 2006

    Posted 03 Jun 2009 Link to this post

    Put

    cmd.Parameters.Clear();

    after

    cmd.ExecuteNonQuery(); 

    /6
  8. Manan
    Manan avatar
    7 posts
    Member since:
    May 2009

    Posted 03 Jun 2009 Link to this post

    Hey thanks for your help. It worked like a charm.
  9. Kevin Mason
    Kevin Mason avatar
    13 posts
    Member since:
    Jan 2007

    Posted 20 Aug 2009 Link to this post

    Would you mind sharing the rest of your Oracle provider code for the sheduler
    in addition to the INSERT function?

    Thanks
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017