?
Solved

Accessing data from an Access DB

Posted on 2003-03-24
11
Medium Priority
?
193 Views
Last Modified: 2010-04-04
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
Comment
Question by:begonz
[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
  • 5
  • 5
11 Comments
 

Author Comment

by:begonz
ID: 8194003
Sorry...table is a little messy.
0
 

Expert Comment

by:chris51179
ID: 8194035
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
 

Expert Comment

by:chris51179
ID: 8194039
TADOquery and TADOConnection are much better than BDE by the way.
0
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.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 8194092
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
 

Author Comment

by:begonz
ID: 8198008
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
 

Expert Comment

by:chris51179
ID: 8201982
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
 

Expert Comment

by:chris51179
ID: 8201992
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
 

Author Comment

by:begonz
ID: 8206603
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
 

Accepted Solution

by:
chris51179 earned 75 total points
ID: 8208852
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
 

Author Comment

by:begonz
ID: 8214940
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
 

Author Comment

by:begonz
ID: 8233202
Thanks I reworked the update query and it works.  Thanks.
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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 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