Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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.
0
begonz
Asked:
begonz
  • 5
  • 5
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now