Unable to bind parameter in Insight.Database

Question

I am trying to bind a parameter to a SQL query in my repository but having an error

public IList<Movie> FindMovieById(int movieId)
{
   return Database.Connection().QuerySql<Movie>("select * from myDB.movies where ID=?", new { movieId });
}

I get an OleDb exception.

SQL0313: Number of host variables not valid. Cause . . . . . : The number of host variables or entries in an SQLDA or descriptor area specified in either an EXECUTE or OPEN statement is not the same as the number of parameter markers specified in the prepared SQL statement S000001. If the statement name is *N, the number of host variables or entries in a SQLDA or descriptor area was specified in an OPEN statement and is not the same as the number of host variables specified in the DECLARE CURSOR statement for cursor C000001. Recovery . . . : Change the number of host variables specified in the USING clause or the number of entries in the SQLDA or descriptor area to equal the number of parameter markers in the prepared SQL statement or the number of host variables in the DECLARE CURSOR statement. Precompile the program again.

I have used ? for parameter binding as OleDb has positional parameters which are denoted by '?' rather the '@parameterName'.

Any help is appreciated.

Answer 1

Using Insight.Database can you try this?

return Database.Connection().QuerySql<Movie>(
  "select * from myDB.movies where ID=@movieId", 
  new { movieId = movieId });

Answer 2

In order for Insight to map queries to objects, it binds parameters by name, not by position.

Your parameter object can be an anonymous type. If you specify:

new { movieId }

I believe the compiler will generate a property with the name "MovieID"

In that case, your parameter should be called @movieId.

If your database doesn't support named parameters, please open an issue up on github and I can take a look at it.

https://github.com/jonwagner/Insight.Database/issues