Thread: Error with "limit" clause

Hi List,
I've been trying the following query from VB.NET: select * from my_table
order by column1 limit 1. This query works flawlessly when I use the
pgAdminIII query tool, but when I run this query from VB.NET, I get the
following error: syntax error at or near "limit". Any ideas as to the cause
of this error? Seems to me this error is specific to the PostgreSQL .NET
providers, what do you think?

If it's of any help, here's my connection string: database=test;User
ID=iceman;Password={suppressed};Server=my_server;Port=5432;Timeout=10

Thanks in advance,
Rommel Edwards.

"Rommel the iCeMAn" <icecrew@gmail.com> writes:
> I've been trying the following query from VB.NET: select * from my_table
> order by column1 limit 1. This query works flawlessly when I use the
> pgAdminIII query tool, but when I run this query from VB.NET, I get the
> following error: syntax error at or near "limit". Any ideas as to the cause
> of this error? Seems to me this error is specific to the PostgreSQL .NET
> providers, what do you think?

Could VB be rewriting the query behind your back?  Turn on log_statement
logging, and look in the postmaster log to see what's really being sent
to the backend ...
        regards, tom lane

> Could VB be rewriting the query behind your back?  Turn on log_statement
logging, and look in the postmaster log to see what's really being sent to
the > backend ...

Exactly!

This was an oversight on my part tho. I had used the following command:

MyDataReader = MyCommand.ExecuteReader(CommandBehavior.SingleRow)

By passing the "CommandBehavior.SingleRow" parameter VB inserted the "limit
1" clause unknown to me. The solution was easy, either explicitly state
"limit 1" in my SQL query OR use the CommandBehavior.SingleRow parameter -
NOT BOTH :-)

Thanks for the input,
Rommel Edwards.