Keep user from moving off current record

[dodgebros]

Thanks rvk. 

1) Actually I didn't disable scrolling in the DBGrid.  I did exactly as you say and let the ShowModal take care of that.  I do have dgEditing disabled which disables editing in the DBGrid.  I enable dgRowSelect, just because selecting a whole row looks better.

2) I could...

3) I had already decided I needed to return to the same record after the Close/Open.  I tried using bookmarks but couldn't get it to work and also some comments in the forum suggested there were better ways to do this.  So in the end I used .Locate.   Here is the code as it stands now, FYI the field 'pid' is an auto-generated number that is unique for each record:

Code: [Select]

procedure TfrmEdit.btnCommitClick(Sender: TObject);
var
  varPID : variant;
begin
  varPID := DBEdit1.Text;
  with DataModule1.SQLQuery1 do
    begin
      Edit;
      Upda]"]>Blockedde:=UpWhereChanged;
      ApplyUpdates;
      DataModule1.SQLTransaction1.Commit;
      Close;
      Open;
      DataModule1.SQLQuery1.Locate('pid',varPID,[]);
    end;
  frmEdit.Close;
end;


[rvk]

1) Actually I didn't disable scrolling in the DBGrid.  I did exactly as you say and let the ShowModal take care of that.  I do have dgEditing disabled which disables editing in the DBGrid.  I enable dgRowSelect, just because selecting a whole row looks better.

Actually, I meant to say you disable Editing and enable Rowselect (of the DBGrid) when entering the form (and reversed when exiting the form). I don't think that's necessary. You can completly remove those lines in btnEditClick (on the mainform) and FormClose (in the edit-form).
 

Code: [Select]

procedure TfrmMain.btnEditClick(Sender: TObject);
begin
  // DBGrid1.Options:=DBGrid1.Options - [dgRowSelect];   <-- NO NEED
  // DBGrid1.Options:=DBGrid1.Options + [dgEditing];     <-- NO NEED
  DataModule1.DataSource1.DataSet.Edit;
  frmEdit.ShowModal;
end; 
and

Code: [Select]

procedure TfrmEdit.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  // frmMain.DBGrid1.Options:=frmMain.DBGrid1.Options + [dgRowSelect];  <--- NO NEED
  // frmMain.DBGrid1.Options:=frmMain.DBGrid1.Options - [dgEditing];    <--- NO NEED
end;

There is no need to set the DBGrid [dgEditing] because you don't use the inline editor in DBGrid. (This options only applies to the DBGrid, not the Dataset)

I do agree dgRowSelect is the best choice in a DBGrid. Furthermore... you could implement a DblClick to activate the edit-form (doubleclicking to edit) and maybe also do a check on VK_RETURN in the KeyDown of the DBGrid so the user can press "enter" to go into the edit-form. (I always like keyboard shortcuts. All my programs are completely usable with the keyboard, even without a mouse) And while you're at it you could also check for VK_INSERT to insert a record (also showing the edit-form after an DataModule1.DataSource1.DataSet.Insert).

[dodgebros]

2) You could also create a new TSQLQuery and use that in the edit-form. When opening the edit-form you can give it the unique id of the record to be edited. Only downside is that when you close the edit-form you need to refresh the first form to re-read the changed record. (this could be done with a sendmessage)

I have a TSQLQuery on  DataModule1 that frmMain.DBGrid1 is connected to.  When I open frmEdit I would like it to show the data for the selected record in DBGrid1.

Do accomplish the suggestion above do I need to:

1) Add another TSQLQuery component to the DataModule and set it to the same table as the TSQLQuery that feeds DBGrid1 then add params to this new TSQLQuery component?  Just realized I'll need another TSQLTransaction and a TDatasource also to go with the new TSQLQuery, correct?

2) Create a TSQLQuery via code without adding another TSQLQuery component to DataModule1.

Being new to Lazarus I don't even know if you can create a TSQLQuery with just code...can you?

Anyway, after you guys answer this set of questions, I need to ask some more regarding how to use params with a TSQLQuery. 

Thanks,
TD

[taazz]

2) You could also create a new TSQLQuery and use that in the edit-form. When opening the edit-form you can give it the unique id of the record to be edited. Only downside is that when you close the edit-form you need to refresh the first form to re-read the changed record. (this could be done with a sendmessage)

I have a TSQLQuery on  DataModule1 that frmMain.DBGrid1 is connected to.  When I open frmEdit I would like it to show the data for the selected record in DBGrid1.

Do accomplish the suggestion above do I need to:

1) Add another TSQLQuery component to the DataModule and set it to the same table as the TSQLQuery that feeds DBGrid1 then add params to this new TSQLQuery component?  Just realized I'll need another TSQLTransaction and a TDatasource also to go with the new TSQLQuery, correct?

Is there a need to do that or you are just trying to learn? In any case yes TSQLQuery means new tdatasource to link it to the query. Everything else is in flux. For example you can use the old connection/database with the new query or you can add a new one too, recommendation : link against the old one. You can also use the existing transaction with out creating a new one but it requires a bit of caution, if for any reason you commit/close the transaction ALL the queries connected to it will be closed too. That might be a sought behavior or an unwanted side effect.
 

2) Create a TSQLQuery via code without adding another TSQLQuery component to DataModule1.

Being new to Lazarus I don't even know if you can create a TSQLQuery with just code...can you?

It doesn't really matter either through code or through the designer the above conditions must be met. so here is a quick pseudo code for doing  it via code. keep in mind that there are things other than the data object them selfs that you need to take in to account when doing it from code.

Code: [Select]


procedure EditCustomer(const aDB:TSQLConnection; const aCustomerID:integer);
var
  vQuery :TSQLQuery;
  vTrans :TSQLTransaction;
  vDSource:TDatasource;
  vFrm : TCustomerEditForm;
begin
  vQuery := TSQLQuery.create(nil);//nil = the owner, usully has the value of the datamodule/form/frame you placed the query on.
  vTrans :=TSQlTransaction.create(nil);
  vDSource := TDataSource.create(nil);
  try
     vQuery.Database := aDB;
     vQuery.SQL := 'Select * from customers where CustID ='+inttostr(aCustomerID);
     vQuery.Transaction := vTrans;
     vDSource.Dataset := vQuery;
     vFrm := TCustomerEditForm.create(nil);
     vFrm.DataSource:=vDSource;
     vQuery.open;
     vQuery.Edit;
     If vFrm.Showmodal = mrOk then vTrans.Commit else vTrans.Cancel;
  finally
    vquery.free;
    vTrans.Free;
    vDsource.free;//make sure we do not have a memory leak.
  end;
end;



That is in short what is required to do it in code. Now let me add this.

In my applications every edit form that has Data aware controls on it has its own Datasource there too. That makes it easy to link any SQLQuery I want that has all the fields the form edits with out the need to go through all the components at runtime to assign the datasource to them I simple assign the SQLQuery to the existing datasource which all components are already linked to and ready to edit;

[rvk]

2) You could also create a new TSQLQuery and use that in the edit-form. When opening the edit-form you can give it the unique id of the record to be edited. Only downside is that when you close the edit-form you need to refresh the first form to re-read the changed record. (this could be done with a sendmessage)

Is there a need to do that or you are just trying to learn?

No, there was no need to do that in this (simple) example.

It was actually my suggestion but was meant to be an example for if you want more advanced forms. For example... if you want multiple forms open for editing it's best that each of those form has its own TSQLQuery, TDataSource and even TTransaction. All forms can post (or rollback) individually to the same TDatabase but have their own TSQLQuery.

Also the main form (with the DBGrid) wouldn't have to do a select on all fields you wanted to edit in a subform. Only the fields to be displayed needed to be in the select on the mainform and only when entering an edit-form the complete select needed to be done.

At least that's how I see it for a more complex multiform application.

For a simpler application with just a DBGrid and one subform with edit fields (which could be shown with Showmodal) the simplest solution would be to use one TSQLQuery and use that throughout your application for that table.

[dodgebros]

Thanks guys for answering my questions. 

One other question, is having a large number of TSQLQuery, TDatasource, and TSQLTransaction components on a DataModule cause any issues?

Thanks,
TD

[rvk]

One other question, is having a large number of TSQLQuery, TDatasource, and TSQLTransaction components on a DataModule cause any issues?

The number of TSQLQuery (TDataSource, TSQLTransaction) doesn't matter at all. It's virtual unlimited. It's only when you're going to set TSQLQuery.Active to true (or TSQLQuery.Open) that the database is really contacted. So I imagine that having thousands of TSQLQuery's open at the same time would have an impact. For a multiform-application it's also best not to let your program autocreate your forms but create them yourself in code only when it's necessary. That way you'll never have more TSQLQuery's than forms.

There is something else you should be aware of. When having multiple forms (and TSQLQuery) open, you start a transaction (if you use separate transactions). The changes are not visible to other forms where a transaction is also started (unless it is started as Read Committed). If you really going to create a multiform application it might be a good idea to read up (and understand) the transaction system. There are some good tutorials out there. Here is a slide-show for "How Firebird transactions work".

For relative simple applications (a few forms with just one transaction) this isn't very important but as your application gradually grows it might become relevant.

[dodgebros]

There is something else you should be aware of. When having multiple forms (and TSQLQuery) open, you start a transaction (if you use separate transactions). The changes are not visible to other forms where a transaction is also started (unless it is started as Read Committed). If you really going to create a multiform application it might be a good idea to read up (and understand) the transaction system. There are some good tutorials out there. Here is a slide-show for "How Firebird transactions work".

Awesome feedback.  I am new so I may be doing things incorrectly.  I always create a TSQLQuery, TDatasource, and a TSQLTransaction together.  Should I be creating a TSQLTransaction for each TSQLQuery?  Can you "share" a TSQLTransaction across multiple TSQLQuerys?  I will indeed read up on this.

Thanks,
TD

[rvk]

Should I be creating a TSQLTransaction for each TSQLQuery?  Can you "share" a TSQLTransaction across multiple TSQLQuerys?  I will indeed read up on this.

Yes, you can share a TSQLTransaction between TSQLQuerys, but it depends on the situation if you want/need to do this. In your example (with a simple ShowModal-form to edit data) using the same TSQLTransaction that's connected to the TSQLConnection is not wrong. It's simple, clear to understand and does the job. You could even do with one TSQLQuery on your datamodule but could also have TSQLQuerys on the forms connected to the same TSQLTransaction.

But picture this... Your same customer database is growing to include offers, orders and invoices. You have the possibility to open 2, 3 or even more customer-detail-screens at the same time, while having multiple orders and invoices open too. Imagine how many TSQLQuerys you need on your datamodule. Especially if you're going to allow multiple customers, orders and invoices open at the same time it's best to create the TSQLQuery/TDatasource in the form where it is used.

As for the TSQLTransaction... if all TSQLQuery's where connected to the same TSQLTransaction (the one on a datamodule), if one TSQLQuery committed the transaction all the others would be committed too (if a Post was already issued). Granted... the time between a .Post and .Transaction.Commit on the other forms is small but it could happen. Having transaction control on the form itself gives you the ability to control it better (i.e. Rollback, Commit etc).

There are situations where you want to use the same TSQLTransaction between TSQLQuerys, for example when you want to access already posted data but not yet committed from that other query.

But as said... above is for multiple editing at the same time. For single editing (i.e. in ShowModal) one TSQLTransaction is fine. It's just that in the future you'll probably encounter situations where you need to understand transactions and use them. If you read-up on transactions it will become apparent when you should use them and where you can share them.

Have fun learning about transactions