PostgreSql ERROR: 22P02: invalid input syntax for integer

Question

I'm trying to use prepared statements in PostgreSQL, but it's giving me some pretty frustrating errors.

I'm trying to select a single record from the DB, by MediaID. In the DB, MediaID is a Serial Integer. In the class structure, it's an int.

When I call sql.Prepare(), however, it's telling me that I have invalid input syntax for MediaID. I don't see how that could be the case.

NpgsqlCommand sql = mediaRepository.CreateCommand();

sql.CommandText = "SELECT * FROM Media WHERE 'MediaID' = :MediaID";
sql.Parameters.Add(new NpgsqlParameter("MediaID", NpgsqlDbType.Integer));
sql.Prepare();
sql.Parameters["MediaID"].Value = id;

The frustrating part is that if I set miscast the int as NpgsqlDbType.Varchar, it prepares just fine - it just doesn't return any information.

Any ideas?

Answer 1

Try changing this sentence:

sql.CommandText = "SELECT * FROM Media WHERE 'MediaID' = :MediaID";

Into this:

sql.CommandText = "SELECT * FROM Media WHERE MediaID = :MediaID";

Note the removal on single quoted from the MediaID field.