DataTable.Open(); with syntax error at or near "LIMIT"

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
econde
Posts: 1
Joined: Tue 16 Jun 2009 15:33
Location: Brazil

DataTable.Open(); with syntax error at or near "LIMIT"

Post by econde » Sat 25 Jul 2009 00:04

dotConnect for PostgreSQL V. 4.55.37.0

Devart.Data.PostgreSql.PgSqlException {"syntax error at or near \"LIMIT\""}
Line Number 807

This error was raised on the .Open() method on the follwoing code:

string pgConnectionString = "host=localhost;port=5432;database=nutriplus;user id=postgres;schema=dicionario";
JLJConnection = new PgSqlConnection(pgConnectionString);
JLJConnection.ConnectionTimeout = 300;
JLJConnection.Open();


PgSqlDataTable DT_01 = new PgSqlDataTable();

DT_01.Connection = JLJConnect;
DT_01.SelectCommand = JLJConnect.CreateCommand();
DT_01.SelectCommand.CommandText = Comando; // "SELECT * FROM dicionario.bdconsulta".....

DT_01.FetchAll = false;
DT_01.NonBlocking = true;
DT_01.StartRecord = 10;
DT_01.MaxRecords = 5;

DT_01.RowFetched += new EventHandler(DT_01_RowFetched);
DT_01.FetchFinished += new EventHandler(DT_01_FetchFinished);
DT_01.Open();

Is there anything wrong?

Waiting for your comments.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 28 Jul 2009 09:07

I can reproduce the mentioned error only in case when the LIMIT keyword is used in the query of the CommandText property and the MaxRecords property of PgSqlDataTable is specified. Please don't use the LIMIT and OFFSET keywords in your query if the StartRecord and MaxRecords properties are set. If the problem persists, please specify the full query text from your CommandText property.

joeyap
Posts: 3
Joined: Mon 05 Jul 2010 13:19

Limit the records return problem

Post by joeyap » Mon 05 Jul 2010 14:59

I using tableadapter in visual studio 2008 to access postgreSQL database. Using Dataset designer.

If I cannot use LIMIT clause. Do you have others suggestion for me to Limit the records returns?

With MSSQL i can use TOP clause in the queries.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 07 Jul 2010 11:14

A workaround (if possible) depends on your select query. For example, try using the WHERE clause to limit your result set. We recommend you to use Devart DataSet Wizard (Devart > MySQL > DataSet Wizard) for creating and modifying your datasets. Devart DataSet Wizard doesn't have problems with MySQL keywords.

joeyap
Posts: 3
Joined: Mon 05 Jul 2010 13:19

Very hard to decide which to use

Post by joeyap » Thu 15 Jul 2010 03:21

using devart dataset wizard to create is find with the 'Limit' keywords but it missing lots of functionality like custom namespace, tableadapter class, and query builder.

Why can't have the 'Limit' clause in the query builder ?

I abit new to the wizard. after generated the dataset how to get the tableadapters to retrive and fill the dataset in code?

Thanks again.

joeyap
Posts: 3
Joined: Mon 05 Jul 2010 13:19

Learning dataset wizard

Post by joeyap » Thu 15 Jul 2010 10:09

Hi, after i try around and do some study. I able to use the dataset wizard to create dataset already.

Just not able to edit the sql with 'limit' clause in it.

What is the dataset manager for ? after i create a few dataset but nothing in the list.

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 15 Jul 2010 10:31

Microsoft's DataSet Editor is not adjusted to work with PostgreSQL, it doesn't parse the PostgreSQL's specific keywords like "limit" or schema names before the table name (e.g., "postgres"."public"."mytable"). We recommend using our DataSet Wizard.

Here is a tutorial that describes usage of DataSet Wizard (editing SQL is possible on step 3 via the "Configure commands" button): http://www.devart.com/dotconnect/postgr ... izard.html.
Our code generation is based on the PgSqlDataTable advanced features. You can fill table after generating Devart DataSet via Tools > PostgreSQL > DataSet Wizard in the following way:

Code: Select all

  MyDataSet ds = new MyDataSet();
  ds.TABLENAME.FetchAll = true;
  ds.TABLENAME.Open(); //or ds.TABLENAME.Active=true;
Also please refer to the Using DataSet Manager article. DataSet is available in DataSet Manager when its instance is created on form designer:
- the "Add this DataSet to designer" option on the last step of Devart DataSet Wizard;
- rebuild your project, and you DataSet will be available in Toolbox - drag&drop it on form designer.

Post Reply