Recent

Author Topic: Displaying Data from DBGrid into DBFields - Error Hooking up fields  (Read 1027 times)

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Okay... reached another blockade here.

Using the SQL DB components.
Got all hooked up
Can display data in DBGird.

However, trying to hook up DBedit controls generates an "Unable to retrieve data-set fields".
And if I mess with the SQL components to much, it then breaks the app (I don't know why)

I get the "Unable to retrieve... " error when I try to set the datasource and enter in the datafield of each DBedit control.

I followed the tutorial from here, but there is nothing about hooking up fields in design or run-time.
http://wiki.freepascal.org/SQLdb_Tutorial0

What I am trying to accomplish is just to hookup my fields to the data-set/grid so that when a user clicks a row/cell,
all the fields populate with that data.

Screenshot


« Last Edit: April 24, 2019, 02:50:41 am by pixelink »
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

valdir.marcos

  • Hero Member
  • *****
  • Posts: 706
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #1 on: April 24, 2019, 04:02:57 am »
Okay... reached another blockade here.

Using the SQL DB components.
Got all hooked up
Can display data in DBGird.

However, trying to hook up DBedit controls generates an "Unable to retrieve data-set fields".
And if I mess with the SQL components to much, it then breaks the app (I don't know why)

I get the "Unable to retrieve... " error when I try to set the datasource and enter in the datafield of each DBedit control.

I followed the tutorial from here, but there is nothing about hooking up fields in design or run-time.
http://wiki.freepascal.org/SQLdb_Tutorial0

What I am trying to accomplish is just to hookup my fields to the data-set/grid so that when a user clicks a row/cell,
all the fields populate with that data.
Are you trying to program a CRUD form?
Are you using Firebird? What version?

mangakissa

  • Hero Member
  • *****
  • Posts: 900
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #2 on: April 24, 2019, 09:09:16 am »
I'm going to look into my cards to see what you're doing.
They say you want to create a kind of filter for your grid. That will not work with DBEdit controls. These are made to show data. When data is modified it wil save it (after calling property post). That's not what you want.

Lets say you want to filter the field status. All these kind of different status can be stored into a combobox (not a DBCombobox). When you selected a kind of status in the box, filtering can be done with three options:
- using property filter
- using event onfilterrecord
- create a new query with a 'WHERE' clause build in.

If you are usng a join in your query (hopefully it is), then extra code must be programmed to find the proper value of the displaydata.

For example see small attached demo.

Good luck. If you save more questions, don't hesitate. But giving us more code tell much more than my cards.
« Last Edit: April 24, 2019, 09:36:00 am by mangakissa »
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #3 on: April 24, 2019, 01:25:27 pm »
I am Using SQLIte3.

I don't want the user to edit the grid directly.
Because the Script column contains large amount of RTF text.
That will have to be extracted into a RichMemo control, edited there and saved back into the data-set.

Also, does DBMemo allow for editing of RichText, if not will have to use RichMemo?

There are no other tables, thus no joins.

I will be using CRUD.

Thus why, I want to populate the DB controls (text fields) with the data when they click on a row in the DBGrid..

Filtering is easy... just use another sql statement and refresh the data-source(data-set)
Already have that figured out and working.

If you don't think DBGrid is needed here, then what? Can I use ListView??

I am thinking here, because of the RTF text that needs to be handled, I may not be able to hook up RichMemo control to the Data-set directly. I may need to handle the CRUD all by code.

That is why in the 1st place I don't want the user to edit the grid directly.

Thanks

« Last Edit: April 24, 2019, 05:31:33 pm by pixelink »
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #4 on: April 24, 2019, 01:37:04 pm »
I'm going to look into my cards to see what you're doing.
They say you want to create a kind of filter for your grid. That will not work with DBEdit controls. These are made to show data. When data is modified it wil save it (after calling property post). That's not what you want.

Lets say you want to filter the field status. All these kind of different status can be stored into a combobox (not a DBCombobox). When you selected a kind of status in the box, filtering can be done with three options:
- using property filter
- using event onfilterrecord
- create a new query with a 'WHERE' clause build in.

If you are usng a join in your query (hopefully it is), then extra code must be programmed to find the proper value of the displaydata.

For example see small attached demo.

Good luck. If you save more questions, don't hesitate. But giving us more code tell much more than my cards.

There is no code yet.

All i did was hook up the SQL components to the data-grid.
And all i want to do is hook up the fields, but it generates an error every time I try to add fields to the SQL Query or Data-source components in design-time (in properties panel).
Thus no code yet.
« Last Edit: April 24, 2019, 01:58:08 pm by pixelink »
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #5 on: April 24, 2019, 05:09:37 pm »
Okay... not getting much help.

So, lets kind of start over.

I am supplying the project that doesn't work here. (its 1.28 MB because of SQL DLL - no exe)
https://www.pixelink.media/downloads/SQL_DBGrid_CRUD.zip

FYI... in this demo project, the Column "Script" is html text.
But, eventually I will be using RTF formatted text w/RTF codes... so ignore the text that is actually in that field for now.

All I am trying to do is get a working CRUD project finished.

The current issue is
I cannot get the DB controls hooked up to the data-source/data-set/grid
I get "Unable to retrieve data-set fields" error. I try to add the fields in sqlQuery and sqlDatasource components

I followed the WIKI tutorial as stated above, but no mention of how to actually add fields to Datasource and hook up db controls to datasource/grid.

Here is the screen of the actual project.

Thanks in Advance.

« Last Edit: April 24, 2019, 05:16:31 pm by pixelink »
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #6 on: April 24, 2019, 05:18:58 pm »
Also.... another issue....

The "DBGrid1.DataSource.DataSet.RecordCount" is returning the wrong number of records.

Code: Pascal  [Select]
  1.  
  2. lbFilter.Caption:='Records: ' + IntToStr(DBGrid1.DataSource.DataSet.RecordCount);
  3.  
  4.  
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

paweld

  • Full Member
  • ***
  • Posts: 177
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #7 on: April 24, 2019, 06:26:28 pm »
Code: Pascal  [Select]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   ...
  4.  
  5.   {actually connect}
  6.   sqlConn.Connected:= True;
  7.   sqlTransaction.Active:= True;
  8.  
  9.   sqlQuery.PacketRecords:=-1;    //fetch all records
  10.  
  11.   sqlQuery.Open;
  12.  
  13.   //db controls to field
  14.   cbStatus.DataSource:=dsDataSource;
  15.   cbStatus.DataField:='Status';
  16.   DBEdit2.DataSource:=dsDataSource;
  17.   DBEdit2.DataField:='Category';
  18.   DBEdit3.DataSource:=dsDataSource;
  19.   DBEdit3.DataField:='Script_name';
  20.   DBMemo1.DataSource:=dsDataSource;
  21.   DBMemo1.DataField:='Script';
  22.  
  23.   ...
  24. end;    
Best regards
paweld

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #8 on: April 24, 2019, 08:13:42 pm »
Code: Pascal  [Select]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   ...
  4.  
  5.   {actually connect}
  6.   sqlConn.Connected:= True;
  7.   sqlTransaction.Active:= True;
  8.  
  9.   sqlQuery.PacketRecords:=-1;    //fetch all records
  10.  
  11.   sqlQuery.Open;
  12.  
  13.   //db controls to field
  14.   cbStatus.DataSource:=dsDataSource;
  15.   cbStatus.DataField:='Status';
  16.   DBEdit2.DataSource:=dsDataSource;
  17.   DBEdit2.DataField:='Category';
  18.   DBEdit3.DataSource:=dsDataSource;
  19.   DBEdit3.DataField:='Script_name';
  20.   DBMemo1.DataSource:=dsDataSource;
  21.   DBMemo1.DataField:='Script';
  22.  
  23.   ...
  24. end;    

THANK YOU - THANK YOU!!!
You help solve two problems at once.

Now, I go onto more research.
Add, Saving Updating and Deleting.

Wish me luck!
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

mangakissa

  • Hero Member
  • *****
  • Posts: 900
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #9 on: April 25, 2019, 08:27:17 am »
It's not going to work. Why are you using DBComponents for searching / filtering records?
Quote
THANK YOU - THANK YOU!!!
You help solve two problems at once.
You didn't read the tutorials in a good way. This is standard procedure.
Quote
Okay... not getting much help.

So, lets kind of start over.

I am supplying the project that doesn't work here. (its 1.28 MB because of SQL DLL - no exe)
https://www.pixelink.media/downloads/SQL_DBGrid_CRUD.zip
Just building a simple project with one screen was enough. Even that wil give you the solution sometimes.


Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

mangakissa

  • Hero Member
  • *****
  • Posts: 900
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #10 on: April 25, 2019, 09:27:31 am »
Okay. Information is crucial to us. Just downloaded your project. I saw the DBedits en DBCombobox is not having the datasource connected. Filled in the property datasource in the components. Now I link the datafield to a DBEdit. Now comes your problem: the IDE comes with the message : Unable to retrieve fields definition from dataset. It's crucial to tell that the IDE gives the message.
This is a normal reaction. When you want to select a datafield, the IDE will open the database to read the query. But you're putting your query on code in the FormCreate event. So there's no query yet and no datafields to select. But you can always put a datafield in the property Datafield.
Now (I think) a bug is coming. It seems that the DBComponents will get data information before the query is initialised. That's not correct. So I put the query into the property SQL of component SQLQuery. 
Now the IDE still comes with the message, but running the project will give the right information.

The solution from paweld is correct, but it's a workaround. Normally you can fill in a datafield and the application should not crashing.

It so important to give al the information to help.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #11 on: April 25, 2019, 12:20:13 pm »
Okay. Information is crucial to us. Just downloaded your project. I saw the DBedits en DBCombobox is not having the datasource connected. Filled in the property datasource in the components. Now I link the datafield to a DBEdit. Now comes your problem: the IDE comes with the message : Unable to retrieve fields definition from dataset. It's crucial to tell that the IDE gives the message.
This is a normal reaction. When you want to select a datafield, the IDE will open the database to read the query. But you're putting your query on code in the FormCreate event. So there's no query yet and no datafields to select. But you can always put a datafield in the property Datafield.
Now (I think) a bug is coming. It seems that the DBComponents will get data information before the query is initialised. That's not correct. So I put the query into the property SQL of component SQLQuery. 
Now the IDE still comes with the message, but running the project will give the right information.

The solution from paweld is correct, but it's a workaround. Normally you can fill in a datafield and the application should not crashing.

It so important to give all the information to help.

1) I did give all the info. Even provided a ZIp project
I said earlier that I got the "unable to retrieve.." error and couldn't add any fields to the SQLQuery.

2) I never said I was filtering the data with the DB controls. I said I just want to display the data in each cell into a DB control.
Someone else assumed I was filtering. I already figured that out, but it is not in the ZIP project. That can only be done through another SQL statement altogether.

So, the code supplied worked exactly as i was needing... to just display each field in a selected record.

And, as you point out, the components don't seem to work right. That is why I am thinking that I will have to rely on coding to do my CRUD and filtering.

I am all set for now. I haven't gotten to the "Create, Editt, Update, and Delete" of records yet.
My only first objective was reached... just display data in DB Controls.

Thanks
« Last Edit: April 25, 2019, 02:30:30 pm by pixelink »
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #12 on: April 25, 2019, 02:45:17 pm »
Question, if I use the DB Navigator (which does work) and allow the user to edit the grid cells directly.
I need a way for the user to edit the "Script" text, but not in the cell, to cumbersome for them.

I need to create a popup window with the the "Script" in a RichMemo textbox that can be applied to the cell when they are done editing.

This "Script" column (see screen above) will have coded text in it.
And my end game is for the user to edit RTF text in Rich Memo, in a popup window. Then commit that RTF to the grid cell

I know how to make another form window.

But How do I put a little button next to the cell so the user clicks it and that popup window displays.

Does this make sense??


Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #13 on: April 25, 2019, 03:27:22 pm »
I guess what I am asking is...

You know how in a StringGrid control, you can have a button, checkbox and dropdown list a cell for each row?.

Can DBGrid do this?

1) I need a button in the Script column so I can display a window to edit RTF text.

2) I need a drop down for the 'Status" so a user can select "Draft" or "Published"... don't want to depend on the userto edit it directly in the cell for data integrity sake.

If I can do something like that, then I can use the DBNavigator and allow user to edit in the cell.

Hope that helps.
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640

pixelink

  • Hero Member
  • *****
  • Posts: 834
    • Pixelink Media
Re: Displaying Data from DBGrid into DBFields - Error Hooking up fields
« Reply #14 on: April 25, 2019, 06:06:59 pm »
Okay... after hours trying to get the sql components to work.

I was able to assign fields etc etc.

I also figured out how to add a "PickList" and "button"

Now, onward to editing RTF  in another window.

MY ONLY QUESTION FOR NOW IS. After displaying another window(Form), edit the RTF in RichMemo...
How do I get it back to the cell where I clicked the "Button" in the cell???

THANKS FOR EVERYONE'S HELP SO FAR!!!
Making Progress.

 :D
Can't Type - Forgetful - Had Stroke = Forgive this old man!


Pixelink Media •  Pixelink Forum
LAZ 2.0 • Win7 • 10G RAM • Nivida GForce GT 640