UpdateSQL and DeleteSQL

[oraitecamonyes]

Hi ppl,

ive spend the last 2 days trying to make these work with no success 

can anyone give me a hand on this?

Here is my code for both cases

    sqlquery1.UpdateSQL.clear;
    SQLQuery1.UpdateSQL.text :=  'UPDATE clientes SET clientes.cli_nome = "'+edit7.text+'" where clientes.cli_id = "2588" ';
    if(MySQL50Connection1.connected=false)then MySQL50Connection1.connected:=true;
    if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;
    SQLQuery1.Open;
    SQLQuery1.Edit; //Put Query in Edit(Update) Mode
    SQLQuery1.Post;
    SQLQuery1.ApplyUpdates; //Post and Apply Updates
    SQLQuery1.Close;
    SQLTransaction1.CommitRetaining; 

    sqlquery1.DeleteSQL.clear;
    SQLQuery1.DeleteSQL.add('DELETE from clientes where clientes.cli_id = "'+edit6.text+'" ;');
    if(MySQL50Connection1.connected=false)then MySQL50Connection1.connected:=true;
    if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;
    SQLQuery1.Open;
    SQLQuery1.Delete;
    SQLQuery1.Post;
    SQLQuery1.ApplyUpdates; //Post and Apply Updates
    SQLQuery1.Close;
    SQLTransaction1.CommitRetaining;   

the updatesql works if i dont use and edit.text... but when i use one it will display an error "No insert query specified and failed to generate one"

on the deletesql i simply get the error "The dataset is empty"

anyone?

[Marc]

Use SqlQuery.Execute for queries returning no data (like update or delete)

[oraitecamonyes]

ok, thx for the suggestion!
finally did it, here is the code that works for me:

DELETE ROW

Code: [Select]

sqlquery1.SQL.clear;
    SQLQuery1.SQL.add ('DELETE FROM clientes WHERE cli_id = :cli_id ');
    sqlquery1.Params.ParamByName('cli_id').Asstring:= edit6.text;

    if(MySQL50Connection1.connected=false)then MySQL50Connection1.connected:=true;
    if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;

    SQLQuery1.ExecSQL;
    SQLQuery1.Close;
    SQLTransaction1.CommitRetaining;   

UPDATE ROW

Code: [Select]

    sqlquery1.SQL.clear;
    SQLQuery1.SQL.add ('UPDATE clientes ');
    SQLQuery1.SQL.add ('SET clientes.cli_nome= :cli_nome, clientes.cli_nasc= :cli_nasc, clientes.cli_morada= :cli_morada, clientes.cli_telf= :cli_telf, clientes.cli_telem= :cli_telem, ');
    SQLQuery1.SQL.add ('clientes.cli_bi= :cli_bi, clientes.cli_regime= :cli_regime, clientes.cli_benefic= :cli_benefic, clientes.cli_prof= :cli_prof, clientes.cli_email= :cli_email, clientes.cli_notas= :cli_notas ');
    SQLQuery1.SQL.add ('WHERE clientes.cli_id= :cli_id ;');

    sqlquery1.Params.ParamByName('cli_nome').Asstring:=UTF8DECODE(edit7.text);
    sqlquery1.Params.ParamByName('cli_nasc').Asstring:=edit8.text;
    sqlquery1.Params.ParamByName('cli_morada').Asstring:=UTF8DECODE(memo2.text);
    sqlquery1.Params.ParamByName('cli_telf').Asstring:=edit9.text;
    sqlquery1.Params.ParamByName('cli_telem').Asstring:=edit10.text;
    sqlquery1.Params.ParamByName('cli_bi').Asstring:=edit11.text;
    sqlquery1.Params.ParamByName('cli_regime').Asstring:=edit12.text;
    sqlquery1.Params.ParamByName('cli_benefic').Asstring:=edit13.text;
    sqlquery1.Params.ParamByName('cli_prof').Asstring:=UTF8DECODE(edit14.text);
    sqlquery1.Params.ParamByName('cli_email').Asstring:=edit15.text;
    sqlquery1.Params.ParamByName('cli_notas').Asstring:=UTF8DECODE(memo3.text);
    sqlquery1.Params.ParamByName('cli_id').Asstring:=edit6.text;

    if(MySQL50Connection1.connected=false)then MySQL50Connection1.connected:=true;
    if(SQLTransaction1.active=false)then SQLTransaction1.active:=true;

    SQLQuery1.ExecSQL;
    SQLQuery1.Close;
    SQLTransaction1.CommitRetaining;   


[Marc]

Don't know if you need a sql.close

[Paulinho Cé]

Perfect !!! work 100 %.

thanks.