?
Solved

Strange error when looping thru recordset

Posted on 2004-09-15
11
Medium Priority
?
639 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 2000 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
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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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