ORA-01036 illegal variable name/number

ORA-01036 illegal variable name/number

Postby 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
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register assembly="Devart.Data.Oracle.Web, Version=5.0.22.0, Culture=neutral, PublicKeyToken=09af7300eec23701" namespace="Devart.Data.Oracle.Web" tagprefix="cc1" %>





   


   

   

   
   

             ConnectionString="User Id=ceinsa;Server=internet1;Unicode=True;Direct=true;Sid=orcl;password=xxx"
         SelectCommand="SELECT * FROM EMP_2008"
         UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME ">
         
                  
         

      
      

               DataSourceID="OracleDataSource1" ForeColor="#333333">
         
         
         
            
            
         

         
         
         
         
      

   



Last edited by intrad80 on Mon 23 Mar 2009 16:20, edited 1 time in total.
intrad80
 
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Postby 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">
         
                 
                 
         
Shalex
Devart Team
 
Posts: 7287
Joined: Thu 14 Aug 2008 12:44

Postby 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
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<%@ Register assembly="Devart.Data.Oracle.Web, Version=5.0.22.0, Culture=neutral, PublicKeyToken=09af7300eec23701" namespace="Devart.Data.Oracle.Web" tagprefix="cc1" %>





   


   

   

   
   

             ConnectionString="User Id=ceinsa;Server=internet1;Direct=true;Sid=orcl;password=xxxx"
         SelectCommand="SELECT E_PD_SURNAME,ID_EMP_0000 FROM EMP_2008"
        UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :ID_EMP_0000 ">
         
                 
                 
         

         
      

                     DataKeyNames ="E_PD_SURNAME" DataSourceID="OracleDataSource1" ForeColor="#333333">
            
            
            
               
               
            
             
            
            
            
         

   




intrad80
 
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Postby intrad80 » Tue 24 Mar 2009 10:48

Getting same error with gridview, any ideas?

Code: Select all
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>
          <%@ Register assembly="Devart.Data.Oracle.Web, Version=5.0.22.0, Culture=neutral, PublicKeyToken=09af7300eec23701" namespace="Devart.Data.Oracle.Web" tagprefix="cc1" %>





   


   

   

                     DataSourceID="SqlDataSource1" AutoGenerateEditButton="true" DataKeyNames="ID_EMP_0000"
           AutoGenerateColumns="False">
          
               
                        
            

     


                       UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :ID_EMP_0000"
                ConnectionString="User Id=ceinsa;Server=internet1;Direct=true;Sid=orcl;password=XXXX" ProviderName ="Devart.Data.Oracle">
       
           
           
       
   
   

   

   



intrad80
 
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Postby Shalex » Tue 24 Mar 2009 12:55

Please replace your code
Code: Select all
                       UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :ID_EMP_0000"
                ConnectionString="User Id=ceinsa;Server=internet1;Direct=true;Sid=orcl;password=XXXX" ProviderName ="Devart.Data.Oracle">
       
           
           
       
   
   
with the following code
Code: Select all
                       UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :Original_ID_EMP_0000"
                ConnectionString="User Id=ceinsa;Server=internet1;Direct=true;Sid=orcl;password=XXXX" ProviderName ="Devart.Data.Oracle">
       
           
           
       
   
   
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.
Shalex
Devart Team
 
Posts: 7287
Joined: Thu 14 Aug 2008 12:44

Postby intrad80 » Tue 24 Mar 2009 14:04

Thank you!!. Works great with Original_ID_EMP_0000 parameter :) !
intrad80
 
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Postby 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
 
Posts: 7
Joined: Fri 09 Oct 2009 08:57

Postby histu » Thu 21 Jan 2010 15:28

Hello

It seems the problem is solved. I forget to use Original_ pretag in the where clause.
histu
 
Posts: 7
Joined: Fri 09 Oct 2009 08:57

Postby 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 ).
Shalex
Devart Team
 
Posts: 7287
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle