SQL Server 2005 throught OLEDB : Named parameter problem ?

21 Jul 2006 7:05 PM

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 syntaxe 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

22 Jul 2006 3:15 PM

Dan Guzman

OleDb uses '?' parameter tokens rather than the '@ParameterName' tokens in
the SQL statement string. Consequently, parameters are positional (mapped
in the order parameters are added to the command parameters collection).
However, you can specify meaningful OleDbParameter names so that you easily
reference the parameters by name rather than ordinal in your application
code.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Sylvain Devidal" <administra***@manga-torii.com> wrote in message
news:00C69731-CB92-4445-B746-35314BE6BCA9@microsoft.com...
> 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 syntaxe 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