Solved

Urgent Help Easy Database Searching

Posted on 1998-11-01
35
185 Views
Last Modified: 2010-04-06
This one should be easy but I seem to be suffering from brain fade so I've given it LOADS O' POINTS because I need a quick answer.

I've got a TQuery that selects records for a report based on a boolean field called update being true.

This query is called from TQReport to generate the report.

Now all I want to do is change the boolean field to false in the underlying TTable components after the report has been printed.

I know the logic behind it ie. go through the Query records one by one, and for each instance edit the Table record that has a matching key.

But how ? All these LOCATE, GOTOKEY, EDITKEY syntaxes are just confusing me and sending me around in circles.

Please Please help

I can up the points a bit more if need be

Thanx in advance

Darren
0
Comment
Question by:elkiors
  • 9
  • 7
  • 7
  • +3
35 Comments
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345249
Hi elkiors,

why not traversing the Table itself?

table1.First
while not table1.EOF do begin
 if table1.FieldByName('selected').AsBoolean then
  FieldByName('Selected').AsBoolean := false
 table1.next;
end;

Even better, just traverse the query :

query1.first;
While not query1.EOF do begin
 query1.FieldByName('selected').AsBoolean := false;
 query1.next;
end;

offcourse for quicker results, better disablecontrols, etc.

Regards, Zif.
0
 
LVL 5

Expert Comment

by:inter
ID: 1345250
Hi Zif and all,
Here is a solution with SQL (Backup you database before test please)
say you table name is MYTABLE
and the field you set to false is MYFIELD so you should execute the following SQL script

update MYTABLE
   set MYFIELD = FALSE
   where MYTABLE.MYFIELD = TRUE

here for any comments,
igor
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345251
hi Inter, yep that's a solution too. Great, that mostly of the time several solutions are posible.

ps. elkiores, you can use LOCATE etc, as you propose, but I think that if you use these functions, the transforming will not be so quick as inter and I propose.
0
 
LVL 5

Expert Comment

by:inter
ID: 1345252
Anyway I suggest you to turn Cachedupdates on and apply Zifs second method...
0
 

Author Comment

by:elkiors
ID: 1345253
Hi guys thanx for coming back so quick. Your answer looks good zif but .... I've already got the records filtered in my initial select query, all I want to do is drop back to the table that has the update field flag set, and reset it to false. Surely this is quicker than going through all the initial table for the sake of a few records?

Maybe I'm not explaining myself too well (my fault not yours)

I have a table (let's call it table1)
It has a boolean field named update
I have a select query (query1) that pulls all records that have table1.fieldbyname('update')=true
After my report is finished i need to set the table1.fieldvalues('update'):=false
I can get the reduced set of record key values from Query1

In a nutshell

flag a few records for report
generate report
un flag the records


Hope this helps you to help me

Darren

0
 

Author Comment

by:elkiors
ID: 1345254
This is what I've got so far but it just isn't working

procedure TrepUpdate.qrLetterAfterPrint(Sender: TObject);
var
  Loop:Integer;
begin
  With DMod.qLetter Do
  begin
    open;
    First;
    For Loop:=0 to RecordCount do
    begin
      with DMod.tContacts do
      begin
        DMod.tContacts.Locate('ContactID',DMod.qLetter['ContactID'],[]);
        Edit;
        FieldValues['Update']:=False;
        Post;
        end;
      next;
    end;
  end;
end;

Please help

The points are now 350
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345255
So you mean you have two tables, all have contactID, and you can tell which record you want to change by the ContactID from other table?
0
 

Author Comment

by:elkiors
ID: 1345256
Dwangg:

That's kind of it. Removing the unnecessary stuff. tContacts has a ContactID (autoincrementing) field and an Update (boolean) field.

the Query qLetter selects all records from tContacts where Update=True including the ContactID for the quick report (which is basically a mail-shot letter)

all I want to do is, after the report has been printed, reset the update field in tcontacts to false. I just thought it'd be quicker basing the search on the ContactID which is already saved in the query since that has already filtered which records I need to modify in tContacts

Clear as mud

Thanx

Darren


0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345257
autoincrementing? So you are using server side database, aren't you?

There are several method to implement this, I just suggest one here: (many copied from your code above ^_^)

valList:string;

valList:='(';

With DMod.qLetter Do
     begin
     open;
     First;
     while not qletter.EOF do
           begin
          valList:=''''+FieldByName('CaontactID').asString+','+'''';
                    //suppose it is a string field.
         end;
         delete(valList,length(valList),1);
         valList:=valList+')';
    end;

then run a SQL statements:

'update qCantact set update=False where contactID in'+valList;


This may be strange, but the main idea is to update the qCantact Table whose cantactID is in the qLetter query.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345258
I still don't understand (guess I'm stupid). If you have a query which contains all the records with update to true. And you want to reset them back to false. Then just travers your query and set all these fields to false... butwise if you only filter, why not using a table with a filter, instead of a query. Queries are only usefull if you want to combine tables.
Zif.
0
 

Author Comment

by:elkiors
ID: 1345259
Now I'm really confused.

The autoincrementing is just a standard Paradox +field of type longint. It's just something to give me a unique key value for a record.

I'm not running anything on a server. It's just a little standalone app for mailing standard letters to different people at different times.

I'm really worried about the
delete(valList,length(valList),1);
bit, surely I shouldn't be deleting any records.

The closest I've got so far is a mod of Zifs original proposal, but even that doesn't work since the tContact table is a detail in a master/detail relationship and so the bde is limiting the update to records in a specific range.

I thought when I first posted this that it'd be a sinsh for someone .. maybe I ought to up the points further

Darren
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345260
Well, I think the main point is that when elkiors says "filter", he does not mean the filter property of a table/query, just means a conditional query.

So inter's answer should be almost OK but one thing: if the original table is changed during the printing, his SQL statement could have updated those records that are not printed yet.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1345261
hi elkiors,

why you don't use inters SQL-Statement  :

in DelphiCode:

Query1.Close;
Query1.SQL.Text := 'update MYTABLE set MYFIELD = FALSE where MYTABLE.MYFIELD = TRUE '
try
  Query1.ExecSQL;
except
  {an Error}
end;

meikl
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345262
No matter what kind of database you are using, the answer I posted should work, the delete method is a string function that delete a character from a string. I should write system.delete rather than delete since it's within the scope of a query.


0
 

Author Comment

by:elkiors
ID: 1345263
Hi all,

I've managed to solve it but I think it's a bit inefficient. After all the effort you guys have put in I feel I should still keep the points available, maybe you can give me your democratic vote on who should have the points, or maybe we can have a share out.

One thing I am sure of is that I like INTER's solution (backed up by KRETZSHMAR's interpretation although I have no idea how to use update queries they seem like they are something I should know more about so I haven't tried it yet because I'm not sure how to do the full implementation. So, can anyone post a full piece of SQL code that does what the code below accomplishes and maybe give me some insights into basic update query syntax. I'd got confused before about these things because I'd used Database Desktop QBE to do my original select query and then just pasted the code, but when it came to doing a changeto query QBE told me I wasn't allowed to change fields I was selecting so I'd left it alone.

Zif: you were kind of right too, although I didn't need to change the contents of the query, similarly, you were right about using the query for doing a join on tables. I'd only given you the relevent bits of what I needed, but in reality my original select query dragged in stuff from 5 different tables.

Dwwang: Sorry but your code was a bit too low level for me to understand so I kind of didn't trust it. Sorry.

  With DMod.tContacts Do
  begin
    disableControls;
    IndexName:='';
    MasterFields:='';
  end;
  With DMod.qLetter Do
  begin
    open;
    First;
    while not eof do
    begin
      with DMod.tContacts do
      begin
        Locate('ContactID',DMod.qLetter['ContactID'],[]);
        Edit;
        FieldValues['Update']:=False;
      end;
      next;
    end;
  end;
  With DMod.tContacts Do
  begin
    IndexName:='CompanyID';
    MasterFields:='CompanyID';
    EnableControls;
  end;

Major thanks to you all

Darren

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345264
elkiors, correct you don't need to change the query, but changing the query effects the underlying table. So changing a field to true, changes that table field to true. Since you got all the records which need to be changed in a query, you can just change them in this query. All changes will be updated to the table. Zif.
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345265
Zif is right, you can just update the query directly, by setting its requetlive to true, and this way is most safe one although not so efficient.

If you want to use inter's answer, you can just use it, only thing is that your application should not be in a real multi-user environment
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Expert Comment

by:Stefaan
ID: 1345266
The best answer IMO has been proposed several times, but has never been considered.

For such a case you should use an Update query.  If I understand it, you selected all fields from a certain table which have a flag set to true.  In SQL this should look like :

"SELECT * FROM <TABLENAME> WHERE FLAG = TRUE".  

To update all the fields you just selected with your SQL Statement, you simply need to write an SQL Update statement like :

UPDATE <TABLENAME> SET FLAG = FALSE WHERE FLAG=TRUE"

This SQL Statement will select all records which have the flag set to true and will modify those records, so that the flag is set to False.

In my opinion this is the best, fastest and also easiest way to handle your problem.

Greetings and best regards,


Stefaan Lesage
0
 

Author Comment

by:elkiors
ID: 1345267
Hi Stefaan, nice of you to join us.

You're answers okay in theory, but as I said earlier, I've never had anything to do with update queries before so I'm not entirely sure how to go about using them.

Please clarify a few things for me to earn the points.

Do I use the Update Query Component or can I use a normal tQuery to perform this task ?

Do I have to reset indexes like I've done in the solution I've used to bypass the master/detail relationship ?

Can you give me a brief lesson in basic update sql syntax maybe as a couple of examples? Like I said earlier, the SQL I've used upto now has just been pasted directly from the Query By Example stuff, and even then, only straight select and joins.

Thanx in advance

Darren

0
 

Author Comment

by:elkiors
ID: 1345268
Hi again.

I'm sorry to keep pestering but I thought I might be able to suss out this update query stuff myself. The best I could do however was to keep on getting Invalid Token messages.

This is what I've got, now if anyone can give me a syntactically correct SQL string it would be greatly appreciated.

a Datamodule. DMod
a Datasource. dsContacts (connected to tContacts)
a Table.      tContacts (connected to Contacts.DB)
the field I wish to update is called Update

I've got this far please put in the quotes and stuff in the right places.

UPDATE tContacts
SET Update=FALSE
WHERE tContacts.Update=TRUE

Cheers
Darren

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345269
your string should be correct.
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345270
Still, the simple update SQL could dangerous if the are other users who update the tContacts table while you are printing.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1345271
hi elkiors,

it could be done in a normal TQuery-Component

meikl
0
 
LVL 3

Accepted Solution

by:
Stefaan earned 500 total points
ID: 1345272
Hi again Elkiors,

Well, it is logic that you ask for an explanation on the use of a TQuery component, since you never used one.  Well I'll try to give you a sample.  Since the update SQL Statement can be executed using a normal TQuery, I'll tell you how you should do it :

First you place the TQuery on your form, then you go to the Object inspector and set the SQL property to :

UPDATE tContacts SET tContacts.Update=FALSE WHERE tContacts.Update=TRUE

This is basically an SQL Statement which selects all the contacts records where the Update field is TRUE and modifies them so that the Update field is false.

Now you can place this piece of code in the OnClick event of your button, or somewhere else (assuming the TQuery is called qryUpdateContacts ).

qryUpdateContacts.Close;
qryUpdateContacts.ExecSQL;

This will basically close the Query and execute it's SQL Statement.  You could also set the SQL property at runtime, then the code would look like this (assuming the TQuery is called qryUpdateContacts ) :

qryUpdateContacts.Close;
qryUpdateContacts.SQL.Clear;
qryUpdateContacts.SQL.Add('UPDATE tContacts SET tContacts.Update=FALSE WHERE tContacts.Update=TRUE
');
qryUpdateContacts.ExecSQL;

This basically closes the query, clears its current SQL Statement, sets the SQL Statement and executes the query.

This should be all you need.

If you need more information, you can always contact me.

Greetings and best regards,



Stefaan Lesage

E-Mail : Stefaan.Lesage@NOSPAMeca.be (remove NOSPAM to E-mail me)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1345273
hi stefaan,

see my and inters comment (its just the same)

meikl
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345274
elkiors, what do you need to know, because all give here solutions? I thought you already had one on your own?
0
 

Author Comment

by:elkiors
ID: 1345275
I must be getting really annoying to you people, so I've upped the points as far as I can go (it leaves me with 13) thanks for the answer stephaan. That's what I needed, some explanation of update SQL syntax ... The only thing is, it does'nt work when I try to set the active property to true.

If I use this:

UPDATE tContacts
SET tContacts.Update = False
WHERE tContacts.Update = True

I get Invalid use of keyword Update at line 2

If I use this:

UPDATE tContacts
SET tContacts."Update" = False
WHERE tContacts."Update" = True

I get table tContacts doesn't exist. (Remember that's the name of the TTable Component pointing to Contacts.DB).

If I use this :

UPDATE Contacts
SET Contacts."Update" = False
WHERE Contacts."Update" = True

I get 'Error creating cursor handle'.
I get this message whether RequestLive is true or false.

I've got all tables and datasources active at design time.

Please help some more.

Zif: I know I already have a working solution using the locate etc. but I think the SQL way that you are all proposing is a more elegant, less code intensive, way to go. Especially since I am using this project as a learning mechanism before moving on to tackling multi-user stuff on our LAN at work. When it comes to doing that, then I think SQL will be an integral mechanism for solving most of the data processing, even though everything will be running using Paradox tables and Delphi 3 Standard edition apps.

Looking forward to that definitive answer

Darren

Stephan, you're well on your way to claiming the points by you taking the time out explaining things for me.

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1345276
hi elkiors,

first in the sql-statement use the physical name of the table

hi stephaan

now it is on yours to solve elkiors problem

bye meikl
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1345277
hi elkiors,

i forget a tip

don't open the query with the update-sqlstatement

use the method
query1.execsql
because there is no resultset

meikl
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1345278
The answer is, when you are not selecting data from tables, you should never use query.open or query.active:=true, or you will get error creating cursor handle.

You only need to run query.execSQL.
0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1345279
Hi Again Elkior,

As I told before (see sample code) and as kretzschmar said, you shouldn't set the active property to true for this TQuery.  The active property for a TQuery sould only be set to true when the TQuery returns a Resultset (a set of records).

In our case the SQL Statements only execute something but they do not return any records back to the user.  The Active property should be left to false.  This is also the case for the TQuery.Open method.  The TQuery.Open method should only be called when the TQuery returns a ResultSet (Set of records) as in our case the TQuery doesn't return any records, we can't use the .Open method, therefor we use the .ExecSQL method.

So if you simply set the Database property and the SQL property at design time and use the code I supplied earlier it should work perfectly.

In summary :

* A TQuery returning records can be called by setting the active property to True at design time or by calling the TQuery.Open method

* A TQuery which is executing something without returning any records to the user should be called by calling the TQuery.ExecSQL property.  Since it doesn't return any records, you can't set the Active property to True at design time.


Hoping that this will be of any help to you, I greet you sincerly.


Stefaan Lesage
0
 

Author Comment

by:elkiors
ID: 1345280
That's It. Yippee.
I didn't realise before that the active property could'nt be used, but it worked without a hitch at runtime.

I'm greatly indebted to you all.

I feel a little guilty about passing all the points on to Stephaan since you all gave me so much input, but in the end, it was Stephaan who realised that I really needed my hand holding so to speak. Now that I have working examples of both Select and Update queries, I should be able to work out the other stuff myself.

I suppose at the end of the day, the points don't really matter, but if Stefaan feels he has stolen some points maybe from Dwwang, Inter, Zif and everyone else then maybe we can still organise that share out. Anyway, from my point of view, my conscience is clear since I've given all my points away to keep you guys interested. But it was worth it.

I'm just sorry that I couldn't accept anyone elses SQL answers before, but as you can see from my last ditched attempt, I just couldn't get the syntax quite right.

See you all later.

Darren

Thanks again Stefaan. Don't be a stranger.

0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1345281
I won't be a stranger.  If I can help you a little more with something else, feel free to contact me at : Stefaan.Lesage@NOSPAMeca.be (remove NOSPAM)
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1345282
Hi all,

 Don't bother about the points,... and correct Stefaan gave you a well explained answer... besides the points stay in Belgium... I like that :-) Just kidding ;-).

Stefaan, in welk bedrijf werk jij? Gebruiken ze Delphi?

Zif.
0
 
LVL 3

Expert Comment

by:Stefaan
ID: 1345283
Hoi ZifNab,

Ik werk sinds kort bij ECA in Assenede, en sinds mijn aankomst hebben ze Delphi aangeschaft, en nu ben ik (samen met 2 collegas die ik moet opleiden) al druk bezig aan het ontwikkelen.

Als je wil kan je mij hier gerust een mailke sturen ook : Stefaan.Lesage@NOSPAMeca.be (gewoon de NOSPAM wegdoen)

Tot nog es.


Stefaan Lesage
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now