Topic: Query to populate several textboxes from tablegrid

Hi all,

I need your assistance.
I would like to create a query to populate several textboxes based on the selected record from a table grid.

I tried for each textbox to write an SQLExecute query, which is working just fine, but I think there should be am easier way.

The structure of my table is:
tblRetete:
-id = integer
-id_tblTipReteta=integer FK
-Activ = boolean
-id_tblArticol = integer FK
-id_tblArticol1=integer FK
-Cantitate

tblArticol contains all the materials (Finished Goods, Row Material)

To select the records, I've created this Select statement, which populate a tablegrid and is working fine:

procedure cautaReteta (Sender: TObject; var Cancel: boolean); // cauta reteta
var
ca, na, cc, nc, nr , tr : String;
sr, cf: integer;

begin
ca:= TRIM(frmMain.txtCodArticolReteta.Text);
na:= trim(frmMain.txtNumeArticolReteta.Text);
cc:=TRIM(frmMain.txtCodComponentaReteta.Text);
nc:=TRIM(frmMain.txtNumeComponentaReteta.Text);
nr:=frmMain.txtNumeReteta.Text;
tr:=SQLExecute('SELECT TipReteta from tblTipReteta');
//sr:=frmMain.chkStatut.State := cbChecked;

   frmMain.grdRetete.dbSQL:=('SELECT NumeReteta,NrTipReteta,CantRef,CASE Activ when 1 THEN "DA" when 0 THEN "NU" End ,a.CodArticol, a.NumeArticol, ' +
                                   ' CASE Comisionare when 1 then "DA" when 0 then "NU" End,c.CodArticol, c.NumeArticol, ' +
                                   ' Cantitate, CASE Fix when 1 then "DA" when 0 then "NU" End from tblRetete r' +
                                   ' INNER JOIN tblArticol a on r.id_tblArticol=a.id ' +
                                   ' INNER JOIN tblArticol c on r.id_tblArticol1=c.id ' +
                                   ' INNER JOIN tblTipReteta tr on r.id_tblTipReteta=tr.id ' +
                                   ' WHERE a.CodArticol like "%' + ca + '%"' +
                                   ' AND a.NumeArticol like "%' + na + '%"' +
                                   ' AND c.codArticol like "%' + cc + '%"' +
                                   ' AND c.NumeArticol like "%' + nc + '%"' +
                                   ' AND NumeReteta like "%' + nr + '%"');
   frmMain.grdRetete.dbListFieldsNames:='Nume Reteta,Tip Reteta,Cant. Ref.,Activ?,Cod Articol, Nume Articol,Comisionare?,Cod Componenta, Nume Componenta, Cantitate, Fix?';
   frmMain.grdRetete.dbSQLExecute;
end;

But now, I want to add the Query to edit / modify a selected record from the tablegrid.

Thank you.

Best regards,
Alin

Best regards,
Alin

2 (edited by tcoton 2023-04-09 19:52:18)

Re: Query to populate several textboxes from tablegrid

You should include an id in your select in order to be able edit/modify a selected record otherwise, MVDB does not know which record it must modify.

Re: Query to populate several textboxes from tablegrid

Hello tcoton,

You're right, but this SELECT statement is for the search form and it populates a tablegrid.
NOw, from this table grid, I would like to create a Select statement and send the information to the edit form based on ID.
When the ID is passed to the edit form, there I have to create a bunch o Select statement for each textbox to hold the values.

I was thinking that maybe is a possibility to create a single SELECT statement which holds all the information and then to assign to the textboxes. I have read something about Recordset, but I am not sure how to do it.

Thank you.

BR,
Alin

Best regards,
Alin

Re: Query to populate several textboxes from tablegrid

Hi,


You were very rightly told what you need for further action.

Such a procedure as you imagine in my opinion is redundant.
"Query from the script -> table -> generate a query in the script -> edit form with text fields."
You can continue: writing queries to save the values in your tables.


The IDs of the table records and the names of the tables where they came from are unknown for editing.
In addition, you will convert some data using CASE.


Why not send the necessary data to the form for editing with the same request?
Everything can be easier than you imagine.
Think and execute from the simple.


Check out the example (function SQLQueryhttp://myvisualdatabase.com/forum/viewt … 944#p31944
Allows you to create one query that returns multiple values.

Re: Query to populate several textboxes from tablegrid

Hi sparrow,

You're a life saver!
This is what I wanted to achieve, but haven't known how to do it.
Thank you so much for pointing that out!

BR,
Alin

Best regards,
Alin

Re: Query to populate several textboxes from tablegrid

Hello all,

I encounter a problem now with the SQLQuery. I have textboxes, comboboxes and checkboxes to asign values to.
For textboxes, it's clear, I have string values, for comboboxes i used integer but I can't figure it out how to asign the values to the checkboxes. I tried also with Integer , string or boolena but is not working.
Can somebody help me to asign the value to the checlbox as well?

Thank you.

BR,
Alin

Best regards,
Alin

7 (edited by sparrow 2023-05-18 08:57:49)

Re: Query to populate several textboxes from tablegrid

Something like this

Post's attachments

Attachment icon examp.zip 397.17 kb, 167 downloads since 2023-05-18 

Re: Query to populate several textboxes from tablegrid

sparrow wrote:

Something like this

Hi sparrow,

You're a life saver!
Thank you so much.

Best regards,
Alin

Best regards,
Alin

Re: Query to populate several textboxes from tablegrid

Hello once again,

I have moved my database from SQLite to MYSQL and now the TDataSet is not populating any fields (textboxes / comboboxes). I am not getting any errors, only that the fields are blank.
How can I solve this to work with Mysql as well? Is there another approach?

Thank you.
Alin

Best regards,
Alin

Re: Query to populate several textboxes from tablegrid

If you switched from SQLite to MYSQL by simply clicking on the MYSQL icon in the program and entering the server parameters,
you have generated and received a clean database in MYSQL.
Unfortunately, you didn't migrate anything, but your data is still in the SQLite database if you haven't wiped anything.

Re: Query to populate several textboxes from tablegrid

Hello Sparrow,

I've already made that. All the information is already in Mysql.
It seems that I had a problem with my SQL Query. I've solved it partially.

The thing is now, that the date conversion is not OK.
I have this conversion

frmAsociereOrdine.dtpDataStart.DateTime := SQLDateTimeToDateTime(detOrdin.FieldByName('DataStart').asString);

but the system is throwing an error message : EConvertError "07/08/2023" is not a valid date and time.

How can I solve that?

Alin

Best regards,
Alin

Re: Query to populate several textboxes from tablegrid

What are you trying to achieve with your conversion?
I do not understand the "SQLDateTimeToDateTime" part, usually you convert a date time to a date or to a string not to date time.
According to the error, your column format might not be a "date time" but a "date" field only, hence the impossible conversion.

13 (edited by sparrow 2023-08-07 17:35:43)

Re: Query to populate several textboxes from tablegrid

As far as I understand, you get the date from the request and you want to set this date in the DateTimePicker .
The error indicates that you have the wrong date format in the database.
The error indicates that your format is "07/08/2023" and should be "2023-08-07".
You can try to change the format in the database to the correct one, or rebuild the date string
from "07/08/2023" to "2023-08-07" using COPY ().



str := detOrdin.FieldByName('DataStart').asString;
newstr := copy(str,7,4)+'-'+copy(str,4,2)+'-'+copy(str,1,2);
frmAsociereOrdine.dtpDataStart.DateTime := SQLDateTimeToDateTime(newstr);

Of course STR and NEWSTR must be defined as STRING .
More information is needed for a better answer.

Re: Query to populate several textboxes from tablegrid

p.s.  Another option is to embed the STR_TO_DATE() function in your MYSQL query. Find help on this function yourself.