Updating cursor?

[erp84]

I have a form with a pageframe and two pages.

In the form load, I load up a cursor. The cursor is used on Page1 in a listbox. I have a command button to processes a transaction based on what record is selected in the listbox.

What would the best way to update the cursor be? I basically need to reprocess the select from into the same cursor name, after the command "Click" has processed.

I've tried several places, but it always errors out on me due to the listbox searching for the cursor.

 

[Mike Lewis]

Based on what you've told us, I would think the obvious place to regenerate the cursor is immediately after you have performed the transaction that you referred to in your second paragraph.

You would then need to call the listbox's Requery method, to tell it to re-bind to the cursor (I'm assuming the listbox has a RowSourceType of 2 or 6).

If that doesn't answer your question, please tell us: (i) what method you are using to populate the listbox; and (ii) what exactly you mean by " errors out on me due to the listbox searching for the cursor"?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[erp84]

Hi Mike,

I switch over the list box to use a SQL Query and I'm calling the Requery method after the transaction. Works perfectly!

I'm not really sure why I wanted to build the cursor on form load versus in the listbox in the first place...

Thank you Mike!

 

[Dan Freeman]

Glad you got that working, but lets make sure you understand why it failed.

When you "re run the query" to regenerate the cursor, you CLOSE THE ORIGINAL CURSOR. The listbox is bound to that cursor. The one you just closed. You've pulled the rug out from under the listbox!

One workaround would be to run your query into a differently named cursor, ZAP the original and APPEND FROM the differently named cursor (and then requery the listbox).

But using the query as the listbox's rowsource will work as well.

 

[Olaf Doschke]

>I'm not really sure why I wanted to build the cursor on form load versus in the listbox in the first place...

It's a widely used and accepted way to load data for all form controls before showing a form, so there are no quirks, especially with grids. As Dan said that also happens, when you query a cursor again and a good strategy is to keep the original cursor alive, Do a query into a secondary alias name, zap the original (which keeps it alive, just not it's data) and append new data.

Another elegant solution in that aspect is a view, which you can requery bby REQUERY("viewname") and that would work with all kinds of controls, too. But views in themselves are limted compared to how you can act on tables and cursors on top of a first query, eg do several queries for an end result, update, scan etc. the cursor. All that can of course also be done with a view cursor, but only the view cursor is backlinked to underlying tables and that limits you a bit.

I always think of rowsourcetype 3 or 4 being too tightly coupled to a DBF data backend. I never worked with .qpr files, which I believe are simply PRGs with the queries, the query designer created. I don't know, whether you can add code in there, eg making a remote connection, etc.

Here's a way you could work with controlspecific aliases and keep the update logic encapsulated, when designing your own control classes:
Add a property "rowsourcealias" and set that instead of rowsource at design time, to the usual alias you want to bind to. In Requery of eg the listbox you can then do

If This.RowsourceType = 2 And Used(This.rowsourcealias)
   Local lcRowsource
   
   lcRowsource = "crs_listbox_"+This.rowsourcealias
   If NOT Used(lcRowsource)
      * Initialisation phase, first time the control specific rowsource cursor is created:
      Select * From (This.rowsourcealias) Into Cursor (lcRowsource) Readwrite
      This.Rowsource = lcRowSource
   Else
      * Update phase, copy over data
      Set Safety Off
      Select (lcRowsource)
      Zap In (lcRowsource) 
      * ZAP would be sufficient after SELECT (lcRowsource), I just like to be really safe in what I ZAP
      * Eg think about adding a breakpoint, debugging, inspecting data, then press F8 and ZAP whatever you last viewed.
      Set Safety On
      Append From Dbf(This.rowsourcealias)
   EndIf
EndIf
Dodefault()

In the end you set the rowsourcealias to "yourtable" and the controls real rowsource will be "crs_listbox_yourtable".

You can keep all your data retrieval code as is, just do Control.Requery in the initialisation and every time, you want to display new data, after you queried it into a cursor or table named as usual. This will handle creating and maintaining a secondary cursor with a name prefix "crs_listbox_". You may come up with other prefixes to ensure it has no collision with any names you normally use for aliases.

Checking This.RowsourceType is 2 ensures, that this only has an effect in controls bound to aliases, not any controls driven by arrays or other sources, Used(This.rowsourcealias) makes sure the data to copy into the control specific cursor exists.

Bye, Olaf.

PS: There is a side effect on present code, of course, as the name of the cursor bound to the control really changes. Eg checking the active/selected record via alias.id, you now instead have to check crs_listbox_alias.id or Select (control.rowsource) and then read id (just the field name) or set lcAlias = control.rowsource and work with eval(lcAlias+".id"). So that change comes with a price.

Anyway, it's also a good practice generic code you write for controls does not include specific database/table names, but take them from the control properties or other meta/configuration data. It's another aspect of write once for reuse.

 

[TamarGranor]

When you "re run the query" to regenerate the cursor, you CLOSE THE ORIGINAL CURSOR. The listbox is bound to that cursor. The one you just closed. You've pulled the rug out from under the listbox!

Actually, listboxes don't care as long as the cursor exists when they're Requeried or accessed. I re-select for listboxes all the time. In fact, the way I handle requirements like this is to put the query in the Requery method, and call that method when the time comes.

Tamar

 

[Olaf Doschke]

Well, the listbox surely isn't as sensitive as the grid, but you often see the effect defered, eg when first activating a page with a listbox in such a state. No matter how seldom that is, you better also use the intermediate cursor. I just recently have such a case in inherited code (inherited from a retired developer, not oop inheritance and also not real inheritance). I'm not yet sure it's what I think, but if it is, I may give you a repro of what happens. If you have good results in requerying data in the requery method (which sounds plausible), than that may be an easier solution.

For erp84 the rewsourcetype sql select is a solution already, too. It's just ugly to me in some aspects, also not being able to nicely format a query readable. You can do that in a qpr, though.

Bye, Olaf.

 

[Dan Freeman]

Actually, listboxes don't care as long as the cursor exists when they're Requeried or accessed.

And that's exactly the condition that caused this thread.

I prefer not to create controls that have "as long as" requirements.

 

[erp84]

So far everything has been working fantastic calling a requery.

I think as my form progresses I'll begin do experiment with some of your suggestions Olaf, just to get a better understanding.