If like me you earn your keep writing code to interrogate databases, you’re probably familiar with parameterised queries. If not, take a look here — they offer a way of passing parameters into queries without all that mucking about with formatting for different data types and so on. They also offer (so I’m led to believe) performance gains from the database server. And OLEDB makes it pretty easy to do it, too. So throw away those horrible old queries and switch to parameter queries today!

One thing not often mentioned however is that the parameters have to be in order. Because in the SQL you identify them only as question marks:

SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 = ? AND CRITERIA2 = ?

they have to be added in the correct order. If you add CRITERIA2 then CRITERIA1 to your parameters, then you’ll get unexpected results. The parameter names don’t actually match up to your SQL by name, only by position. This is kinda logical, because if you wanted:

SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 > ? AND CRITERIA1 < ?

both your parameters couldn’t be named CRITERIA1.

For some of my code, I build an INSERT or an UPDATE depending on whether I have a new or updated record to write. To avoid two sets of code, I’ll build the parameter list with the key field last, and though it may seem counter-intuitive, I build the INSERT statement to match. Bad semi-VB pseudocode follows:

If (new record) Then
  SQL = "INSERT INTO TABLENAME (FIELD1, FIELD2, KEYFIELD) VALUES (?, ?, ?)"
Else (updating record)
  SQL = "UPDATE TABLENAME SET FIELD1 = ?, FIELD2 = ? WHERE KEYFIELD = ?"
End If

…then add my parameters FIELD1, FIELD2 and KEYFIELD in that order. Easy.