Add record using script

Hello v_pozidis,

Adding a record to a database table with a button click is not difficult.

First, you need to activate scripts in your project by clicking the 'script' button, next to the 'run project' button in the toolbar.

Then, on your form, you need to create a button and then, in the 'events' tab, found on the left in the object inspector, you need to double click in the OnClick event. That will create the code structure for you.

Let say you have a table like : id, name, surname, address called client

All these data come from edit fields (or memo, or datepicker....), so you have to link them manually if you don't want to use embedded MVD functions. This means you will have to declare variables first, like this :

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
     name, surname, address : String;
begin

end;

You need as many variables declared as there are fields to save.

var
     age : Integer;
     salary : real;

Now that you have declared your variables, you need to assign them, that is to say link and edit field to a variable :

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
     name, surname, address: String;
begin
     name := Form1.Edit1.Text;
     surname := Form1.Edit2.Text;
     address := Form1.Edit3.Text;
end;

Finally, you need to trigger the SQL action to save you data for the OnClic event :

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
     name, surname, address: String;
begin
     name := Form1.Edit1.Text;
     surname := Form1.Edit2.Text;
     address := Form1.Edit3.Text;

     SQLExecute('INSERT INTO client(name,surname,address) VALUES("'+name+'","'+surname+'","'+address+'") ');
end;

That's basically it, but in the code above, there is no safety whatsoever.

For example, we did not check that the fields where not empty, or that the data was not already in database.

If you want to check for empty fields, you could do something like :

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
     name, surname, address: String;
begin
    name := Form1.Edit1.Text;
    surname := Form1.Edit2.Text;
    address := Form1.Edit3.Text;
        if (name = '') OR (surname = '') OR (address = '') then
            begin
                Cancel := True;
                ShowMessage('You left at least one field empty. All fields must my completed before saving is allowed');
            end
        else SQLExecute('INSERT INTO client(name,surname,address) VALUES("'+name+'","'+surname+'","'+address+'") ');
end;

Checking for duplicates before inserting is also possible, but that's a completely different code

Hope this helped

Cheers

Mathias

PS : One final note. Inserting data in a table can be dangerous when you rely on the user to enter it. The MVD system is solid, but you can never know what the user will type : a SQL instruction could be typed in wich, if correctly escaped from the variable, could erase data or even drop your database.

if you want to avoid that, you can use this

name := Form1.Edit1.sqlValue;
instead of 
name := Form1.Edit1.Text;

This is much safer and will also let you save text with " or ' or + and so on.

But in order to work properly, you will need to change your query like this

SQLExecute('INSERT INTO client(name,surname,address) VALUES('+name+','+surname+','+address+')');

Simple quotes instead of double quotes plus simple quotes

Cheers