ORA-01036 illegal variable name/number

intrad80 · Post by **intrad80** » Mon 23 Mar 2009 10:44

Hello, I recieve this exception when i try to update a row with a formview control. The oracle column E_PD_SURNAME is char(20). I use an oracle 10g database and vs2008 3.5 framework and oracle provider Version=5.0.22.0

Exception

Code: Select all

Devart.Data.Oracle.OracleException was unhandled by user code
  Code=1036
  ErrorCode=-2147467259
  Message="ORA-01036 illegal variable name/number"
  Offset=0
  Source="Devart.Data.Oracle"
  StackTrace:
       en Devart.Data.Oracle.s.a(aq[] A_0, Byte[] A_1)
       en Devart.Data.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
       en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
       en Devart.Data.Oracle.Web.OracleDataSource.a.a(CommandBehavior A_0)
       en System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
       en Devart.Data.Oracle.OracleCommand.ExecuteReader(CommandBehavior behavior)
       en Devart.Data.Oracle.Web.OracleDataSource.a.a(CommandBehavior A_0)
       en System.Data.Common.DbCommand.ExecuteReader()
       en Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
       en System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation)
       en System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)
       en System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback)
       en System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation)
       en System.Web.UI.WebControls.FormView.UpdateItem(Boolean causesValidation)
       en _Default.Button2_Click(Object sender, EventArgs e) en E:\ceinsaDE\ASP.NET\GDCEcinfa\Default.aspx.vb:línea 13
       en System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       en System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       en System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       en System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       en System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       en System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:

.ASPX Code

Code: Select all

'>

Shalex · Post by **Shalex** » Mon 23 Mar 2009 16:06

This error was generated because only one parameter is set in your code but our provider generates the whole set of parameters if at least one parameter is set. Please replace your code

Code: Select all

         UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME ">

with the following piece of code

Code: Select all

         UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :ID_EMP_0000">

intrad80 · Post by **intrad80** » Mon 23 Mar 2009 16:19

Thank you but I have the same error with the 2 parameters. My table have only 2 columns ID_EMP_0000 and E_PD_SURNAME and I put the 2 parameters in the UPDATECOMMAND.

Create table:

CREATE TABLE CEINSA.EMP_2008 (
E_PD_SURNAME CHAR(20),
ID_EMP_0000 LONG)
TABLESPACE CEINSAEX
STORAGE (
INITIAL 64K
MAXEXTENTS UNLIMITED
)
LOGGING;

Code: Select all

'>

intrad80 · Post by **intrad80** » Tue 24 Mar 2009 10:48

Getting same error with gridview, any ideas?

Code: Select all

Shalex · Post by **Shalex** » Tue 24 Mar 2009 12:55

Please replace your code

Code: Select all

with the following code

Code: Select all

I.e., add to the name of your id parameter the Original_ prefix. This is a Microsoft issue. We cannot change it.

About the script posted above: if you set the type of your id column to LONG, it cannot be used in the WHERE clause. Please refer to the Oracle documentation.

intrad80 · Post by **intrad80** » Tue 24 Mar 2009 14:04

Thank you!!. Works great with Original_ID_EMP_0000 parameter

!

histu · Post by **histu** » Thu 21 Jan 2010 12:28

Hello Devart Team,

I have got the same problem.

When you said this:
This error was generated because only one parameter is set in your code but our provider generates the whole set of parameters if at least one parameter is set.

The whole set of parameters is the hole fields of the record, or the hole fields of the table? In my case selects only select some fields from the table without parameters.

When I see what sent to the database with the dbmonitor I see that the params are sent like strings and not like values.

Prepare: update iban_felhaszn_jog_fej set C_ELONEV = :C_ELONEV , C_NEV1 = :C_NEV1 where C_OPERNEV = :C_OPERNEV
It means that the parameters are handled like strings or just hide the values and change the parameter names with their value in the background?

histu · Post by **histu** » Thu 21 Jan 2010 15:28

Hello

It seems the problem is solved. I forget to use Original_ pretag in the where clause.

Shalex · Post by **Shalex** » Thu 21 Jan 2010 16:00

Devart OracleDataSource behaviour is like the one of Microsoft SqlDataSource. OracleDataSource uses Microsoft's parameter generation. So you need to adjust your query to the generated parameters collection. You can find this parameters collection using DbMonitor (choose your OracleConnection object in Object Tree > select your your query in the Events window > the View | Parameters menu of DbMonitor). Please refer to MSDN (e.g., http://msdn.microsoft.com/en-us/library/z72eefad.aspx ).