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