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?

Pablo Santa Cruz · Accepted Answer · 2010-10-16 16:15:50Z

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.

answered Oct 16, 2010 at 16:15

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

andrewheins Over a year ago

When I do that, it can no longer find the coloumn "MediaID".

Milen A. Radev Over a year ago

If you have mixed-case name, quote it but with double quotes not with apostrophes.

andrewheins Over a year ago

Does this mean I should be avoiding mixed case names with PostgreSQL? Is this convention?

andrewheins Over a year ago

This was it! Wow, that's so frustrating. Note to self: Avoid mixed case names in PGSQL.

Collectives™ on Stack Overflow

PostgreSql ERROR: 22P02: invalid input syntax for integer

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related