ORA-01036 illegal variable name/number

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
intrad80
Posts: 5
Joined: Fri 05 Oct 2007 14:11

ORA-01036 illegal variable name/number

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








    


    
    
    
    
    
			
						
			
		
		
		
			
			
			
				'>
				
			
			
			
			
			
		
    


Last edited by intrad80 on Mon 23 Mar 2009 16:20, edited 1 time in total.

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

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
Posts: 5
Joined: Fri 05 Oct 2007 14:11

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
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Post by intrad80 » Tue 24 Mar 2009 10:48

Getting same error with gridview, any ideas?

Code: Select all


			 





    


    
    
			
	       
		         
		                   
			   
     

     
       
           
           
           
    
    
    



Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

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
Posts: 5
Joined: Fri 05 Oct 2007 14:11

Post by intrad80 » Tue 24 Mar 2009 14:04

Thank you!!. Works great with Original_ID_EMP_0000 parameter :) !

histu
Posts: 7
Joined: Fri 09 Oct 2009 08:57

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

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
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

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

Post Reply