SQL Server 2005 throught OLEDB : Named parameter problem ?

[Sylvain Devidal]

Hello,

I'm designing a web application that must be able to connect different
databases.
So I decided to use OleDb connector instead of SqlClient.
While with SqlClient, I can run queries like "select * from mytable where
col = @param" then giving a value to the parameter, I get an error when
doing the same with SQL Server 2005.
I checked with Access, SQL Server 2000 and Oracle : all accept this syntax
through OleDb. SQL Server 2005 doesn't.
I have to use old school way : "select * from mytable where col = ?". But
this syntax is bad coz I'm not sure of the parameters order.

Why ?

..NET 2.0 / C# language
Windows 2003 Server Entreprise Edition SP 1 / IIS 6
SQL Server 2005 Express with advanced

 

[Paul Clement]

On Fri, 21 Jul 2006 21:31:03 +0200, "Sylvain Devidal"

¤ Hello,
¤
¤ I'm designing a web application that must be able to connect different
¤ databases.
¤ So I decided to use OleDb connector instead of SqlClient.
¤ While with SqlClient, I can run queries like "select * from mytable where
¤ col = @param" then giving a value to the parameter, I get an error when
¤ doing the same with SQL Server 2005.
¤ I checked with Access, SQL Server 2000 and Oracle : all accept this syntax
¤ through OleDb. SQL Server 2005 doesn't.
¤ I have to use old school way : "select * from mytable where col = ?". But
¤ this syntax is bad coz I'm not sure of the parameters order.
¤
¤ Why ?

OLEDB under ADO.NET does not support named parameters. You would have to use the
..NET native provider for SQL Server for named parameter support.

Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Sylvain Devidal]

Hello Paul,

On the net, I can find many people using named parameters with Access
throught OLEDB with .NET :
http://webdeveloper.com/forum/showthread.php?mode=hybrid&t=105975
http://www.mcse.ms/archive94-2005-6-1707110.html
http://p2p.wrox.com/topic.asp?TOPIC_ID=29906&whichpage=1

And this one (a guy who have the same problem, with MSSQL 2005) :
http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21786841.html
Yurich and existenz2 told they can run the code with no problem.
That's why I'm not sure ADO.NET can't use named parameters with OLEDB.

Also, I'm pretty sure I already wrote similar code against SQL Server 2000
and hadn't any issue.

It really seems only SQL Server 2005 have this issue...

 

[Paul Clement]

¤ Hello Paul,
¤
¤ On the net, I can find many people using named parameters with Access
¤ throught OLEDB with .NET :
¤ http://webdeveloper.com/forum/showthread.php?mode=hybrid&t=105975
¤ http://www.mcse.ms/archive94-2005-6-1707110.html
¤ http://p2p.wrox.com/topic.asp?TOPIC_ID=29906&whichpage=1
¤
¤ And this one (a guy who have the same problem, with MSSQL 2005) :
¤ http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21786841.html
¤ Yurich and existenz2 told they can run the code with no problem.
¤ That's why I'm not sure ADO.NET can't use named parameters with OLEDB.
¤
¤ Also, I'm pretty sure I already wrote similar code against SQL Server 2000
¤ and hadn't any issue.
¤
¤ It really seems only SQL Server 2005 have this issue...

No, it's an issue with all OLEDB providers when used with ADO.NET.

You can used named parameters but they are ignored. Parameters are evaluated based upon their
ordinal position in the parameters collection and not their name.


Paul
~~~~
Microsoft MVP (Visual Basic)