Topic: sqlexecute

i need working sample sqlexecute update and sqlexecute insert statements using plain text, a text box and a combobox. every time i try to use one of these statements i always get a missing ) or a missing ; message and i dont seem to be able to find the problem. thanks

lee

Re: sqlexecute

Hello lhimes,


Examples bellow are based on :

- a table called clients and a table called gender
- two fields : name, surname in table clients
- one field : id_gender in table gender


If you want to save values from edit fields and comboboxes, you have to assign their content to a variable which must first be declared

procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    gender_id,client_name, client_surname : String;
begin
    gender_id := IntToStr(Form2.ComboBox1.dbItemID);
    client_name := Form2.Edit1.Text;
    client_surname := Form2.Edit2.Text;
    SQLExecute('INSERT INTO clients(id_gender,name,surname) VALUES("'+gender_id+'","'+client_name+'","'+client_surname+'")');
end;

The syntax for performing and update needs some specific elements :
what fields do you want to update ?
in what table ?
under which condition ?

procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    gender_id,client_name, client_surname : String;
begin
    gender_id := IntToStr(Form2.ComboBox1.dbItemID);
    client_name := Form2.Edit1.Text;
    client_surname := Form2.Edit2.Text;
    SQLExecute('UPDATE clients SET id_gender="'+gender_id+'",name = "'+client_name+'" WHERE surname="'+client_surname+'" ');
end;

In this (silly) example, you are going to change the value of name and gender where the value of surname matches something you selected.


Do you know that MVD can do all that for you without a single line of code ?


Hope this helps



Mathias

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

Zaza Gabor

Re: sqlexecute

hello Mathias

that was a big help. nowhere did i see or read anything that indicated i needed to use a variable in the statement. as for the update. an example  would be a person with a numeric handicap in one table and is continually recalculated in a form. i have thought of calculated fields but then i run into problems when i do reports and think the update is the easiest solution

Re: sqlexecute

Hello lhimes,


You are right, teh use of variables is not mandatory and you can write :

SQLExecute('UPDATE clients SET id_gender="'+IntToStr(Form2.ComboBox1.dbItemID)+'",name = "'+Form2.Edit1.Text+'" WHERE surname="'+Form2.Edit2.Text+'" ');

instead of

SQLExecute('UPDATE clients SET id_gender="'+gender_id+'",name = "'+client_name+'" WHERE surname="'+client_surname+'" ');  

I personnaly use variables because I find it easier to track and it's a good habbit.


Anyway, the trick is to include what you want to save bewteen

"'+

and

+'"

The entire SQL Query is surrounded by

('                     and                            ');

have a good day



Mathias

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

Zaza Gabor

Re: sqlexecute

i have a table venue. i use this command>;
sqlexecute('insert into venue values ("'+8 Ball+'")');
i get an error ) expected at 5:47
where am i going wrong?

lee

Re: sqlexecute

Hello,

I think you just forgot to had the field after the table declaration :

SQLExecute('INSERT INTO table_name(field1,field2) VALUES("'+value1+'","'+value2+'")');

Math

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

Zaza Gabor

Re: sqlexecute

By the way, if you are not using variables but hard coding the values to be inserted, you don't need the + sign, this is only for concatenation when using variables.

With a variable :

"'+my_variable+'"

With text directly in the query

"my_text"

With an integer directly in the query

'123' 
Or sometimes just
123
I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: sqlexecute

mathmathou wrote:

Hello lhimes,


Examples bellow are based on :

- a table called clients and a table called gender
- two fields : name, surname in table clients
- one field : id_gender in table gender


If you want to save values from edit fields and comboboxes, you have to assign their content to a variable which must first be declared

procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    gender_id,client_name, client_surname : String;
begin
    gender_id := IntToStr(Form2.ComboBox1.dbItemID);
    client_name := Form2.Edit1.Text;
    client_surname := Form2.Edit2.Text;
    SQLExecute('INSERT INTO clients(id_gender,name,surname) VALUES("'+gender_id+'","'+client_name+'","'+client_surname+'")');
end;

The syntax for performing and update needs some specific elements :
what fields do you want to update ?
in what table ?
under which condition ?

procedure Form2_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    gender_id,client_name, client_surname : String;
begin
    gender_id := IntToStr(Form2.ComboBox1.dbItemID);
    client_name := Form2.Edit1.Text;
    client_surname := Form2.Edit2.Text;
    SQLExecute('UPDATE clients SET id_gender="'+gender_id+'",name = "'+client_name+'" WHERE surname="'+client_surname+'" ');
end;

In this (silly) example, you are going to change the value of name and gender where the value of surname matches something you selected.


Do you know that MVD can do all that for you without a single line of code ?


Hope this helps



Mathias

Hi Mathias,

Could you send me this example in the form of source code?  I need to see if I can add this to a script
I'm writing.   Thanks a  lot!!

Re: sqlexecute

hey Mathmathou you are allways a life saver, i'm trying to use the "like" function in sql to replace some tables and search buttons hidden in my program, that way i can use this simple line instead of hitting an invisible button to search for an item in a table.
but i'm doing something wrong

The sentence is this:

SqlExecute('SELECT id FROM cliente WHERE contacto1 LIKE '% + frm_assistencia.cl_contacto1.text%'')

i can't seem to make this one work.
i tried using ' and " and everything else...
Don't know where i am failing

Re: sqlexecute

nevermind...
SqlExecute('SELECT id FROM cliente WHERE contacto1 LIKE "%stoopidme%"')