update multiple rows using dbgrid

Question

I'm super new at coding and I'm making a simple apps now.

So as you can see in the pic, I have multiple data in selected date. If I wanted to update every row with different values and do it with save button, what code should I do?
Does my dbgrid need more setting at its properties?
I'm using Delphi 7 and zeos as db connection as well as oracle as db. Thanks in advance.

A DBGrid edits one row at a time. If you need to update multiple rows, use a SQL UPDATE statement with the appropriate WHERE clause. — Ken White, Dec 05 '17 at 02:24
Not clear what you want. Do you want to click on a button and then have all rows in the grid updated ? Or do you want to edit different values in one or more rows in the grid, and then click on the button to save these changes in the database ? — GuidoG, Dec 05 '17 at 12:11

Amir · Answer 1 · 2017-12-05T09:15:14.403

Use an Update Query

select "Database"
Update "tablename" 
set "colonnename1"="new value, "colonnename1"="new value, ...
where "colonnename"=value

this is the update query and while using delphi you can do this :

begin
uniquery1.clear;
Uniquery1.SQL.Add('update person set name ='''+edit1.Text+''',lastname='''+edit2.Text+''',age='+edit3.Text+',city='''+edit4.Text+''' where date=' + edit5.text);                                     
uniquery1.ExecSQL;
messagedlg('Successfully Modified!',mtinformation,[mbOK],0);
end;

Or you can use the parametrized method which is way better then concatenating the SQL As NIL said :

begin
Uniquery1.sql.clear;
Uniquery1.SQL.Add('UPDATE person SET  "1colonnename"= ":parmID", name= :sal WHERE Num='+ inttostr(strtoint(dbgrid1.DataSource.DataSet.FieldValues['num'])));                             uniquery1.ParamByName('paramID').value:=edit1.text;
uniquery1.ParamByName('sal').Value:=edit2.text;
uniquery1.ExecSQL;
end;

This is just an exemple but this how it works ! Good luck

I would suggest using a parameterized query instead of concatenating the SQL using direct user input. Funny things may happen otherwise, see SQL injection. — nil, Dec 05 '17 at 08:49
yes, Absolutely i'm just used to concatenating my SQL query but of course the best method is Parametrized query ! thank you for reminding me ! — Amir, Dec 05 '17 at 09:16

score 0 · Answer 2 · answered Dec 05 '17 at 08:11

There are different ways depends of what you want and what you use.

If you wish to edit several rows into DBGrid and save them simultaneously, most dataset components supports a CachedUpdate mode (look into the Zeos documentation for details). In this mode dataset holds all changes made until you call ApplyUpdates method to put these changes into DB using one batch.

Using Oracle, you can do it without cached updates, just use 'long transaction'. In this case you should start transaction when form opens, edit and post data at usual way and commit them when Save pressed. This way have some disadvantages, but I doubt you'll face them.

If you wish to do some sort of mass update when pressing Save button, you have two ways. First of all, you can set up a query component, set it's SQL property into something like

update My_Table set My_Field_1 = :Value1, My_Field_2 = :Value2

and call it into save action handler:

qUpdate.Params.ParamByName('Value1').AsString := edFirstValue.Text;
qUpdate.Params.ParamByName('Value2').AsString := edSecondValue.Text;
qUpdate.ExecSQL;
qMain.Refresh; { we're in need to see updates values, yeah? }

Otherwise, you can do it from client:

qMain.DisableControls;
try
  Bookmark := qMain.Bookmark;
  qMain.First;
  while not qMain.Eof do
  begin
    qMain.Edit;
    qMain.FieldByName('My_Field_1').AsString := edFirstValue.Text;
    qMain.FieldByName('My_Field_2').AsString := edSecondValue.Text;
    qMain.Post;
    qMain.Next;
  end;
finally
  qMain.Bookmark := Bookmark;
  qMain.EnableControls;
end;

That's, generally, not so good way, so don't addict of that.

update multiple rows using dbgrid

2 Answers2