SqlOleDb StoredProc Parameter Error

Topics: Data Access Application Block
Mar 28, 2007 at 9:32 AM
There is a subtle bug in using 'providerName=System.Data.SqlOleDb' (rather than SqlClient). The stored procedure paramaters are matched by sequence rather than by name. This will not cause an error if the parameter data types match, but will simply write the wrong values into the database. It showed up in my code recently because I have some datatime parameters which objected to being assigned an integer value.
Mar 28, 2007 at 1:32 PM
Hi Mike,

It isn't a bug. This is the case with all overloads that accept parameter discovery as such:

int ExecuteNonQuery(string storedProcedureName, params object[] parameterValues);

The parameters are discovered by the built-in SqlCommandBuilder.DeriveParameters method in the same sequence as placed in the stored procedure, and that is how they are used within the DAAB.

I talk about parameter discovery a bit more here:

SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials




David Hayden
Microsoft MVP C#