Code for UPDATE and DELETE in delphi uniquery

Question

I'm trying to update and delete my record. I'm using dbgrid as to show the database and i use uniquery to do the query. I managed to do the insert query but not with the update and delete.

Here is my code :

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.Grids, Vcl.DBGrids, Data.DB,
  DBAccess, Uni, UniProvider, MySQLUniProvider, MemDS, Vcl.StdCtrls, DAScript,
  UniScript;

type
  TForm1 = class(TForm)
    UniConnection1: TUniConnection;
    MySQLUniProvider1: TMySQLUniProvider;
    UniDataSource1: TUniDataSource;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Label1: TLabel;
    Edit1: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    Edit2: TEdit;
    Edit3: TEdit;
    Label4: TLabel;
    DBGrid1: TDBGrid;
    UniQuery1: TUniQuery;
    UniScript1: TUniScript;
    procedure Button1Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure Button5Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  Application.Terminate();
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
      UniQuery1.Edit;
      UniQuery1.SQL.Add('UPDATE barang SET id:=i, name:=nam, stock:=st where id=:i');
      UniQuery1.ParamByName('i').AsString := Edit1.Text;
      UniQuery1.ParamByName('nam').AsString := Edit2.Text;
      UniQuery1.ParamByName('st').AsString := Edit3.Text;
      UniQuery1.ExecSQL;

end;

procedure TForm1.Button5Click(Sender: TObject);
begin
  UniQuery1.Insert;
  UniQuery1.FieldByName('ID').AsString := Edit1.Text;
  UniQuery1.FieldByName('Name').AsString := Edit2.Text;
  UniQuery1.FieldByName('Stock').AsString := Edit3.Text;
  UniQuery1.Post;
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  UniQuery1.Edit;
  UniQuery1.SQLdelete('DELETE FROM barang where id=:i');
  UniQuery1.ParamByName('i').AsString:=edit1.Text;
  UniQuery1.ExecSQL;
end;

procedure TForm1.DBGrid1CellClick(Column: TColumn);
begin
  edit1.Text := DBGrid1.Fields[0].asstring;
  edit2.text := DBGrid1.Fields[1].asstring;
  edit3.Text := DBGrid1.Fields[2].asstring;
end;

end.

Thanks!

Answer 1

You're using the wrong syntax for your query.
The query does not use Delphi syntax and := does not make sense in that context.

Change the query to:

UniQuery1.SQL.Add('UPDATE barang SET id= :i, name= :nam, stock = :st where id= :i');

The : is a prefix that tells TQuery that these are named parameters.
Furthermore it makes little sense to set id = :i where id = :i that's a no-op.
So you can simplify the query to:

UniQuery1.SQL.Add('UPDATE barang SET name= :nam, stock = :st where id= :i');

In addition you don't have to insert/edit the queries.
These methods do not do what you think they do.

The insertion and editing is already being done by your SQL statements.
Don't use SQL.Add. It's slow and error prone, because if there is already text in your SQL the added text will clash with the SQL that's already there.
Never use SQL.Add ever again.

Change the first method like so:

procedure TForm1.Button4Click(Sender: TObject);
begin
      UniQuery1.SQL.Text:= 'UPDATE barang SET name= :nam, stock = :st where id=:i';
      UniQuery1.ParamByName('i').AsString := Edit1.Text;
      UniQuery1.ParamByName('nam').AsString := Edit2.Text;
      UniQuery1.ParamByName('st').AsString := Edit3.Text;
      UniQuery1.ExecSQL;
end;

This method does not make any sense.

procedure TForm1.Button5Click(Sender: TObject);
begin
  UniQuery1.Insert;  //insert what? A query is not a table.
  UniQuery1.FieldByName('ID').AsString := Edit1.Text;
  UniQuery1.FieldByName('Name').AsString := Edit2.Text;
  UniQuery1.FieldByName('Stock').AsString := Edit3.Text;
  UniQuery1.Post;  //makes no sense here.
end;

Just replace this with a INSERT INTO.... sql statement.

Finally the last method should look like:

procedure TForm1.Button6Click(Sender: TObject);
begin
  UniQuery1.SQL.Text:= 'DELETE FROM barang where id=:i';
  UniQuery1.ParamByName('i').AsString:=edit1.Text;
  UniQuery1.ExecSQL;
end;

Surely you've figured out the there is no method called SQLdelete?

You need to rethink the concept.
It's the SQL statement that does the work.
The Query only cares if the statement is a select -> if so so Query.Open.
Or if it will change the data (delete/insert/update) -> so Query.ExecSQL.
All the rest is done in the SQL.Text.

Query.Edit etc
Yes you can do Query.Edit.
This puts the dataset in edit mode and allows the user to change fields in the query. The database layer will then transmit these changes to the underlying database tables.
However this only works if the query is simple. If not it will silently break and not update your tables.
Only use edit/insert/delete/post/cancel etc with Tables.