Workaround for the ORA-01008 error when working with .net datasets

When you try to use the original provider dll from Oracle (Oracle.DataAccess.dll ) instead of the one of Microsoft's ( System.Data.OracleClient.dll ) there is a possibility that you will get an annoying error like

ORA-01008: not all variables bound

This is due because the Oracle guys decided to bind the parameters when executing a SQL command by index as default behavior instead of bind them by name. You will get this error when you use more than one parameter.

Consider the following example:
OracleCommand cmd = new OracleCommand());
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM CLIENTS WHERE NAME LIKE :searchParam " + 
                  " OR LASTNAME LIKE :searchParam AND CLIENTTYPE = :idType";

// Then we bind the values to our command
cmd.Parameters.Add(new OracleParameter(":searchParam", "Landry"));
cmd.Parameters.Add(new OracleParameter(":idType", 1));

cmd.ExecuteNonQuery();


Here the Oracle provider is expecting 3 parameters: :searchParam, :searchParam and :idType. For the provider there is still one missing to bind to a value. To correct this problem, you only have to tell your command that your are binding your parameters by ** name **.

cmd.BindByName = true;

Simple, isn't ?

But I'm using a dataset integrated in Visual Studio 2013, what should I do ?

This is a very annoying case because all the data objects code is generated by Visual Studio automatically, and even if you access that code and set the cmd.BindByName = true;, at the moment of doing some modification the code will be regenerated and our line will be removed.

This is what we can do as a workaround:
1. Right click on the adapter that contains your query with the parameter and then click on the "View Code" menu.



2. Once you clicked, Visual Studio will let you add code to the adapter class via a partial class. There you have access to the data objects like the auto-generated command, then you can add a method to set the BindByName property:



3. Call the corrector method before call the method fill of your adapter.