asked on

Strange error when looping thru recordset

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 (https://www.experts-exchange.com/questions/20304916/Insufficient-memory-For-This-Operation.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

Ivanov_G

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

Ivanov_G

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

ASKER CERTIFIED SOLUTION

Jacco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

roverm

ASKER

@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

roverm

ASKER

edit: that should be:

I know that ExecSQL should NOT be nessacary ....

Jacco

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

roverm

ASKER

Nope, sql statement is not empty... too bad :)

Jacco

Does the error state exactly: "No current SQL Statement"?

What database do you use?

Is there a BLOB involved?

Regards Jacco

roverm

ASKER

It's a paradox db, no blob involved.
I will give the exact error shortly (don't have the code here now).

roverm

ASKER

Yeah! It was the ExexSQL after all!
You guys put me on the right track!

Thanks!
Problem solved!

D'Mzz!
RoverM

roverm

ASKER

@Ivanov_G:
Please get your points here:
https://www.experts-exchange.com/questions/21133177/Points-for-Ivanov-G.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