Board index » delphi » How to do an Update query or a Delete Query or an Insert Query using D3

How to do an Update query or a Delete Query or an Insert Query using D3

I need some coaching please. (Bill Todd - Team B if you are listening, I
am all ears). I seem to have most of the pieces to call an update or a
modify query on my Paradox database using Delphi 3, but I still get
errors. I know I am missing some minor step the HELP files do not
mention. Please, if someone could, describe to me like I am 4 years old,
how you create an UPDATE SQL call in Delphi?

some background: I have seen where the TQuery has an UpdateObject
property which I can set to be some TUPDateSQL object. I see that the
TUpdateSQL object has three string properties, for delete, insert and
modify. From there I get lost in various blunders and miscuses resulting
in various error messages. I know this has GOT to be simple.

generally: is there a good book with working exmaples for doing this
kind of thing in Paradox using Delphi? I can't find it, so far. Does
Team B have a book of handy tips?

Jeannine Menger
PC Synergy
760-931-0350

 

Re:How to do an Update query or a Delete Query or an Insert Query using D3


Jeannine,
   Sorry, I'm not Bill, but nevertheless I'll try to give you some hints.
First, the use of TUPDateSQL is a relatively advanced method, that you
should use when dealing with tables being cached (using the Cached Updates
property of a table or query). Just like you said, there HAS to be an easier
way of doing this, right? Well, you guessed right.
  The easier way of doing this is using the SQL property of a query. Put the
query that you have in mind, just like any Select query. You can pass
paramethers the usual way, using the ParamsByName method. One example:
 "Update <table> set <field> = <something> [where ...]"
So, to update the customers table you could write something like this:
   Update customers set custno = :newcustno
This query will set custno field from ALL customers (since you are not using
a where clause) to newcustno.
  Now comes the call part (supposing your query is named query1):
    query1.Prepare; (first of all, prepare the query)
    query1.ParamByName('newcustno').AsFloat := (something);  (sets the
parameter)
    query1.ExecSql;   (executes the query!)
 Please mind that you do not have to necessarilly give a paramether to use
this type of query (you can pass 2, 3,... or none). I only did this to
explain and to give you the power of this type of query. It's really simple,
isn't it?

Hope this can help you (and please excuse my english...)
   Alexandre

Quote
Jeannine Menger wrote:
> I need some coaching please. (Bill Todd - Team B if you are listening, I
> am all ears). I seem to have most of the pieces to call an update or a
> modify query on my Paradox database using Delphi 3, but I still get
> errors. I know I am missing some minor step the HELP files do not
> mention. Please, if someone could, describe to me like I am 4 years old,
> how you create an UPDATE SQL call in Delphi?

> some background: I have seen where the TQuery has an UpdateObject
> property which I can set to be some TUPDateSQL object. I see that the
> TUpdateSQL object has three string properties, for delete, insert and
> modify. From there I get lost in various blunders and miscuses resulting
> in various error messages. I know this has GOT to be simple.

> generally: is there a good book with working exmaples for doing this
> kind of thing in Paradox using Delphi? I can't find it, so far. Does
> Team B have a book of handy tips?

> Jeannine Menger
> PC Synergy
> 760-931-0350

Re:How to do an Update query or a Delete Query or an Insert Query using D3


Hi Jeannine,

The TQuery component can do update queries. Try the following steps ...

1.      Drop a TQuery component on your form.
2.      Set the DatabaseName property to 'DBDEMOS'
3.      Double click the SQL property to get the Strings editor
4.      Enter something along the lines of ...

        Update Customer
           Set TaxRate = 40.00
           Where Customer.Country = "US"

5.      Drop a button on the form and in its OnClick event code, enter ...

        Query1.ExecSQL;

Unless I've missed anything out (which is possible as it's quite late
here in the UK - way past my bedtime), the above steps should
demonstrate a simple Update query.

There is some help available in the Database desktop on-line help. From
the Contents tab, select

        SQL, Using Local SQL, Data Manipulation Language (DML), DML Examples,
DML Examples

The info is very limited, although I seem to remember that Steve
Koterski of Inprise (who regularly posts some excellent SQL information
in this newsgroup) said that an improved help file may be available for
download at some point. Perhaps if Steve reads this he could confirm
this.

It's worth mentioning that you can also make SQL calls entirely in code.
The previous steps could be done using the following code which also
introduces a way of passing parameters to the query.

Hope this helps,
Carl

-----------------------------------------------------------------------------------------------
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs,
  StdCtrls, DBTables;

//*** Need to add DBTables to uses clause ***

type
  TForm1 = class(TForm)
    Button1: TButton;
    Query1: TQuery;
    procedure Button1Click(Sender: TObject);
  private
    procedure UpdateTax(Country: String; NewRate: Double);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
  UpdateTax('US', 40.00);
end;

procedure TForm1.UpdateTax(Country: String; NewRate: Double);
begin
  with TQuery.Create(nil) do
    try
      DatabaseName := 'DBDemos';
      SQL.Add('Update Customer');
      SQL.Add(Format('  Set TaxRate = %5.2f', [NewRate]));
      SQL.Add(Format('  Where Customer.Country = "%s"', [Country]));
      ExecSQL;
    finally    
      Free;  // try/finally is used to ensure that TQuery is freed
whatever happens
    end;    
end;

end.

Quote
Jeannine Menger wrote:

> I need some coaching please. (Bill Todd - Team B if you are listening, I
> am all ears). I seem to have most of the pieces to call an update or a
> modify query on my Paradox database using Delphi 3, but I still get
> errors. I know I am missing some minor step the HELP files do not
> mention. Please, if someone could, describe to me like I am 4 years old,
> how you create an UPDATE SQL call in Delphi?

---8<---

Re:How to do an Update query or a Delete Query or an Insert Query using D3


Looks like you already have a couple of good answers<g>.

--
Bill Todd
(Sorry but TeamB cannot answer questions received via email)
(Remove nospam from my email address to contact me for any other reason)

Other Threads