Topic: Use current record ID within a scripted Sql Query

Hi,

I think I already saw it in the forum but I cannot find it anymore. I need to write a SQL query in the main script that would use the current record ID but I can't figure how to write it.

it would look like this:

Sqlexecute('REPLACE INTO computers (id_Status) VALUES (2) where computers.id= 'current computer_ID_coming_from_form');

The purpose is to replace the id_status on form "Admin" if a status is changed on another form.

The record comes from "Form1.GridSearch" --> opens "Admin" form --> can open "Maintenance" form which can change status in "Admin" form via SQL query.

Re: Use current record ID within a scripted Sql Query

Hi,
I'm not at a PC at the moment to be 100% sure of the syntax but try something like this:

sqlexecute('replace into computers (id_status) values (2) where id = "'+form1.gridsearch.row[form1.gridsearch.selectedrow].id+'"');

Re: Use current record ID within a scripted Sql Query

Hi Tcoton, Hi Derek


Your request is wrong.
You need to use UPDATE.

Re: Use current record ID within a scripted Sql Query

Hi Sparrow, I do agree, I replaced with an Update statement.

Derek's answer works like this

sqlexecute('update computers set id_status = "2" where id = '+form1.gridsearch.row[form1.gridsearch.selectedrow].id');

Now I am scratching my head to see if I can use an "OR" in the query as I need to use 2 sources for the current ID to change a combobox display.

Form1 has 2 grids: GridSearch (search all records with filters)
                                                                                                     
                               ShowMaint (Filters all records with 1 status only)

                                              => Both can open the same Admin form and the computers.ID should be consistent whether accessed from one grid or another but... it does not work. I tried to display the computers.id in a field on Admin using the object properties is and it does not refresh when I open the form using one grid after the other. I tried a script but I am stuck

What I want to do is change the Admin.StatusList combo display according to current status as soon as I save a certain status in Maintenance form.

5 (edited by tcoton 2023-06-02 18:46:03)

Re: Use current record ID within a scripted Sql Query

Here is what I mean by the record ID that does not show. I kind of solved my issue with the combobox, but I do not understand why the id does is not displayed in the Admin form while it is on the Maintenance form.

Both grids on Form1 are using grid search to display the info and the other forms are just called using the "show record" property of a button.

http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&item=9532

Post's attachments

Attachment icon Record ID not showing up.png 466.17 kb, 45 downloads since 2023-06-02 

Re: Use current record ID within a scripted Sql Query

Hi,
Without seeing the actual project (or a version of it where you can see the problem) it's not easy to suggest what the problem (and solution) might be.
Derek.

Re: Use current record ID within a scripted Sql Query

I have made a quick and dirty version of my huge project with no script for no interference that illustrate my issues. I am open to suggestions but I want to keep the display of form1 to remain in the same way of working.

Post's attachments

Attachment icon Debug_ID.zip 338.15 kb, 111 downloads since 2023-06-02 

Re: Use current record ID within a scripted Sql Query

Hi,
Perhaps try it something like the attached (I've only done it for the 'gridsearch' and not for the 'showmaint'.
I've put the PC Name and record ID values into the form captions but they could just as easily be placed in discrete edit fields.
Derek.

Post's attachments

Attachment icon Debug_ID.zip 339.13 kb, 115 downloads since 2023-06-03 

Re: Use current record ID within a scripted Sql Query

Hi,

thanks for the example but it is not what I am looking for since I also need to get the Id from the showman grid. What I do not understand is that the ID is automatically retrieved and stored somewhere in memory when using the auto search grid to show a record but how is it possible that we cannot access it easily like any other field?

10

Re: Use current record ID within a scripted Sql Query

A trick you could try, is where you can get the ID save it to a 'form_name.tag' then it is available to you everywhere while that ID is needed.

On a clear disk you can seek forever

Re: Use current record ID within a scripted Sql Query

show id

Post's attachments

Attachment icon Debug_ID-1.zip 327.58 kb, 126 downloads since 2023-06-04 

Re: Use current record ID within a scripted Sql Query

Hi Sparrow, CDB,
Like yourselves, I'd originally tried to find a solution using a calculated field (and also considered using a 'tag') but the scenario that neither appears to fix is if you create a new 'computer' record (form 'admin') and then go immediately (without saving) to create a new 'maintenance' record (form 'maintenance').  In this scenario, I believe the PC Name and ID would be blank.
I can't see any way of avoiding a bit of script to resolve this particular situation.
And if you have to write a bit of script, you then need to consider if it is the 'gridsearch' tablegrid or the 'showmaint' tablegrid that has been clicked (although without knowing more details, I don't see any advantage from having two tablegrids rather than just one with a 'maintenance' filter which obviously then simplifies the script considerably - see attached as a suggestion).
Derek.

Post's attachments

Attachment icon Debug_ID3.zip 339.05 kb, 113 downloads since 2023-06-04 

Re: Use current record ID within a scripted Sql Query

Hello Derek.

No matter how hard I try, I can not understand why and for what it is all done.
Perhaps there are other solutions, easier and more logical.
But it's good that you understand the problem. )))

Re: Use current record ID within a scripted Sql Query

The purpose is very simple, I need to use scripts to update information not shown in the current form to be updated according to the id of the record being edited and not using a field that could contain a duplicate. Why do I use 2 table grids in Form1 is because the client needs to see which computers are in need of maintenance and access them without having to lookup for them to not forget about them. There are other "prime" information displayed as well in the full project.

Now I have tricked the app by altering the database while the app was open so it would throw an SQL error to see exactly what it does when using "Show record" and "Save record" functions within the grids and the SQL query triggered contains indeed the id of the computer related to what I am doing!!! The id looks like it is stored as a temporary variable within MVDB framework when saving the record. So, where is this id stored that we cannot access it with scripts or even display in a field?

I even tried to select the computers.id within the search grid, it is displayed in the table grid but we cannot display it in a field as it is not available in the dropdown of the field properties!!!!!

The "show record" function triggers this SQL query:

SELECT table1.columnX, table1.columnY, table1.columnZ from table1 where table1.id=A

The "save record" function triggers this SQL query:

UPDATE table1 SET columnZ='F', columnY='D', columnX='S' where id=A
Post's attachments

Attachment icon SAVE RECORD QUERY.png 218.17 kb, 45 downloads since 2023-06-05 

Re: Use current record ID within a scripted Sql Query

I want to add that it is super easy to get the id with a written SQL query but we all prefer to use internal features of MVDB as much as possible as it saves a lot of coding, this is why I want to understand how to get access to the hidden record ID stored by MVDB when editing a record in a form while using auto queries.

16 (edited by sparrow 2023-06-05 16:48:03)

Re: Use current record ID within a scripted Sql Query

If you carefully read the MVD manual, in particular the BUTTON tabs, you will be able to find out that:

  dbGeneralTableId - Integer type -
  "Makes sense if the button has the "Save record" action assigned to it. Contains the
record id, if the record is not saved, it returns -1."

i.e.  If you have opened a form to edit a record, this property will contain the ID of the record from the table for which you are editing. If the entry fails, the ID will change to "-1" For a new entry, the ID will be "-1" by default. In order for the ID to be saved in the form button after recording, there is a dbDontResetID property.

And many more interesting things in the MVD manual.

Re: Use current record ID within a scripted Sql Query

Hi Tcoton, Hi Sparrow,

I need to use scripts to update information not shown in the current form to be updated according to the id of the record being edited and not using a field that could contain a duplicate.

If your concern is using a field that could contain duplicates, then why not use a counter? 
I appreciate that you're not using primary keys so it won't be as fast (although probably not noticeable unless you are talking about very large amounts of data).
Derek.

Post's attachments

Attachment icon Debug_ID3.zip 338.63 kb, 143 downloads since 2023-06-05 

Re: Use current record ID within a scripted Sql Query

@Sparrow, I did read the help before posting but it was so unclear that I did not understand how to use it. I think YOUR explanation should replace what is written in the help smile To be fair, the MVD manual is pretty thin on how exactly we should/could use the components/methods and properties.

@Derek, the counter idea is a good idea, I will keep it for another purpose!!

I got it working with just this script:

procedure Admin_OnShow (Sender: TObject; Action: string);
begin
Admin.Adm_PC_ID.Text:= IntToStr(Admin.Adm_save.dbGeneralTableId);
end;

I still think this should not be so cumbersome to get this ID!!

Thanks folks once again!