Unable to retrieve fields definition from dataset

[Rod]

I've been following the Lazarus SQLdb tutorial (http://wiki.freepascal.org/SQLdb_Tutorial0 and following), using a SQLite database, and I've managed to get the app to run and display data. This works fine if I set up the dbgrid as per the tutorial and don't try to set up any column definitions in it.

However, as soon as I add columns to the dbgrid and run the app again, no data is displayed. And if I try and link the dbgrid columns to the columns in the database, I get the message "unable to retrieve fields definition from dataset".

Can anyone tell me what I'm missing? Is it a limitation with SQLite?

The reason I want this to work is that I want to write an app which specifies at leas one of the columns as a picklist, so naturally I need to be able to edit the dbgrid columns.

Thanks

Rod

[kapibara]

Hi Rod,

All that works, but you need to know the steps. Have you created a lookup query? And have you created any lookup fields in the query that will feed the DBGrid?

I put together an example of dbgrid with picklists from lookupfields, check it out.

While you create lookupfields, the lookup query must be ACTIVE otherwise you get an error message because the other query cant read the fields from the lookup query. Likewise when you run your app, the lookup query must of course be activated before the query that uses it is activated.

Conveniently, activating a query automatically activates the associated SQLTransaction. And activating the transaction automatically activates the Connection. So the only thing you actually need to do is to activate the lookup query. You dont have to activate connection or transaction manually.

[Rod]

Hi kapibara,

Thanks for this, I'll work through the code. As you have probably guessed I'm new to Lazarus.

When I tried to run the app it said it was unable to open the package zcomponent. I got rid of this reference via the project inspector but is it required?

Now the form loads but when I click on the arrow in the toolbar I get a message that it can't find sqlite3.dll, even though I have copied this into the application folder.

Any ideas?

Regards

Rod

[Rod]

Ok, got it running now - just a case of getting sqlite3.dll in the right place and pointing to the right place for the database!

I will have a play. Thanks again.

[Rod]

Hi kapibara,

I've had a bit of a root round your example and I see how it all works now. I must say it's very neat - hardly a line of code needed.

One thing that kept puzzling me in my own project was that I kept getting a message that the tables the queries were based on didn't exist. Then I realised you have to specify the full path of the databasename in the object inspector. You can't rely on just the database file name, even though the database is actually in the project folder.

[kapibara]

I've had a bit of a root round your example and I see how it all works now. I must say it's very neat - hardly a line of code needed.

Right, no much code. I'm happy you managed to work it through, prolly the most complex design-time wiring of objects that we ever have to deal with in Lazarus.

One thing that kept puzzling me in my own project was that I kept getting a message that the tables the queries were based on didn't exist. Then I realised you have to specify the full path of the databasename in the object inspector. You can't rely on just the database file name, even though the database is actually in the project folder.

Full path and filename of database is needed. There is also something else to say here. If you go ahead and specify only the name without path and then toggle Connection to active at designtime, an EMPTY fresh SQLite database file will immediately be created. Size on disk zero and no tables. When I checked, the file was found in the Lazarus directory, or where Lazarus is set to start. If you dont know this, you may later put a database file in the project directory and start writing queries. The queries will disappoint you since they will "secretly" use the empty database instead and you will get messages for example that "field 'so-and-so' doesn't exist". Because in an empty database without tables there are no fields. Thats the usual "hard way" to learn we must specify full path for the SQLite database in object inspector.

[kapibara]

ZComponent not required or used, I just forgot to remove it.

When I tried to run the app it said it was unable to open the package zcomponent. I got rid of this reference via the project inspector but is it required?