In this article I will explain the solution to the Exception ORA-01036: illegal variable name/number in ASP.Net while 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
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.
As you can see below the parameter in Oracle has a prefix : (colon) and not @ like SQL Server Query.
SELECT * FROM Customers WHERE Cname = :pCname
Solution
1. Using Code
When using parameters in query, parameters must start with colon and when adding them it should be added 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.
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
© COPYRIGHT 2024 ASPSnippets.com ALL RIGHTS RESERVED.