Tuesday, October 30, 2007

"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.

---

5 comments:

Ashok Krishnamoorthy said...

Thanks... It will reduce our time in fixing the error if we face it in future...

ஜமீல் said...

Dear Murugan,

You Have started a great job. Even I don't know anything regarding .Net technologies, I enjoyed very much the flow and content what you come to explore.

And I forwarded this article to some of my friends who are strong (as per my perception)in .Net Tech.
They also replied nice...!

Congrats..! All The Best Murugan!

From Jameel.

ganainfo said...

hi muruga

Best wishes to create this blog.

because im also in .net field. I'm

also willing to post some article

which one i knows in my best

level. This is good oppersunity

for share our thoughts.

nice
..
...All the Best Muruga..

With Regards
gana

sophia said...

Hai,
Well and Good,Job which you done are great and we most welcoming it.
Best Wishes for ur upcoming success.......

sophia said...

Hai,
Well and Good,Job which you done are great and we most welcoming it.
Best Wishes for ur upcoming success.......