999 Error Only the cancel command is allowed when rows are pending

[Former Member]

I have started experiencing a really strange issue lately. Sometimes when I retrieve into a tabular or grid datawindow and I have another window open I get this error. I optimized the pbls and did a full rebuild but it didn't resolve it.

However, I found that if I get the row count in the datawindow constructor event and set the height to that count * the detail row height it retrieves correctly. I already know for a certainty that no retrieval is taking place. This is PB Classic 12.5.2 build 5602 running on windows 7 Enterprise Service Pack 1.

Has anyone else experienced this behavior or know if this is a known bug? What makes this really interesting is that we haven't updated PB in years. This is a legacy system that was been around for about 12-13 years and no changes have been made that would cause this to occur.

Any help/ideas would be greatly appreciated.

[Former Member · Accepted solution]

I searched in Google for "only the cancel command is allowed" and there are hits going back to at least 2004 so this isn't a new issue. Unfortunately I didn't see any really good answers.

You should turn on database tracing so you can see what exactly the database is complaining about.

[Former Member]

Hey guys,

Thanks for the reponses and ideas.

RetrieveAsNeeded is not set and there is no code at all in the retrieverow event. Again, what's really strange is if I set the datawindow control to a height large enough to hold the entire result set it works fine.

I know for a fact there are no rows pending. I can open the app in the morning and retrieve one row, and then come back to it hours later, open the new window while the window with one row is still open and the error occurs.

[Former Member]

The database must still have something up it's sleeve.  You may want to consider issuing an explicit COMMIT after each retrieval.

[Former Member]

Turn on database trace to see the exact sql statement causing the error.

Is there a computed field on the DataWindow that calls a global function with SQL code in it? Or it could be a dropdown DataWindow.

[Former Member]

Thanks again for replying, but I think you're both missing something here. This seems to be directly related to the height of the datawindow control. If I set it to be large enough to hold the entire result set then the retrieval completes successfully.

There are no computed fields and no dddw's. The SQL is really simple:

SELECT releasing_company_id,

     releasing_company

FROM releasing_company

This has been in production for years with no problems until the last build. We're using user objects inherited from datawindow in our framework. Maybe one of them is corrupted. I'll try rebuilding them and see what happens.

Thanks again for your suggestions, they're greatly appreciated.

[Former Member]

Hi Jack;

   (always wanted to say that on an airplane to see what would happen) 

  I agree ... the workaround you have seems to point to a corruption somewhere in the DW Control and/or DW object and then its interaction with the DML statement being processed. I would go back to the last application version before the changes you made prior where this was not a problem Then have a look at what was changed from that point-in-time.

  Suggestion: Put some code like a MessageBox ( ) comand in the DC's RetrieveEnd event and see if it gets triggered before you modify the DWO's height and then after you add that code.

  Tip: Set your SQLCA.DBMS = "TRACE xxxx" and see what's going one SQL Client wise during this issue.

Good luck!

Regards ... Chris

PS: Could be a child DW object firing as well. Check your Child DW's and/or DDDW's as well. 

[Former Member]

Do you have embedded SQL in a resize event then?

[Former Member]

"Hi Jack;

   (always wanted to say that on an airplane to see what would happen) "  

Morning Chris.... you are obviously have a good week

[Former Member]

Just "FairDinkum" - Ta mate! 

I hope that your having a cracking one too!

[Former Member]

I think Lars has the right idea, you must have a SQL statement in the retrieverow event. The database won't allow you to run a SQL command while the first one is still in progress.

[Former Member]

Do you have retrieveasneeded set?

Do you have SQL in the retrieverow event?

(Assume you get the rowcount from RowCount() not select count(*)...)