Does OleDbCommand Parameter Order Matter When CommandType is Text?

OleDbCommand Parameter Order and Priority: A Case of Misaligned Expectancies

When constructing queries using OleDbCommand, it's crucial to ensure the order of parameters accurately follows the sequence in the SQL statement. A common misconception revolves around the assumption that named parameters eliminate this requirement. However, as revealed by an in-depth debugging session, OleDbCommand lacks support for named parameters when CommandType is set to Text.

In the given example:

SELECT * FROM tblSomeThing WHERE id = @id AND debut = @dtDebut AND fin = @dtFin

The parameters are added in the following order:

cmd.Parameters.Add("@id", OleDbType.Integer).Value = idSociete;
cmd.Parameters.Add("@dtFin", OleDbType.Date).Value = dateTraitementFin;
cmd.Parameters.Add("@dtDebut", OleDbType.Date).Value = dateTraitementDebut;

However, no results are returned, indicating an inconsistency. Upon reordering the parameters to match the SQL statement, the query executes successfully:

cmd.Parameters.Add("@id", OleDbType.Integer).Value = idSociete;
cmd.Parameters.Add("@dtDebut", OleDbType.Date).Value = dateTraitementDebut;
cmd.Parameters.Add("@dtFin", OleDbType.Date).Value = dateTraitementFin;

This aligns with the documentation at http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx, which explicitly states that OleDbCommand does not support named parameters for SQL statements in Text mode. Instead, question mark (?) placeholders must be used, and the order of OleDbParameter objects in the collection must strictly correspond to their placement in the command text.

The above is the detailed content of Does OleDbCommand Parameter Order Matter When CommandType is Text?. For more information, please follow other related articles on the PHP Chinese website!