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

[econde]

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]

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]

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]

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]

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]

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]

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/postgresql/docs/?DataSetWizard.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.