1 Topic by tcoton (edited by tcoton 2015-03-03 14:32:27)

  • Registered: 2014-06-25
  • Posts: 863

Topic: Record data in two different tables

I have a special forms with 2 different buttons to save different information in 2 separate tables.
When one is working fine, the other does not, why?

In the attached screenshot, I cannot save the information in Red whereas there is no pb with information in blue.

The grid should display all data in "Maintenance" table ( sql query= select * from maintenance)
I even checked with SQliteStudio and no data is present in maintenance. There is also no error message at all.
I use 1.49




Screenshot attached

Post's attachments

Attachment icon image3.png 48.65 kb, 397 downloads since 2015-03-03 

  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

I managed to trick it using an SQL statement to populate my other table as using default functions does not the job


it looks like this:

procedure Admin_maint_add_OnClick (Sender: string; var Cancel: boolean);// Save maintenance info into a separate table

begin

    SQLExecute ('INSERT INTO maintenance (PCName,id_pc_typ,maint_details,maint_date) VALUES ('+ Admin.EditPCName.sqlValue+','+Admin.TypList.sqlValue+','+Admin.MaintEdit.sqlValue+', '+Admin.MaintDate.sqlDateTime+')');

    Admin.Close;
end;
  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

Now I just need to find a way to query the maintenances just for the PC Name displayed... this is tricky. Any suggestion?

  • Registered: 2015-02-24
  • Posts: 446

Re: Record data in two different tables

I'm not sure I fully understand what you want to achieve.


Are you saying that you need to query a table from a reference on another table ?


If that is so, you need to use an INNER JOIN .. ON.. statment in your SQL query.
Here is a good example : INNER JOIN




It's a bit tricky you are right but fun ! If the example does not help you, give more details and I'll try to help you

Have a good day

Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor

5 Reply by tcoton (edited by tcoton 2015-03-04 08:13:59)

  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

Nop, join would have been too easy, I know how to join multiple tables on multiple levels tongue


If you look at the screenshot on first post, you may understand.

I save data from the red selection in another table than the blue selection. (edit: replace word database by table smile )

By default when I click on Save, I save the blue selection into "computers", but I  save the red selection into "maintenance" when I click on the button "Add Maint."  to get a track of all maintenances occured on this machine smile

What I want to do now, is to display in the lower grid the maintenances which concern only the current record.

For example if I display PcName=L2343, I want to see only the maintenances for this PCName not all maintenances for all PC, if I open another record, the query should show me the accurate data according to the current PCName, this is tricky!!

The trick is to get the PCName as a variable in the sql query... I am a bit tired right now. I will see tomorrow.

6 Reply by rjkantor (edited by rjkantor 2015-03-03 22:09:48)

  • Registered: 2014-12-02
  • Posts: 136

Re: Record data in two different tables

Here is how I show related records

http://backdoortechnology.com/public/RelatedRecords.jpg

procedure frmTransaction_Button4_OnClick (Sender: string; var Cancel: boolean);
var
    id_Orders: integer;
begin
    id_Orders := SQLExecute ('SELECT id_Orders FROM Transactions WHERE id=' + IntToStr(frmMain.TableGrid2.dbItemID));
    //showmessage(inttostr(id_Orders));
    frmTransaction.TableGrid1.dbSQL:= 'select Transactions.[year], coin.[Description], Transactions.[Mint], Transactions.condition,Transactions.[Quantity]  from Transactions left outer join [coin] on transactions.[id_coin]=coin.[id] where Transactions.id <> ' + IntToStr(frmMain.TableGrid2.dbItemID) + ' and Transactions.[id_Orders] = ' + IntToStr(id_Orders) ;
    
    frmTransaction.TableGrid1.dbSQLExecute;
end;

frmMain.TableGrid2.dbItemID is the selected record from the Tablegrid which is displayed prior to the user selecting the record to edit or view in this form which is presented.  From here they can edit the record details or see related records to this order (in your case, the maintenance).

  • Registered: 2015-02-24
  • Posts: 446

Re: Record data in two different tables

Just to make sure : your application saves data to 2 different DATABASES, not to 2 TABLES in the same DATABASE, right ?

If that is so, I'm very much curious to know how you set up the system in the first place, because I tried and failed :
- 2 different sqlite.db files
- 2 differents tables.ini files
- 2 different servers declared in the settings.ini file


In sql you can use the ATTACH command to link 2 different databases together, but I don't know how with MVD.

Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: Record data in two different tables

tcoton
Your code (procedure Admin_maint_add_OnClick) should be working.
Please, send me your project to support@drive-software.com with link at this topic, or attach your project here.

Dmitry.

9 Reply by tcoton (edited by tcoton 2015-03-04 08:19:30)

  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

@mathmathou, my fault, I wrote database, I was thinking table ( tireness....) MVDB currently does not support the use of multiple databases.

@rjkantor, I think you got it, I will try your trick, thanks a lot for the help.

@Dmitry, yes it is working properly, I am just trying to find a way to retrieve the value of "PCName" displayed to query all related maintenance records and show these records in the lower grid:)

  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

Here is exactly what I want to do but MVDB does a mistake in the query, check the attached screenshot. In red rectangle, you can see what I get as a result sad

I tried with a script but I could not get it even returning the column name, it does nothing:

procedure Form1_Button5_OnClick (Sender: string; var Cancel: boolean);
var
    sPCName: string;
   

    begin
        sPCName := Admin.EditPCName.sqlValue; // get current PCName
        Admin.TableGrid1.dbSQL:= SQLExecute('SELECT PCName as "PC Name", id_Pc_Typ as "Model", Maint_details as "Details Maint.", Maint_Date as "Date Maint." FROM Maintenance WHERE PCName='+sPCName); // get information related to current PCName
       
    end;
Post's attachments

Attachment icon image5.png 74.32 kb, 352 downloads since 2015-03-04 

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Record data in two different tables

tcoton
Your script is incorrect, please try this, but this example works only in the latest beta version 1.50
https://www.dropbox.com/s/0m799p8qxh5kb … 0.zip?dl=0


procedure Form1_Button5_OnClick (Sender: string; var Cancel: boolean);
var
    sPCName: string;
begin
    sPCName := Admin.EditPCName.sqlValue; // get current PCName

    Admin.TableGrid1.dbSQL:='SELECT PCName, id_Pc_Typ, Maint_details, Maint_Date FROM Maintenance WHERE PCName="'+sPCName+'"';
    Admin.TableGrid1.dbListFieldsNames :='PC Name,Model,Details Maint,Date Maint'; 
    Admin.TableGrid1.dbSQLExecute;
end;

also you can download the example
http://myvisualdatabase.com/forum/misc. … download=1
(fill TableGrid result of the SQL query using script)

Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

Hi Dmitry,

this is approaching the result I am looking for but I got no record returned. Only the headers...

See screenshot using 1.50 beta + your script.

Post's attachments

Attachment icon image6.png 76.18 kb, 289 downloads since 2015-03-04 

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Record data in two different tables

tcoton
Please, send me your project to support@drive-software.com
or attach your ptoject here (without exe and dll)

Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

I sent you the project and thanks again for your precious help! smile

15 Reply by tcoton (edited by tcoton 2015-03-05 12:38:49)

  • Registered: 2014-06-25
  • Posts: 863

Re: Record data in two different tables

Dmitry found the error in the query, thanks again!!

procedure Form1_Button5_OnClick (Sender: string; var Cancel: boolean);
var
    sPCName: string;
begin
    sPCName := Admin.EditPCName.sqlValue; // get current PCName

    Admin.TableGrid1.dbSQL:='SELECT PCName, id_Pc_Typ, Maint_details, Maint_Date FROM Maintenance WHERE PCName='+sPCName+;
    Admin.TableGrid1.dbListFieldsNames :='PC Name,Model,Details Maint,Date Maint'; 
    Admin.TableGrid1.dbSQLExecute;
end;

Check the end of the query (where blablabla= '+blablabla+;) , it works perfectly with 1.50 beta at the moment.

I am so happy smile