How to link DBGrids Dataset to DBEdit

[erictan]

I have a form with DBConnection, SQLTransaction, SQL Query, DataSource, DBGrid and 5 DBEdit components.
I can extract and display a database table with 5 fields onto the DBGrid.
Questions
1. How do I linkup the DBGrid with DBEdit such that
    - when I scroll down each record in the DBGrid, the 5 DBEdit data will change accordingly.
    - if I make any change in the DBEdit, the change will reflect in the DBGrid.

2. How do I display the individual fields of the DBGrid Dataset as I believe the Dataset is a temporary table or cursor.

I am new to Lazarus and any advice will be greatly appreciated.

[Zoran]

I have a form with DBConnection, SQLTransaction, SQL Query, DataSource, DBGrid and 5 DBEdit components.
I can extract and display a database table with 5 fields onto the DBGrid.
Questions
1. How do I linkup the DBGrid with DBEdit such that
    - when I scroll down each record in the DBGrid, the 5 DBEdit data will change accordingly.
    - if I make any change in the DBEdit, the change will reflect in the DBGrid.

2. How do I display the individual fields of the DBGrid Dataset as I believe the Dataset is a temporary table or cursor.

I am new to Lazarus and any advice will be greatly appreciated.

If I understand well what you want, the following is enough (I assume that all db components you had already set well -- if DBGrid works as you say, than it is so already):

For every DBEdit:
 - Set DBEdit's DataSource property to the same DataSource which DBGrid uses.
 - Then, in Object Inspector set DBEdit's Field property to name of the db field this DBEdit should be linked to.

Then, everything you describe should just work as you want.

[erictan]

From the Object Inspector for each DBEdit, when I click the DataField, an error message pop out "Unable to retrieve fields definition from Dataset.
I had this error before during my testing and could not figure out how to fix this error.

[erictan]

I have no problem extracting and displaying the data onto the DBGrid.
I also set the DBEdit DataSource to be the same as DBGrid DataSource.
For each DBEdit, when I click the DataField property I am expecting to see a list of fields from the DataSource for me select which field I want to link.
But the error message popup to say "Unable to retrieve fields definition from Dataset".

[JanRoza]

The error message you get from the Object Inspector is probably caused by the fact that at designtime your database connection and datasource are not active, so the object inspector cannot retrieve the fieldnames. But that doesn't have to be a problem, just click away the error meesage and type in the fieldname yourself.

[erictan]

I did bypass the error message and enter the field directly but when the form try to extract the data another error message popup to say  "field not found"

By the way, is the Dataset a temporary table or Cursor and how can I query the Dataset fields directly.
 

[JanRoza]

Hard to say without seeing your code.
If you send me a personal mail I'll send you a database example project which contains most things you use: connection, transaction, queries, datasources, dbgrids and dbedits (unfortunately the forum does not allow me to attach a 700 kb zip-file).
It's an old project I once used for a magazine tutorial but it contains all to get you up and running with databases.
This example uses an SQLite database and I just tested it with Lazarus 2.0.8 (32-bit).
Sometimes seeing is better than reading.   

[erictan]

Thanks JanRoza, I have attached the project for your checking.
As you can see from the attached file, it is basically a very basic form testing the functionality of DBGrid and DBEdit.
I am using an old Firebird Database for this testing which is not attached to the zip file as it is too large in size, you can use any of your database for this testing. I also did not include the Library as it is too large too.

Hope you can fix the problem.

[gsa]

Thanks JanRoza, I have attached the project for your checking.
As you can see from the attached file, it is basically a very basic form testing the functionality of DBGrid and DBEdit.
I am using an old Firebird Database for this testing which is not attached to the zip file as it is too large in size, you can use any of your database for this testing. I also did not include the Library as it is too large too.

Hope you can fix the problem.

Set the property SQL of SQLQuery at design time to 'select * from customer'. Afterwords set 'Connection' of DBConnection to 'Connected' and set 'Active' of SQLQuery to true.

You will see the result immediately in the dbgrid. Now you can select the desired datafield in DBEdit1.

Gerd

[JanRoza]

I don't see any immediate errors, but for practical reason I would give your query a more unique and self explanatory name like 'qryCustomer'.
For changing the sql text of a query it is enough to set the query to inactive and after the change to active again instead of opening and closing it.

Code: Pascal  [Select][+][-]

1. procedure TfrmDBConnection.btnShowDataClick(Sender: TObject);
2. begin
3.   DBConnection.Connected:= True;
4.   SQLTransaction.Active:= True;
5.  
6.   qryCustomer.Active := False; // Just making a query inactive is enough for assigning a new sql string
7.   qryCustomer.SQL.Text:= 'select * from CUSTOMER';
8.   qryCustomer.Active := True; // This is enough to activate the query using the sql statement you just set
9. end;
10.  

Like I said before, if you want a sample project using most database basics send me a personal mail so I can attach the project in my reply to you.

[erictan]

Thanks Gsa and JanRoza, will definitely try your suggestions and update you asap.

[erictan]

I just tried GSA's suggestion and it works well as suggested.
Under DataField for each DBEdit, I am now able to see the list of fields to select for each DBEdit.
Setting the "Select * from Customer;" SQL property of SQLQuery at design time seems to trigger the field list for DBEdit DataField property.
I set the 'Active' of SQLQuery to false as I want the DBGrid data to be shown only after I click the "Show Data" button and this works too.
I would like to explore this solution more to see how I can expand the functionality further.

I would next like to try JanRoza's suggestion and definitely take up his offer on learning more database basics.
I will update you all again probably tomorrow as it is past mid-night Singapore time.

Thank you GSA and JanRoza again, really appreciate your help.

[erictan]

I just couldn't sleep without testing JanRoza's suggestion and it also works well too.
The only thing I had to change on the sample code is from 'qryCustomer' to 'SQLQuery' otherwise the error 'qryCustomer not found' will popup.
Thanks JanRoza, will follow up with you on PM.

[Zoran]

There are good database tutorials in our Wiki. See Databases portal