This code works when the connection is made to an accdb database:
Dim customer = connection.Query(Of Klantgegevens)("Select Actief,Onderhoudscontract From Klantgegevens Where Klantnummer=@Idx", New With {.Idx = customerId}).SingleOrDefault
But the code below gives the error about the Idx parameter when the connection is made to a SQL server database that has a table with the same structure:
Dim customer = connection.Query(Of Klantgegevens)("Select Actief,Onderhoudscontract From [dbo.Klantgegevens] Where Klantnummer=@Idx", New With {.Idx = customerId}).SingleOrDefault
What is going wrong here? I had hoped that by using Dapper I would be able to write database agnostic code. But it seems that is not the case!
If you are using an ODBC/OLEDB connection, then my first suggestion would be: move to SqlClient (SqlConnection). Everything should work fine with SqlConnection.
If you can't do that for some reason - i.e. you're stuck with a provider that doesn't have good support for named parameters - then you might need to tell dapper to use pseudo-positional parameters. Instead of @Idx, use ?Idx?. Dapper interprets this as an instruction to replace ?Idx? with the positional placeholder (simply: ?), using the value from the member Idx.
This is also a good fix for talking to accdb, which has very atypical parameter usage for an ADO.NET provider: it allows named parameter tokens, but all the tokens all replaced with ?, and given values from the positions of the added parameters (not via their names).