SQL.Add onChange

[lespaul]

It's me again!

I'd like to write a procedure/function that I can pass the edit box name and text into if the edit box has been changed to add to the SQL update query. The edit box names are standardized to ebFIELDNAME.

How do I pass object information into the function/procedure and set the OnChange event to that procedure instead of the procedure edbox.OnChange?

thanks for any input!!

Leslie

 

[McMerfy]

Here's a sample code.

  TForm1 = class(TForm)
    ed1              : TEdit;
    btnSetHandler    : TButton;
    btnRestoreHanler : TButton;
    procedure btnSetHandlerClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ed1Change(Sender: TObject);
    procedure btnRestoreHanlerClick(Sender: TObject);
  private
  public
    OldHandlers : TList;
    procedure edOnChange(Sender : TObject); // New OnChange handler.
    procedure SetNewOnChange(Sender : TObject);
    procedure RestoreOldHandler(Sender : TObject);
  end;

var Form1: TForm1;

implementation

procedure TForm1.edOnChange(Sender: TObject);
begin
  ShowMessage('tada');
  // Your OnChange code here...
end;

procedure TForm1.SetNewOnChange(Sender: TObject);
var
  OldOnChange : TNotifyEvent;
  AEdit       : TEdit;
begin
  if(not(Sender is TEdit))then Exit;
  AEdit := TEdit(Sender);
  if (AEdit.Tag > 0) then Exit;
  OldOnChange := AEdit.OnChange;
  if Assigned(OldOnChange)then
  begin
    AEdit.Tag := OldHandlers.Count;
    OldHandlers.Add(@OldOnChange);
  end;
  AEdit.OnChange := edOnChange;
end;

procedure TForm1.btnSetHandlerClick(Sender: TObject);
begin
  SetNewOnChange(ed1);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  OldHandlers := TList.Create;
  OldHandlers.Add(pChar('Dummy'));
end;

procedure TForm1.RestoreOldHandler(Sender: TObject);
var
  OldOnChange : TNotifyEvent;
  AEdit       : TEdit;
begin
  if(not(Sender is TEdit))then Exit;
  AEdit := TEdit(Sender);
  if (AEdit.Tag <= 0)then Exit;
  OldOnChange  := nil;
  @OldOnChange := OldHandlers.Items[AEdit.Tag];
  OldHandlers.Items[AEdit.Tag] := nil;
  AEdit.OnChange := OldOnChange;
  AEdit.Tag      := 0;
end;

procedure TForm1.ed1Change(Sender: TObject);
begin 
  ShowMessage('blahblah'); 
end;

procedure TForm1.btnRestoreHanlerClick(Sender: TObject);
begin
  RestoreOldHandler(ed1);
end;

Cheers.

--- markus

 

[lespaul]

Thanks for the code! I'm a Delphi newbie and can't quite figure out what this is doing! Could you post some explanation of what you're doing? Thanks!

Leslie

 

[LucieLastic]

hi lesPaul

What exactly do you want to achieve, are you wanting simply to update certain fields ? or are you wanting the user to be able to write the sql they want to run ?

cheers
ChetAtkins
...another fan here

 

[lespaul]

I want to be able to create my SQL update statement by seeing if the edit box has been changed. So I load the database with basic information NAME, SSN, ADDRESS. The person is then sent a questionnaire. When the questionnaire is returned we add information to the database (Employer, Sex, Phone Numbers). So when the user selects Joe Blow for the first time, only the basic information is loaded into the form. When the additional information is added, I need to update the database (I have a field in the Database named UPDATE that is initially set to F so that I only load existing information into the form once the additional data is added I need to update the database and set that field to 'T' to indicate that the additional data is available to be loaded into the form). But sometimes some of the basic information will also be update (ie address is uploaded as 123 Main St NW Apt - we would then update that basic information to include the missing apt #). So, I thought that I could somehow see if the edit box had changed (OnChange event) and which ever edit box it was take the name of the box and the updated text to construct the Update Query. For example, the edit box that has the phone number is named "ebHMPHONE" and the field in the database is named "HMPHONE". So to create the query.SQL.Add I could trim off the eb and have my field name and set the update query to the new text in the edit box. If there is an easier way to accomplish this, that would be great, but I didn't want to have to create OnChange events for all the edit boxes! I wanted to create ONE that I could pass the name and text into to create my SQL statement. Make sense?

I would appreciate any assistance.

Leslie

 

[EricDraven]

You could add an onEnter event and store the original text of an edit and then instead of using the onchange event use the onexit event and take the new text to see if it has changed. If it has you could use some code like this to grab the edit boxes name


var
S : String
begin
if Sender is TEdit then
begin
S := TEdit(Sender).Name; //Stores Name in S
Delete(S,1,2); //Deletes first 2 characters from string
end;

Add to your SQL here!

end;

If using things other than Edit boxes as well, add "if Sender is" statements as above for those types (i.e. TComboBox). Arte Et Labore

 

[LucieLastic]

It sounds like DBEdits would do the trick, together with RequestLive := true and possibly CachedUpdates := true aswell so that the user has chance to cancel changes.

Then at the bottom of your form have a 'SAVE CHANGES' button which will execute your myquery.applyupdates.

Just remember that Request live can not be true for a dataset which is composed of joined tables.

Have you used DBEdits before? if so, why not now?

lou

 

[svanels]

It would be easier to create a log table and every time a record is edited, put in the record and the date/time.

Sort like:

1/1/2001 16:00 Record BlaBla updated.

Used often in to record financial transactions Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

thanks for all the help!

No I haven't used DBEdits before (I've only been using Delphi for about a MONTH now!). But I am using joined tables, so I guess it wouldn't work. It seems that Eric's suggestion is the best so far! Would I be able to name it procedure AddSQL and put it in each of the OnExit Events for my edit boxes?

Thanks!
Leslie

landrews@metrocourt.state.nm.us

 

[svanels]

Les, I suggest you seriously look at the data-aware components like, dbedit, dbcombox, dblistbox, dbgrid, dblookupbox, dblookuplistbox etc. else you will be trying to reinvent the wheel. The bulk work of synchronizing the table with the user interface has already done by Borland. There are cases where you will need to do some customizing, but they are rare. The code from McMerfy is definitely not a beginners welcome.

Regards Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

Steven,

thanks for the response (I need all the help I can get - the easier the better!)

So here's what I've got so far, any further direction would be appreciated:

A form called Juror Search with a combobox filled in by a query, an edit box for last name, and an edit box for SSN. Depending on which criteria the user fills in, a query is created that populates a DBGrid.

PanelID LastName FirstName SSN Status
##### Blow Joe 111-22-333 NR

In the OnDoubleClick event of my DBGrid, I use the unique id (not displayed) to collect the information needed for the Juror Information form.

The Juror Information form then runs a query using the unique id from the Search form to get the detailed information about that juror. Then the fields on the form are filled in.

So how would I go about using some of the components you mentioned?

Thanks!

Leslie

 

[lespaul]

OK!

I added a data set to my Juror Information Form, changed my edit box to a DBEdit, assigned the FieldName - which it filled in - but it won't let me edit it! the read only is set to False. Is there something special I have to do to be able to edit?

Thanks!

leslie

 

[svanels]

If you are using a query set requestlive to true if it is a single query Steven van Els
SAvanEls@cq-link.sr

 

[svanels]

Check out Auto-complete in DBEdit?

thread102-322045 in this forum, maybe you have some similarities

Regards Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

So I get the following error when I set RequestLive - True and try to change something. I don't get to change it - as soon as I press any key within a DBEdit box it appears:

Invalid Expression.
Token = was not valid. Valid Tokens: FOR WITH ORDER UNION OPTIMIZE

Do I need a different query for the update? If so, how and what do i need to do with it?

As it stands right now I have two queries on the form and two datasets. QRYStatus and DSStatus (retreive a table with Status codes and descriptions to fill in a DBComboBox for choices for the Juror Info - and it's set to display the value that is returned in the Juror Query) and QRYIndJuror and DSIndJuror. I have created all the DBEdit fields and it fills them in but I get the error!

Another question:
I also have a DBRadioGroup called Public Employees. The form has nice 'Yes' and 'No' on the buttons, but the database query returns T or F (now my Sex Radio buttons work because the Database stores the same info as the Radio Button group displays). How can I tell it if the Field PubEmp is T mark Yes and if F mark no?

Thanks!

Leslie

 

[svanels]

About the DBRadioGroup, in the property items you put the Values as shown to the user. In your case Yes or No. In the property values you put what is stored in the Database, in your case T and F.

An updatable query must be a simple query, and not a combination of various tables (no union etc.)
A work around is to use the structure of a master-detail query.
This is 2 or more seperate queries on the same form, where the master query is used for scrolling the records. Use the Form wizard in the the Database menu to see how it is constructed. Steven van Els
SAvanEls@cq-link.sr

 

[lespaul]

I tried the Master Detail and I don't think that's going to work.
My join was to bring back a description of a particular field. ie - the jurors have different status codes. We store the StatusCode in the Juror database and the status description in the Status database. On my form I want to display the description in my DBComboBox, not the status code. So my query was

SELECT JURNUM, FIRSTNAME, LASTNAME, STREET1, STREET2, CITY,
STATE, ZIPCODE, SSN, ORGPOST, TERMDATE, DOB, SSN, SEX, HMPHONE,
STDESC, UPDATE, PUBEMP FROM JMLIB/JMPMAIN
INNER JOIN JMPSTATUS ON
JMPMAIN.STATUSCD = JMPSTATUS.STCODE
WHERE JURNUM = :CURRCRIT

And I had finally got it to show the description where it equaled the Status Code in the JurorMain database (I was really pleased, but without the join it doesn't work anymore!)

So how can I show the StatusTable Description field that matches the JurorMainTable StatusCode with out a join?

Thanks!
Leslie

 

[LucieLastic]

Hi

Have a look at creating Lookup Fields in your main query.

You will need a 2nd query (eg qry_StatusCodeLookup) which will contain simple 'select * from jmpStatus'.

Then remove the join from the main query. Double click the qry comp and right click, select 'New Field' and choose 'Lookup' in the the Field type part. Then fill in the boxes. KeyFields will contain the id field of the main query, Dataset will be set to qry_StatusCodeLookup. choose the matching ID field in the Lookupkeys and select the desc. field in the Result field.

Make sure the lookup query is opened when your main query is open else nothing may appear.

Hope you can follow that.
lou