Accessing data from an Access DB

Help.

I have a simple form with 1 editbox for input and 5 editboxs for outputs.  

What I want to do is be able to input a CountryFrom code eg AUD and it will return all the conversion rates.  See table below.  

ConID     CountryFrom     AUD          NZD     DEM          FRF     USD
1     AUD             1          1.321898     1.24422          4.172943     0.574098
2     NZD             0.756486     1             0.941238     3.156774     0.434299
3     DEM             0.803716     1.062429     1          3.351981     0.604778
4     FRF             0.229639     0.316778     0.298331     1             0.180424
5     USD             1.841856     2.302555     1.6535          7.5425     1

I have a Database connection using ODBC and BDE Alias.  On my form I have a TDatabase component and a TTable component.  Both these are pointing to the DB.

Please help, I can't seem to find any decent info on the net to help me do this.  I know how to do the SQL statement in Access but I am not sure how to do it in Delphi - (SELECT CountryFrom, AUD, NZD, DEM, FRF, USD FROM CurrencyTable WHERE CountryFrom = 'NZD';).  

Do I need to create a connection and close?  How do I store each value to a variable?

If you require more info from me please let me know.

Thanks heaps in advance.
begonzAsked:
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.

begonzAuthor Commented:
Sorry...table is a little messy.
0
chris51179Commented:
query.sql.add('SELECT CountryFrom, AUD, NZD, DEM, FRF, USD FROM CurrencyTable WHERE CountryFrom = ''NZD'';');
try
  connection.begintrans;
  query.open;
  connection.committrans;
//do your stuff with the query here//
//edt1.text:=query.fieldbyname('countryFrom'); etc//
except
  connection.rollbacktrans;
  raise;
end;
0
chris51179Commented:
TADOquery and TADOConnection are much better than BDE by the way.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

kretzschmarCommented:
create a crosstab-query in access,
which results in a sheet like above

just guessing that you have a table like

FromCountry  ToCountry  Faktor

use a storedProc to open the crosstab-query

meikl ;-)
0
begonzAuthor Commented:
Sorry guys, new to all of this.  I still can't seem to get working chris51179.  Do you think you could maybe provide more info?  Where you have 'Connection.Begintrans', connection is not recognised.

Also, what are you talking about TADOquery and TADOConnection?  Is this easier?  Do I need to set up a DB in ODBC or can I just specify it in the ADOConnection component?

Am I still able to build an exe in Installshield and install it on other PCs?
0
chris51179Commented:
you can use a component called TADOConnection (under the ADO tab) this allows you to connect to a database (for an access DB double click the component on your form, click build, select 'microsoft jet 4.0' as you provider and browse for your database under the connection tab). when you have you connection set up you can link a TADOQuery component to it (also under the ADO tab). change its connection property to your TADOConnection component in the object inspector.

You can then use the code above where 'connection' is the name of your TADOConnection component and 'query' is the name of your TADOQuery component. e.g. if you call your TADOConnection myConnection then myConnection.beginTrans will begin a transaction. You don't have to use transactions but they are good practice, if you choose not to the simply access your query directly like this.

for example you call your TADOQuery component 'myQuery'

myQuery.SQL.clear;
myQuery.sql.add('SELECT CountryFrom, AUD, NZD, DEM, FRF, USD FROM CurrencyTable WHERE CountryFrom = ''NZD'';');
try
  myQuery.open
  edt1.text:=myQuery.fieldByName('AUD').asString;
  edt2.text:=myQuery.fieldByName('DEM').asString;
except
  showMessage('There was a problem');
  raise;
end;

here endeth the lesson
hope it helps




0
chris51179Commented:
you can use this method for .exe and distribution provided your database is in the same reletive place is it when you designed it. i.e. if it is in the same folder as the application when you design it and distribute it then you will be ok.
0
begonzAuthor Commented:
Chris this seems to work, I can now display data, only ONCE though and then it says it "cannot perform the operation on an open dataset".  I have tried clearing with no luck  :-(  Any ideas?

Also, if I change a field how can I update that field in the database?  It would be an OnChange event for the editbox but how would I code it?

Thanks, you are a star!
0
chris51179Commented:
when you have the query open you can access information using the fieldByName function i.e.

edt2.text:=myQuery.fieldByName('FRF').asString

however if you wish to use a new SQL command you must close the query, you should do this after your "try/Except" statement.

try
 myQuery.open
 edt1.text:=myQuery.fieldByName('AUD').asString;
 edt2.text:=myQuery.fieldByName('DEM').asString;
except
 showMessage('There was a problem');
 raise;
end;
myQuery.close;

you can then use other SQL statements. in order to update the Database, again you need SQL, and as an UPDATE returns nothing we have to use myQuery.execSQL instead of myQuery.open.

procedure updateDB;
begin
  myQuery.SQL.clear;
  myQuery.sql.add(format('UPDATE CurrencyTable(AUD, NZD,   DEM, FRF,  USD)  VALUES(%s,%s,%s,%s,%s) FROM WHERE CountryFrom = ''NZD'';', [edt1.text, edt2.text, edt3.text, edt4.text, edt5.text]));
  try
    try
      myQuery.execSQL;
      showMessage('database updated');
    except
      showMessage('There was a problem');
      raise;
    end;
  finally
    myQuery.close;
  end;
end;

you may have to sort that SQL statement out, i cant remember it properly. you can also use parameters but i cant remember the exact syntax for an TADOQuery (it is different to a TDBQuery). Im sure there are threads on this forum for how to use parameters in a TADOQuery, they will explain it more thoroughly.

good luck
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
begonzAuthor Commented:
Thanks chris, I can now do a number of search when opening and closing the query.  Thanks.

As for update, I'm having a few issues with the update statement.  I'll keep working on it.
0
begonzAuthor Commented:
Thanks I reworked the update query and it works.  Thanks.
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.