Database access

Hi, thanks for this esoftbg.  This is a good start.

What I need to know is how do I actually execute procedures 1 and 2 by clicking a search button????????

I have added the first two procedures into my app and linked them up to my TQuery component matching them up with the appropriate events.  It all compiles well but I can't use them.

By the way, I take it I was supposed to use:
- TDatabase
- TTable
- TQuery
- TUpdateSQL

Once I can actuially get data displayed then I can look at trying the update DB fields procedures.

Your help is very much appreciated.  I look forward to your response.

> "What I need to know is how do I actually execute procedures 1 and 2 by clicking a search button????????"

You need only to call the event OnClick of the search button, and they will start the events QueryPortfolioBeforeOpen and QueryPortfolioAfterOpen:

procedure TForm1.SearchButtonClick(Sender: TObject);
begin
  QueryPortfolio.Open;
end;

For my example you need:
- TDatabase
- TQuery
- TTable (if you decide to use some TDBEdit for editing the fields)
- TUpdateSQL (if you decide to use some TEdit for editing the fields)

I would like to say that my example was described as to answer your order of questions: 1., 2., 3., 4.
It is not the only way. May be you need to use TDBEdit...
I don't know what is you DataBase. I am using INTERBASE (FIREBIRD) and have experience for it.
There is many ways to solve a task !!!!
Regards

Sounds good esoftbg.  I can now do more than one search and the feilds are being updated on the form...all good and sorted there.

I am now having problems doing the UpDateSQL statement.

procedure TForm1.EditEPSChange(Sender: TObject);
var
 T:      string;
begin
// 3.
 if CanPost then
 begin
   T := ''
      + ' UPDATE Portfolio SET EPS = '
      + '''' + EditEPS.Text + ''''
      + ' WHERE ShareCode = '
      + '''' + EditShareCode.Text + ''''
      + '';
   UpdateSQLPortfolio.ModifySQL.Text := T;
   UpdateSQLPortfolio.ExecSQL(ukModify);
 end;
end;

It does compile but fails then I try to change this field.

It says:

"Table does not exist.  File or directory does not exist"

I have had a look at the "UpdateSQL Editor" and I doesn't seem to recognise the DB, can't even select a table.  Any ideas on how to link this in?

One more obsticle and all done!  

Thanks for you help.

Excuse me I did not say about the link:
You need to link QueryPortfolio.UpdateObject := UpdateSQLPortfolio (in Object Inspector).

Thanks for this esoftbg, I can now update the DB using either OnChange for the editbox or having an update button.

I guess I can use UpdateSQL for adding another record to the DB?  Would this work?  I'm not sure how to find the last record in a DB though.

Thanks.

It is a better idea to having an update button:

procedure TForm1.ButtonUpdateClick(Sender: TObject);
var
 T:      string;
begin
 if CanPost then
 begin
   T := ''
      + ' UPDATE Portfolio SET EPS = '
      + '''' + EditEPS.Text + ''''
      + ', DPS = '
      + '''' + EditDPS.Text + ''''
//    + All fields
      + ' WHERE ShareCode = '
      + '''' + EditShareCode.Text + ''''
      + '';
   UpdateSQLPortfolio.ModifySQL.Text := T;
   UpdateSQLPortfolio.ExecSQL(ukModify);
 end;
end;

and to having an insert button:

procedure TForm1.ButtonInsertClick(Sender: TObject);
var
 T:      string;
begin
   T := ''
      + ' INSERT INTO Portfolio VALUES ('
      + '''' + EditEPS.Text + ''''
      + ', ' + '''' + EditDPS.Text + ''''
//    + All fields
      + ')'
      + '';
   UpdateSQLPortfolio.InsertSQL.Text := T;
   UpdateSQLPortfolio.ExecSQL(ukInsert);
end;

To find the last record in a DB you can use another TQuery without clause WHERE in the SQL.Text:

var
 S:      string;
 T:      string;
begin
  if QueryLast.Active then
    QueryLast.Close;
  T := ''
     + 'SELECT * FROM Portfolio'
     + '';
  QueryLast.SQL.Text := T;
  QueryLast.Open;
  QueryLast.Last;
  S := QueryLast.FieldByName('ShareCode').AsString;
end;

and may be to having an delete button:

procedure TForm1.ButtonDeleteClick(Sender: TObject);
var
T:      string;
begin
  T := ''
     + ' DELETE FROM Portfolio'
     + ' WHERE ShareCode = '
     + '''' + EditShareCode.Text + ''''
     + '';
  UpdateSQLPortfolio.DeleteSQL.Text := T;
  UpdateSQLPortfolio.ExecSQL(ukDelete);
end;

Thanks for this.  Delete works fine but I am having problems with the insert.

I am trying to add a new record into the DB.  I have found the last record and increased the Primary Key by one.

I then try to do the insert procedure above passing ALL fields but get the following error:

"General SQL error.
[Micrsoft][ODBC Microstoft Access Driver] Number of query values and destination fields are not the same."

Any idea what this means?  Help, I am getting close!  You have been a huge help.  :-)

The reason for this error:
"General SQL error.
[Micrsoft][ODBC Microstoft Access Driver] Number of query values and destination fields are not the same."
is that you describe MORE or LESS fields in:

procedure TForm1.ButtonInsertClick(Sender: TObject);
var
T:      string;
begin
  T := ''
     + ' INSERT INTO Portfolio VALUES ('
     + '''' + EditEPS.Text + ''''
     + ', ' + '''' + EditDPS.Text + ''''
//    + ALL FIELDS
     + ')'
     + '';
  UpdateSQLPortfolio.InsertSQL.Text := T;
  UpdateSQLPortfolio.ExecSQL(ukInsert);
end;

If your Primary Key is "Auto increment" in the DataBase, may be you don't need to describe it in code above. I am not sure because in INTERBASE I am using Generator for the Primary Key Value.
The reason could be that you forget to describe one or more fields in code above, but they are in the DataBase....
  I hope it helps !

Thanks

I managed to get everything going.  I got Insert working by doing the following:

procedure TForm1.InsertClick(Sender: TObject);
var
T:      string;
begin
  QueryPortfolio.UpdateObject := UpdateSQLPortfolio;

  T := ''
     + ' INSERT INTO Portfolio (ShareCode,SharePrice,EPS,DPS,EPSGrowth,PERatio,SHF,TargetReturn,Period) VALUES ('
     + '''' + edtShareCode.Text + ''''
     + ', ' + '''' + edtSharePrice.Text + ''''
     + ', ' + '''' + edtEPS.Text + ''''
     + ', ' + '''' + edtDPS.Text + ''''
     + ', ' + '''' + edtEPSGrowth.Text + ''''
     + ', ' + '''' + edtPERatio.Text + ''''
     + ', ' + '''' + edtSHF.Text + ''''
     + ', ' + '''' + edtTargetReturn.Text + ''''
     + ', ' + '''' + edtPeriod.Text + ''''
     + ')'
     + '';

  UpdateSQLPortfolio.InsertSQL.Text := T;
  UpdateSQLPortfolio.ExecSQL(ukInsert);
end;

Thanks again.  No doubt I will have questions in the future, keep an eye out.  Here are the points.

THANKS!  :-)

Thanks,
I will keep un eye out.
esoftbg :-)