Solved

Strange error when looping thru recordset

Posted on 2004-09-15
11
624 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
  • 6
  • 3
  • 2
11 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
Comment Utility
"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
Comment Utility
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
Comment Utility
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
 
LVL 12

Author Comment

by:roverm
Comment Utility
@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
Comment Utility
edit: that should be:


I know that ExecSQL should NOT be nessacary ....
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:Jacco
Comment Utility
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
Comment Utility
Nope, sql statement is not empty... too bad :)
0
 
LVL 10

Expert Comment

by:Jacco
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

13 Experts available now in Live!

Get 1:1 Help Now