Posts Tagged ‘error’

ORA-01036- The Comprehensive Guide for Frustrated ASP.Net People

If you’re running into the ambiguous, unhelpful “ORA-01036: illegal variable name/number” error when trying to do an update or an insert in ASP.Net while using a SqlDataSource, here’s some advice that may prevent you from pulling out all of your hair, swearing off technology forever, moving to rural Wyoming and living off the land until the end of your days…

  • Make sure you have the ProviderName specified in your SqlDataSource tag.  It should be System.Data.OracleClient (unless you’re using the Oracle.Whatever.Whatever provider)
  • Make sure you’re using :MyParameterName instead of @MyParameterName.
  • Get rid of OldValuesParameterFormatString in your SqlDataSource tag.
  • Each and every input where you have specified Bind(“FIELD_NAME”) MUST be represented in your UpdateParameters (and/or InsertParameters).  To debug this problem, what I recommend is that you change ALL of your Bind()s to Eval()s and pare down your SQL statements and update/insert parameters to just one or two things…then change the Evals to Binds as you add parameters to your SQL statement.  Every time you get a successful update/insert, add another parameter or two until you’ve found the problem.  More than likely you’ve got an extra input lurking somewhere that isn’t represented in your SQL query and/or parameters.

ORA-01036: illegal variable name/number parameter in ASP.Net app

My Oracle chops are way outdated, and, being a fan of business objects and/or a genuine Data Access Layer, I’m not used to working with typed datasets in asp.net.  So when I got the above error I was pretty stumped, and I started using some obscene anti-Oracle phrases to name a few test variables to figure out wtf was going on.

Eventually I figured out that it’s not a good idea to just enter parameters into a Select method via the Properties window, even though Visual Studio will cheerfully let you do that.  If you don’t have the parameter referenced in the actual SQL of the select method, you’re gonna get the above message when you try using it.  So…

In your Select method’s SQL, be sure to add “WHERE ID = :ID” and Visual Studio should pick up on that and make a parameter for you.  Too bad it doesn’t work the other way around.

When I was trying to find info on this error I found lots of talk about update parameters and other causes, but didn’t see anything related to this particular circumstance.