ORA-01036: illegal variable name/number

[Manan]

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

}

}

}

[Jesper Krejberg Petersen]

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.

[Manan]

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

[Jesper Krejberg Petersen]

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

 

[Manan]

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

[Jesper Krejberg Petersen]

Put

cmd.Parameters.Clear();

after

cmd.ExecuteNonQuery(); 

/6

[Manan]

Hey thanks for your help. It worked like a charm.

[Kevin Mason]

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

Thanks