using DBGrid to make changes to Interbase 1

[DaZZleD]

I am using a DBGrid component with a DBNavigator to visualise - through a DataSource - the dataset generated by a IBQuery (which contains a select statement). I also use IBDatabase, IBTransaction and IBUpdateSQL.

Each change that the user makes affects the dataset instantly but doesn't affect the database. From what I've gathered from the Delphi 5 documentation, the only way to make inserts, deletes and modifies to an Interbase database is through the InsertSQL, DeleteSQL and ModifySQL properties of the IBUpdateSQL component. My question is, how do I build these statements while the user browses the records in DBGrid, and when and how I apply them? Or, is there an easier way for this?

Thanks in advance,
Alex

 

[DaZZleD]

I finally figured it out... It works very well using two events that 'cause' navigation in the DBGrid, and it inserts new records to the db, updates the one being edited, deletes the selected one. Also works with DBNavigator. If anyone is interested, i will post the code for it.

 

[egil84]

I'm looking for thr same answer.
If you could post your code, I'd greatly appreciated it.
Thanks in advance.

 

[DaZZleD]

ok... the principle is quite simple though it takes a bit more to implement. as you know with Interbase you have to build the modify, delete, insert SQL statements of the IBUpdateSQL component in order to make any modifications to the DB. The problem is that my DB had no index defined so I had to build the whole statement. If your DB has an index things should be more easy. I will first post the code for my case, then addapt it to when there is an index.

    curentRecord : integer;                  // index of current record in the DataSet
    modif: TBookmark;                        // position of the current record
    inserting, edit : boolean;               // whether editing or inserting a new record
    procedure setediting(value : boolean);
    property  editing: boolean read edit write setediting;
    procedure UpdateData();                  // this procedure makes the actual modifications to the DB
    procedure ModifStatements(row : integer);// this procedure builds the statements
    procedure RefreshDB();                   // refresh of the DB (reloads all records from the DB)
    procedure DeleteRecord();                // deletes the record

the two events that I used are the DBGrid OnKeyUp(DBGrid1KeyUp) and OnMouseUp(DBGrid1MouseUp).

procedure TForm1.FormCreate(Sender: TObject);
begin
     form1.editing := false;
     form1.inserting := false;
end;

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
var
tmpBookmark : TBookmark;
begin
  if (ord(Key)=27) then    // on Esc press
  begin
       editing:=false;
       inserting:=false;
  end;
     // bookmark to the record you just moved to
     tmpBookmark := form1.DBGrid1.DataSource.DataSet.GetBookmark;
  // if the old record differs from the one after the KeyPress (you navigated to a new record) or you are inserting
  if (form1.curentrecord <> form1.DBGrid1.DataSource.DataSet.RecNo) and (form1.DBGrid1.DataSource.DataSet.RecNo <> form1.recordsnum+1) then          
  begin
       if editing then
       begin
         // go to the record you were editing before moving to the current one
         form1.DBGrid1.DataSource.DataSet.GotoBookmark(form1.modif);
         // apply the changes
         form1.UpdateData(); 
         // return to the current record
         form1.DBGrid1.DataSource.DataSet.GotoBookmark(tmpBookmark);
       end
       else if inserting then
       begin
         // insert the new data in the DB
         form1.UpdateData();  
       end;
       form1.inserting := false;  // done inserting
       form1.editing := false;    // done editing
       // build the statements for the current record
       form1.modifStatements(form1.DBGrid1.DataSource.DataSet.RecNo-1);  
  end
// if pressing any of the a-z,A-Z,0-9, and num keypad 0-9 keys while in the record
  else if ((ord(Key) > 65) and (ord(Key) < 90)) or
          ((ord(Key) > 96) and (ord(Key) < 105)) or
          ((ord(Key) > 48) and (ord(Key) < 57)) or
          (ord(Key) > 32) or (ord(Key) > 8) or (ord(Key) > 46) 
  // mark as editing
  then form1.editing := true;  
  // set the new record num
  form1.curentRecord := form1.DBGrid1.DataSource.DataSet.RecNo;  
  // bookmark the record
  form1.modif := form1.DBGrid1.DataSource.DataSet.GetBookmark; 
end;

// this does nothing but calls the OnKeyUp event of the DBGrid
procedure TForm1.DBGrid1MouseUp(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer); 
var
sst : TShiftState;
key : word;
begin
     sst := [ssShift];
     key := VK_LEFT;
     form1.DBGrid1KeyUp(Sender,key,sst);
end;

now for the fun part: the ModifStatements and the UpdateData procedures.

procedure TForm1.UpdateData();
var
query, what : string;
add : boolean;
i : integer;
begin
     add := false;
     what := '';
     if editing then
     begin
          if inserting then
          begin
              form1.DBGrid1.DataSource.DataSet.Last();
              query := 'insert into tableName(';
              for i:=0 to form1.DBGrid1.FieldCount-1 do
              begin
               if not form1.DBGrid1.Fields[i].IsNull then
               begin
                 if (add) then
                 begin
                     what := what + ', ';
                     query := query + ', ';
                 end;
                 what := what + '"' + form1.DBGrid1.Fields[i].AsString + '"';
                 query := query  + form1.DBGrid1.Fields[i].DisplayName;
                 add := true;
               end;
              end;
              form1.IBUpdateSQL1.InsertSQL.Clear;
              query := query + ') values (' + what + ')';
              form1.IBUpdateSQL1.InsertSQL.Add(query);
              form1.inserting := false;
              inc(form1.recordsnum);
              form1.IBUpdateSQL1.Apply(ukInsert);
              inserting := false;
          end
          else
          begin
              query := form1.IBUpdateSQL1.ModifySQL.Text;
              for i:=0 to form1.DBGrid1.FieldCount-1 do
              begin
               if not form1.DBGrid1.Fields[i].IsNull then
               begin
                 if add then what := what + ', ';
                 what := what + form1.DBGrid1.Fields[i].DisplayName + ' = "' + form1.DBGrid1.Fields[i].AsString + '"';
                 add := true;
               end;
              end;
              form1.IBUpdateSQL1.ModifySQL.Clear;
              query := StringReplace(query, '***', what, [rfReplaceAll, rfIgnoreCase]);
              form1.IBUpdateSQL1.ModifySQL.Add(query);
              form1.IBUpdateSQL1.Apply(ukModify);
              editing := false;
          end;
     end;
end;

procedure TForm1.ModifStatements(row : integer);
var
i : integer;
query : string;
begin
     if row=-1 then
     begin
          inserting := true;
     end;
          query := 'delete from tableName where ';
          for i:=0 to form1.DBGrid1.FieldCount-1 do
          begin
            if i > 0 then query := query + ' and ';
            if not form1.DBGrid1.Fields[i].IsNull then
              query := query + form1.DBGrid1.Fields[i].DisplayName + ' = "' + form1.DBGrid1.Fields[i].AsString + '"'
            else
              query := query + form1.DBGrid1.Fields[i].DisplayName + ' is null';
          end;
          form1.IBUpdateSQL1.DeleteSQL.Add(query);

          form1.IBUpdateSQL1.ModifySQL.Clear;
          query := 'update tableName set *** where ';
          for i:=0 to form1.DBGrid1.FieldCount-1 do
          begin
            if i > 0 then query := query + ' and ';
            if not form1.DBGrid1.Fields[i].IsNull then
              query := query + form1.DBGrid1.Fields[i].DisplayName + ' = "' + form1.DBGrid1.Fields[i].AsString + '"'
            else
              query := query + form1.DBGrid1.Fields[i].DisplayName + ' is null';
          end;
          form1.IBUpdateSQL1.ModifySQL.Add(query);
     form1.UpdateData();
end;

now for the DeleteRecord and the RefreshDB procedures and the navigator actions ('link' the )

procedure TForm1.DeleteRecord();
begin
    form1.DataSource2.DataSet.Close;
    if form1.IBTransaction1.InTransaction then
       form1.IBTransaction1.Commit;
    form1.IBTransaction1.StartTransaction;
    try
      begin
         form1.IBUpdateSQL1.Apply(ukDelete);
         dec(form1.recordsnum);
       end;
    except
    on E: Exception do
      begin
        ShowMessage(E.Message);
      end;
    end;
    form1.IBTransaction1.Commit;
    form1.DataSource2.DataSet.Open;
    form1.DBGrid1.Refresh;
end;

procedure TForm1.RefreshDB();
var
position : TBookmark;
begin
     position := form1.DBGrid1.DataSource.DataSet.GetBookmark;
     form1.DBGrid1.DataSource.DataSet.Close;
     form1.IBQuery1.Open;
// we need the Last, First actions to properly read the recordCount property
     form1.DBGrid1.DataSource.DataSet.Last;
     form1.DBGrid1.DataSource.DataSet.First;
     recordsnum := form1.DataSource2.DataSet.RecordCount;
     form1.DBGrid1.DataSource.DataSet.GotoBookmark(position);
end;

procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
var
  BtnName: string;
  sst : TShiftState;
  key : word;
begin
  case Button of
    nbInsert : form1.ModifStatements(-1);
    nbDelete : DeleteRecord();
    nbCancel : begin
                 form1.editing := false;
                 form1.inserting := false;
               end;
    nbRefresh: refreshDB();
    else
    begin
     sst := [ssShift];
     key := VK_LEFT;
     form1.DBGrid1KeyUp(Sender,key,sst);
    end;
  end;
end;

if you have any problems understanding the code or implementing it, let me know.

 

[SamC]

hi dazzled,

what version of Delphi are you using?
have you tried the TIBDataset?

 

[DaZZleD]

Delphi 5 Enterprise... and no.. I haven't tried it because I used this code with SQL and there's no use for TIBDataset there.

is there an easier sollution with TIBDataset?

 

[SamC]

i would think so.

the whole edit and navigation stuff is handled by the VCL.
the only thing you have to do is to configure the needed statements.(Insert,Modify,Delete,Refresh).

if interested i could give you a short explanation.

 

[DaZZleD]

then it's nothing different than before because that's exactly what I do. I build the statements and apply them if they are needed. I don't handle navigation, I just use the events to build statements (when entering a new row, i need to build the last part of a modify statement for example - the part with 'where field1=...'; or the delete statement).

 

[SamC]

that's right. the result is the same. but you'll get it without one line of code.

 

[DaZZleD]

you have to build the statements and that's all I coded in the example above.

 

[SamC]

if you're accessing known data (table) you can DEFINE the needed statements at design-time via wizzard.