OleDbCommand parameters order and priority

calendar_today Asked Sep 25, 2009
thumb_up 28 upvotes
history Updated April 16, 2026

Question posted 2009 · +13 upvotes

I have been debugging this query for the last 40 minutes, and the problem apparently is the order of the parameters after all.

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

Then I add the parameters this way, notice that the two last parameters are switched, I get no results.

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

When I declare the parameters the way they appear in the queury everything works perfectly.

I thought named parameters were at first place to address this problem! what am I missing here?

Thank you

Accepted answer +28 upvotes

According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand does not support named parameter

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

So order of parameter is important.

Top ms-access Q&A (6)

+28 upvotes ranks this answer #2 out of 55 ms-access solutions on this site — top 4%.
ms-access