?
Solved

Database access

Posted on 2003-03-25
13
Medium Priority
?
155 Views
Last Modified: 2010-04-04
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.
0
Comment
Question by:nzboss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 12

Accepted Solution

by:
esoftbg earned 300 total points
ID: 8201674
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
 

Author Comment

by:nzboss
ID: 8205950
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8208461
> "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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nzboss
ID: 8209196
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
 
LVL 12

Expert Comment

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

Author Comment

by:nzboss
ID: 8214974
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8216014
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8223219
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
 

Author Comment

by:nzboss
ID: 8229085
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8229598
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
 

Author Comment

by:nzboss
ID: 8230199
Thanks
0
 

Author Comment

by:nzboss
ID: 8230201
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 8233046
Thanks,
I will keep un eye out.
esoftbg :-)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question