"ADO.NET Parameter" usage : Order is matter with OLEDB provider

While we are doing Data Manipulation using ADO.NET, "Parameter" objects for In/Out direction values, specified "Order of Parameters" is matter with OLEDB Provider.

In SQLServer Data Provider, parameters order is NOT a matter, parameters names are important.




For example: From "Emp" table, we need employees with Given Name and Given Salary.
to do this, we need to supply values for two parameters (Name and Salary)

...
...
OLEDBConnection con = new SQLConnection("give connection string here");


string strCommand= "select * from emp where Name=@Name and Salary=@Salary";

OLEDBCommand cmd = new OLEDBCommand(strCommand);
cmd.Connection = con;


OLEDBParameter paramName = new OLEDBParameter( );
paramName.ParameterName ="Name";
paramName.value = "Vignesh";
cmd.parameters.add(paramName);


OLEDBParameter paramSalary = new OLEDBParameter( );
paramSalary.ParameterName ="Salary";
paramSalary.value = "20o00";
cmd.parameters.add(paramSalary);

...
...
...



Here, we are doing with OLEDB Provider. In our example, we added parameter in the same order as in given query. So, it won't create problem.


OLEDB looking for ORDER of parameter, NOT for the parameter name. Check the parameter names given in the Query (they are @Name and @Salary) .


in coding : paramName.ParameterName ="Name"

even you change the parameter name as

   paramName.ParameterName ="NOTName"

It won't be problem, we added that paramName as first in the cmd's parameters collection. so, take care about "order of parameters" while adding to Command object in OLEDB provider.



but in SQLSERVER Provider, parameter name is important, so paramObject.ParameterName must match with Parameter name which is given in the Query.


---

In one of my application, i was affected by this problem [ actually, problem was with me, even i know about OLEDB's behavior in Parameters, i found this issue only after 2 weeks ]

---

for other Data Providers [ Oracle provider and ODBC provider] i will verify and Update soon.

---