(4 replies, posted in SQL queries)

I finally got it working.

After trying to UPDATE without success, I started second-guessing myself and thought it was INSERT.

Sparrow brought me back on track, and I noticed after some research that my MAIN mistake was a set of DOUBLE QUOTES that I was missing.

Here is the coding for anybody interested. Watch out for those single and double quotes

    SQLExecute('UPDATE dtClientName SET NACommunication1= " '+fmNeedsAssessment.NACommunicationText1.Text+' " WHERE id='+fmClientSearh.TableGrid1.sqlValue);       

Thanks all.


(4 replies, posted in SQL queries)

Thanks tcoton

I know it was something silly....

It unblocked that issue but I'm getting a new error.

Would have an idea what it is this time?

    SQLExecute('INSERT INTO dtClientName(NACommunication1) VALUES ("'+fmNeedsAssessment.NACommunicationText1.Text+'") WHERE id='+fmClientSearh.TableGrid1.sqlValue);


(4 replies, posted in SQL queries)

Hi everybody.

Can anybody see the error in my logic?

I get an error but the error seems to be what I want.

dtClientName = Table
NACommunication1 = Field in Table

The command is triggered by a Button - Onclick
    SQLExecute('INSERT INTO dtClientName(NACommunication1) VALUE ("'+fmNeedsAssessment.NACommunicationText1.Text+'")WHERE id='+fmClientSearh.TableGrid1.sqlValue);

any help would be greatly appreciated.


(2 replies, posted in SQL queries)


This is a similar question that I had but with a twist (I previously could use a GRID to get an SQL reference but now I am using a different Table and only have a combobox as a point of connection)

I want to force an answer into a combobox from a different table. It works with a Textbox, but not with Combobox

The reason behind my crazy, is that I can get the users to use the search button with the combobox (otherwise I wouldn't know how to get around this problem)..

Here are 2 lines of code similar in function but information placed in 2 different formats (TEXTBOX and COMBOBOX) I just get a blank in combobox..

*** From a selection of a condobox = cdClientResource,  you will look up the table dbManager of ManagerNameCombined and place it in Combobox

Push to a text box (ClientManagerText)

# 1

fmClient.ClientManagerText.text:=SQLExecute('Select ResManagerText FROM dbResource WHERE id= '+fmclient.cbClientResource.sqlvalue);

Push to a combo box (cbClientManager.)

# 2
fmclient.cbClientManager.text:=SQLExecute('Select ResManagerText FROM dbResource WHERE id= '+fmclient.cbClientResource.sqlvalue);

see ATTACHED PDF. I find it hard to explain my problem.


(3 replies, posted in SQL queries)

Hi Derek

And thanks as it works.

This example of coding directly within a Database tables, using a field, brings a new dimension in attempting to manipulate data.

I still don't know how whole script line works but I have a concrete example to dissect and think about.

With my little knowledge of programming, your example was scripted SQL  ?

Also, do you recommend any books for both Delphi and SQL that would coincide with MVD ?


(3 replies, posted in SQL queries)

Hi all

If there is anybody that help me solve this issue. 
I've gotten his far in my search with my minimal on going learning of SQL.

I want to filter everybody over a certain age.
Originally I was scripting everybody's AGE and saving in the table. I then realized that it wouldn't get updated automatically and would have to go into everybody's account and press save.

So I know the answer lies in SQL and came up with some kind of logic !
I know it doesn't work but Im missing the gaps of programming to get it going.

At Form startup (SQL QUERY)

        SELECT client, message, FROM dbclient
                date(Datebirth) – curdate() >= 58;

Result is sent to a table.


(6 replies, posted in Script)

On the same Theme

Is it possible to not refer to a grid but tell the combobox of the value you want.(Knowing that the value exists in the selection field).

Here is an example but I tried it but didnt work.

procedure fmGroupPresence_OnShow (Sender: TObject; Action: string);

          fmGroupPresence.ProgressType.dbitemid:='For closure';


(6 replies, posted in Script)

Ohhh my god..   So simple and elegant.  Once again I was over thinking this problem.
Programming is an ART.

Thank you Derek this will bring what I am doing to a higher level, with such simplify.


(6 replies, posted in Script)

Hi Derek

The idea is correct, but it wouldn't be the id but the name.
I was under the impression that giving the ID to the combo box would; give me text assigned to the TEXT

So when I open a form, based on a previous grid, the Name of the selected grid would appear in the combobox.



(6 replies, posted in Script)

Does this make any sense ?

      fform.combobox1.dbSQLExecute('SELECT id FROM dbClientName WHERE id='+fmClientsSearch.TableGrid1.sqlValue);   

I'd like to basically force an answer into a combo box, as I am limited in my programming skills, this would be a quick loop around  my problem.

(p.s. what is in the fmclientSearch.TableGrid would be the same list that would be available in the combo box)

Help and thanks to anyone.

Thanks Sparrow

I was over thinking this and I got into a mind jam.

     tmpID :=SQLExecute('SELECT id FROM dtClientName WHERE id=' +fmClientSearh.TableGrid1.sqlvalue);

I'll be working on the second part later on today.

much appreciated


Hi Everybody

I am trying to extract the ID number (For example : of a client) by using SQL, convert that Number into a string.

I then will use that Number(id) to "INSERT INTO database (Client, Update, price) values ('id','On Hold', 'Price pending');

So my question has 2 parts.

1) Extracting the id using sql using Delphi scripting:   
Example ( I can do this which extracts the info but not the ID) 
fmGroupPresence.GroupProgressClientName.Text:= SQLExecute('SELECT txClientName FROM dtClientName WHERE id='+fmClientSearh.TableGrid1.sqlValue);

2) How to insert a string into an sql using sql. I will be using the SQL BUTTON , which all is done in mysql..

Example (as a test ive forced info into the table by doing this, but would need the client number to be scripted.)

insert into dtFollowing(id_dtClientName,ProgressEdited,txtTitle,txNote)  values ('23','Yes','Test','Testnote');         

Sorry Im not more clear as I don't have the lingo used to explain what i need to do.

Thanks for your help.


Thanks, Derek, that was it.

So now I am going in the schema and looking at the linked tables and slowly scripting a cascading delete of each table before the last delete, which is the central database of clients.

I appreciate the help all give on the forum. Thanks.

Hi All, hope you guys can help or lead me to the right direction.

Now that I've built an application, I would like to share it with people at work. With one click at a hidden button, I could the necessary tables and clear the tables.  I've tried this code as a test in a testing environment.

procedure fmMainMenu_Button7_OnClick (Sender: TObject; var Cancel: boolean);
             SQLExecute('DELETE FROM dtTimeTableListing');

Yet I get the following message:

Error Message
FFOREIGN KEY constraint failed

Script: SQLExecute.

I am assuming the cause is links between other tables ? !

I've done as much research as my "PROGRAMMER WANT-TO-BE" brain can handle and would need simple coding to clear tables I think i need to before it becomes too spaghetti programming for my mind.

Also, am I on the right track to think, that one needs clear the links of secondary tables that feed to a main table ? (Does my sentence may any sense ?)

Thanks for your help everybody.


(7 replies, posted in SQL queries)

Hi Brian

Your approach of using the "SHOW RECORD" is working well and not causing any address violation.

Such that

Upon double clicking the grid, the desired form (TODO or ASSESS) will open.

However, I am having 3 forms opening up.  2 x the one desired and one the other form, one after the other.

From various tests, it appears double clicking the grid will activate the 2 "SHOW RECORD", buttons as as well as proceed with the SCRIPT which also clicks the button.

Thus this will result in 3 FORMS opening up one after the other.
I've double checked and compared all the properties from multiform2 with my own, yet nothing seems to come up.

procedure fmTimeTable_TableGrid1_OnDoubleClick (Sender: TObject);
    rslt : string;
     rslt :=SQLEXECUTE('SELECT DISTINCT ttCategory FROM dttimeTable WHERE id = ' +fmTimeTable.TableGrid1.sqlValue) ;

     if rslt = 'Todo' then
     else if rslt = 'Assess' then



(7 replies, posted in SQL queries)

Hi Brian and thanks for you help.

I did exactly as you instructed and your program is exactly what I want to do however, I am getting a really strange message

Access violation at address 00E42167 in modele 'NAME OF PROGRAM',
Read of address FFFFFFFE.

Thinking it was a computer memory problem (did do I know), rebooted Windows and tried again with no luck.


Hi everybody
I don't know if this is an SQL or a SCRIPT.

I would like the users to double click a ROW on a GRID, and based on a variable (either 'Assess' or 'Todo') different Forms will open.

I got as far as I can open the appropriate FORM but I would also like the Data from the ROW of the GRID to also transfer.


procedure fmTimeTable_TableGrid1_OnCellDoubleClick (Sender: TObject; ACol, ARow: Integer);   
        aba : string;
         aba :=SQLEXECUTE('SELECT DISTINCT ttCategory FROM dttimeTable WHERE id = ' +fmTimeTable.TableGrid1.sqlValue) ;

         showmessage(ARow); //Test to see what acol and arow does...I know the answer lies with something to do with this.

         if aba = 'Todo' then
                  // carry over information from the selected ROW of the GRID to the form Requested fmTodo2
          else if aba = 'Assess' then
                  // carry over information from the selected ROW of the GRID to the form Requested in this case fmTimeTableEntry.


====== I think it has something to do with an SQL but also the ARow ?==========

Thanks for any help or guidence.


(4 replies, posted in SQL queries)

Hi Derek

Million thanks.

It worked !!

It took a while for me to figure out how it worked, but as always, the logic of it is simple once you break it down and understand what is going on.



(4 replies, posted in SQL queries)

Hi agusecc, and thanks but I need to have it done in SQL as I have other things that will follow.

I updated the visual helping to make myself clearer.

I can sort the EXPENSE in one grid, and sort the INCOME in a second grid.

Things get strange when I get into SQL and try to place each calculation separately for that specific client. I get all EXPENSE and all INCOMES for all clients.


(4 replies, posted in SQL queries)

Hi, as I am now up to the point of learning how  SQL works, my question is probably very basic but part of the learning curve.

I seem unable to find how you search for an individual from a parent grid.

I am trying to sort info form a table using a "Client name" that has been selected in an earlier grid/form.

See my pic for a visual example and here is my script.

form2.totalExpense.Value := SQLExecute('SELECT TOTAL(amount) FROM expense WHERE 'Status' = "expense" AND   ???? Name of client on form matches id of table expense ?????   ');


(3 replies, posted in SQL queries)


I am trying to delete all information within table before, replenishing it with new data.

I've tried using TRUNCATE at the beginning of the coding to empty my Table before replenishing, using an action button SQL QUERY (dtReportAssess = Table name)

          truncate Table dtReportAssess ;             


And have been getting an error.

          Error message;
          near "truncate" : synax error.

Any suggestions and any help would be great.


(6 replies, posted in Script)

Thanks guys, Actually both are going to be helpful.

I now have to study both solutions and see how to apply them.

Thanks for your help, and if this works the way I would like, will make a lot of people in a Day program that are in wheel chairs very happy.



(6 replies, posted in Script)

Thanks CDB however I never know if it will be a file or a URL as the data entered in a database is somewhat random.

I am trying to create a menu board, for people who have reduced mobility.

By creating very large buttons they will be able to open web pages, software or files. The menu board needs to be flexible so that  any button can be modifiable and updated as needed (Thus the database).

I do know that Shellexecute has some kind of ability to do that.


(6 replies, posted in Script)

Hi Everybody

I'm looking for a command that will open either a file or url depending on what is in the TABLE
Best I could come up with was the following (and it ain't working);

var S : String ;
uses ShellApi;

       s :=SQLExecute('SELECT Menu1URL2 FROM MainDatabase WHERE id=1');
        ShellExecute(Handle,'open',s,nil,nil, SW_SHOWNORMAL);

Far from being a programmer  anything too complicated and it  starts looking like spaghetti.
Thanks for your help



(2 replies, posted in General)

Thanks Derek

That's what I was looking.