Database access

I hope someone can help me with my problem.  Below is a Table from a DB I am trying to access.  

On my form I have an input box and a search button.  I also have 6 boxes to show the results.

DB Name = shares.mdb
DB Table = Portfolio

ShareID       ShareCode   SharePrice   EPS      DPS   EPSGrowth   PERatio   SHF
1       SKC           866        50      44    17        16          113
2       WHS           540        31      15    16        17          61

What I want to be able to do is:

1.
Perform an SQL quary when the search button is clicked taking the ShareCode from the user and putting it into the statement: SELECT * FROM Portfolio WHERE ShareCode = [Enter Share Code];

2.
Write each result to a sepearte edit field e.g. EPS value in edit2 box and DPS value in edit2 box.

3.
Update a field if the user changes a value e.g. EPS value change on form, update DB EPS field.

4.
Where are results from quaries stored?  How do you access fields in the search result?  Question one will probably answer this one.

I am easy as to what components to use, never done this before so any suggestions would be great.

Thanks.
nzbossAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

esoftbgCommented:
var
  CanPost: Boolean;

procedure TForm1.QueryPortfolioBeforeOpen(DataSet: TDataSet);
var
  T:      string;
begin
// 1.
  CanPost := False;
  T := ''
     + 'SELECT * FROM Portfolio WHERE ShareCode = '
     + '''' + EditShareCode.Text + ''''
     + '';
  QueryPortfolio.SQL.Text := T;
end;

procedure TForm1.QueryPortfolioAfterOpen(DataSet: TDataSet);
begin
// 2.
  try
    EditEPS.Text := QueryPortfolio.FieldByName('EPS').AsString;
    EditDPS.Text := QueryPortfolio.FieldByName('DPS').AsString;
//..........................................
  finally
    CanPost := True;
  end;
end;

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;

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

4. See //1. and // 2.

I hope it is helpfull,
esoftbg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nzbossAuthor Commented:
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.
0
esoftbgCommented:
> "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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

nzbossAuthor Commented:
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.
0
esoftbgCommented:
Excuse me I did not say about the link:
You need to link QueryPortfolio.UpdateObject := UpdateSQLPortfolio (in Object Inspector).
0
nzbossAuthor Commented:
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.
0
esoftbgCommented:
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;
0
esoftbgCommented:
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;
0
nzbossAuthor Commented:
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.  :-)
0
esoftbgCommented:
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 !
0
nzbossAuthor Commented:
Thanks
0
nzbossAuthor Commented:
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!  :-)
0
esoftbgCommented:
Thanks,
I will keep un eye out.
esoftbg :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.