Server Error in '/' Application.


ORA-01036: illegal variable name/number

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

 

Cause

This error occurs when the parameters are not properly passed to the SQLDatasource or Command object when connecting to Oracle Database using System.Data.OracleClient.

 

Issue

The solution to this problem is to correctly pass the parameters Consider the following Query

SELECT * FROM Customers WHERE Cname = :pCname

 

As you can see above the parameter in Oracle has a prefix : (colon) and not @ like SQL Server Query

When using parameters in query they must start with colon and when adding them they should be added without colon

SelectCommand.Parameters.Add("pCname", OracleType.VarChar, 80).Value="Smith";

 

Same applies to SQLDatasource

SelectCommand="SELECT * FROM Customers WHERE Cname = :pCname"

<SelectParameters>

    <asp:Parameter Name = "pCname"/>

</SelectParameters>

 

The links below will give some additional information

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparametercollection.aspx

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters.aspx

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/41835349-22d6-4bad-b4a2-1dd8e39e15b2

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/579b5a98-ec74-4f6f-b5b4-6919d8bc3e7c

 

If anyone got other solutions please share