Solved

Strange error when looping thru recordset

Posted on 2004-09-15
11
635 Views
Last Modified: 2010-04-05
Hi all,

I have a strange problem.
I am using a TQuery to retrieve a recordset (1772 records).
I need to loop thru this recordset and retrieve values from other tables using the recordset-values.
For this purpose I use a second TQuery.

So, basicly:

qry1 = first recordset
qry2 = sub query

while not qry1.eof
begin
  //do some work, store some field in a string
  //then based on the value from field 'X' retrieve other values
  qry2.Close();
  qry2.SQL.Clear();
  qry2.SQL.Append('the sql statement');
  qry2.ExecSQL;
  qry2.Open()
  if qry2.RecordCount=1 then
  begin
     //add some values to my big string
  end;
  qry2.Close();
  //write the big string to a textfile
  qry1.next;
end;

This didn't work at first because the BDE reported "Insufficient memory". But using this q (http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20304916.html?query=insufficient+memory&topics=85), that problem is solved.
Now another error occurs, but without any good debug information. The error is "access violation at...".

The strange thing is that when I read the first 114 records and run the loop, it works fine.
But any number above, it fails.

Can anybody help me?
Am I doing this wrong?

D'Mzz!
RoverM

0
Comment
Question by:roverm
[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
  • 6
  • 3
  • 2
11 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072221
"I need to loop thru this recordset and retrieve values from other tables using the recordset-values"

Why don't use SQL join between these 2 tables. For e.g. :

SELECT PRODUCT.NAME, SALES.PRICE
FROM PRODUCT, SALES
WHERE PRODUCT.PRODUCT_ID = SALES.PRODUCT_ID

This way you won't need Query2...
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072235
Now I noticed something else:

  qry2.ExecSQL;
  qry2.Open()

You use both of them which is not needed ... If you have a SQL, which returns a dataset - use OPEN, for something like INSERT, UPDATE or DELETE use ExecSQL...
0
 
LVL 10

Accepted Solution

by:
Jacco earned 500 total points
ID: 12072238
Code looks fine.

Just one thing I don't understand is why you do an ExecSQL followed by an Open. I personally use ExecSQL only with Update/Insert queries but I don't Open these. I only use Open when SQL contains a select query.

What you seem to do is check if how many records the ExecSQL affected.

You can use the RowsAffected property for that.

Regards Jacco
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:roverm
ID: 12072282
@ivanov_g:
I can't use a join because the selection comes from different tables. It's not just 2 tables...

@both:
I know that ExecSQL should be nessacary but for some strange reason, when I remove that line the code doesn't work at all! "No current SQL Statement"

If I remove the code for the subquery, that only the main loop is running, then everything works fine.

Thanks so far!

D'Mzz!
RoverM
0
 
LVL 12

Author Comment

by:roverm
ID: 12072295
edit: that should be:


I know that ExecSQL should NOT be nessacary ....
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12072350
Maybe this error: 'No SQL statement available'

It is in the SEmptySQLStatement constant. The error is raise when

Length(SQL.Text) < 1

or

SQL.Count = 0

Maybe you can check this. It seems that qry2.SQL.Append('the sql statement') does not always have text?

Regards Jacco
0
 
LVL 12

Author Comment

by:roverm
ID: 12072374
Nope, sql statement is not empty... too bad :)
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12072385
Does the error state exactly: "No current SQL Statement"?

What database do you use?

Is there a BLOB involved?

Regards Jacco
0
 
LVL 12

Author Comment

by:roverm
ID: 12072465
It's a paradox db, no blob involved.
I will give the exact error shortly (don't have the code here now).
0
 
LVL 12

Author Comment

by:roverm
ID: 12072517
Yeah! It was the ExexSQL after all!
You guys put me on the right track!

Thanks!
Problem solved!

D'Mzz!
RoverM
0
 
LVL 12

Author Comment

by:roverm
ID: 12072524
@Ivanov_G:
Please get your points here:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21133177.html

I think it's only fair (and well deserved) to give you both 500 points and an A-grade.

Thank you both!

D'Mzz!
RoverM
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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