In this article I will explain with an example, how to resolve the following error.
ORA-01036: illegal variable name/number.
 
 

Error

The following error occurs when the parameters are not properly passed to the SqlDatasource Command object when we are connecting to Oracle Database.
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
 
 

Issue

The solution to this problem is to correctly pass the parameters.
Please refer following query, as you can see the below parameter in Oracle has a prefix :(colon) and not @ like SQL Server query.
SELECT * FROM Customers WHERE Cname = :pCname
 
 

Solution

1. Using Code
When we use parameters in query, it must start with colon and when we add them, it should be used without colon.
C#
SelectCommand.Parameters.Add("pCname", OracleType.VarChar, 80).Value = "Smith";
 
VB.Net
SelectCommand.Parameters.Add("pCname", OracleType.VarChar, 80).Value = "Smith"
 
 
2. Using SqlDataSource
In similar way, for SqlDataSource parameters must be added without colon.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT * FROM Customers WHERE Cname = :pCname">
    <SelectParameters>
        <asp:Parameter Name="pCname" />
    </SelectParameters>
</asp:SqlDataSource>