can I use text boxes to edit the results of a query? 1

[AlastairOz]

I am using a SQL select into a temporary cursor.
The results are then displayed in a grid.
This works fine, I can add records to the table and see the new record in the grid after TABLEUPDATE.
I set the record source of a text box to the cursor.fieldname and see the records changing in the text box when scrolling up or down the grid.
All I need to do now is be able to edit the current selected record in the grid.
something like:

Select record in cursor that = current record pointer position in cursor, update (or replace) fields with values from textboxes.

Can this be done?
If so I will have nailed my grid!

Thanks
Alastair

 

[Olaf Doschke]

If you set textbox.controlsource = "alias.fieldname" and you talk about an updatable view you're already done.

Changes made to the textbox are saved to the cursor, no INSERT/UPDATE SQL or REPLACE needed.

Bye, Olaf.

 

[cbasoz]

What is problem? Did you try setting controlsource property of your textboxes'? If I read you right you are saying you have a readwrite cursor and want to update it via textbox'on form. That is straight forward.

Record in cursor = current record pointer position in cursor. I can't see where the problem is.


Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Cetin, Alastair already said he's new t fopro in some other thread. We all once didn't knew about the controlsource.

Alastair: One thing you say makes me think what you're saying may not actually be what you're doing:

I can add records to the table and see the new record in the grid after TABLEUPDATE

If the grid is bound to a view, adding to the table and tableupdate won't show the record in the grid, only a requery. You'd normally add records to an updatable view, not the table, and save to the table by TABLEUPDATE(), but if you do that, you see the new record in the view right away before a TABLEUPDATE(), the TABLEUPDATE() just saves changes to the table in contrast to a view REQUERY, which refreshes the view with data from the table.

Normal order of things when working with updatable views:
SET MULTILOCKS ON

use view in form load (or use Dataenvironment of SCX forms or Dataenvironment class for form classes in a VCX and visually add the view there to be opened for the form)

When the view is opened index it in buffermode 3, set it to buffermode 5.

have a grid set to the view as recordsource, recordsourcetype set to alias and perhaps other controls set their controlsource to viewname.fieldname

Within the form only work on the view, the table is changed through the view only. So you INSERT INTO viewname ... for example or APPEND BLANK IN viewname.

You finally do TABLEUPDATE(.T.,.T.,"viewname") to save changes or TABLEREVERT("viewname") to cancel changes.

You can REQUERY() the view to see changes of the table by other users, but only when there are no changes in the view buffer. So you first save or revert your changes and then REQUERY("viewname").

So a simple save button click code could be

TABLEUPDATE(.T.,.T.,"viewname")
REQUERY("viewname")
Tisform.Refresh()

and a cancel button click would be

TABLEREVERT("viewname")
REQUERY("viewname")
Tisform.Refresh()

The esential thing is to work with an updatable view to be able to make changes to the view cursor and finally save them or cancel them.

You can also work directly on the table itself, but that has a major pain of always working on the whole set of data and in time the table grows large, with a view you can query and work on a subset by filtering the view. Next step would be reading about view parameterization.

Bye, Olaf.

 

[AlastairOz]

Hi Olaf,
I am entering data into text boxes where the control source is the buffered table. Then TABLEUPDATE() to the base table.
The grid then requeries and displays the updated record.

....by the way, I changed the record source from a temporary cursor to a cursor adaptor.
I load a grid programmically onto the form with recordsource type=4, record source = "select etc etc"
I set the clause READWRITE in the select query.

I have set MULTLOCKS ON in the form load:

SET TALK OFF
SET EXCLUSIVE off
SET DELETED on
SET MULTILOCKS ON
CURSORSETPROP("buffering", 5, "cLights")
CURSORSETPROP("buffering", 5, "assetlts")

cLights is the cursor adaptor.
I also set the buffering to 5 in the data environment.
I set the options in the cursor adaptor builder
tab 3 as "send updates" "Auto updates" "Update all fields",
update using "SQL Upadate", "Key and Modified fields"


Now I have another text box which control source is the cursor adaptor.
I can type changes into this text box and see the change in the grid. But the change is not getting to the buffered table.
I tried using the TABLEUPATE command for the cursor adaptor, but it gave me an error message that I need to have table buffering ON. I do have buffering on.
So what am I missing?

 

[Olaf Doschke]

You're still missing, that you should bind to the table directly, bind the textboxes and the grid to the cursor adapter cursor, that's your working horse, the table is jsut the storage to pull data from in the init and store it, when finished with editing.

TABLEUPDATE is the command that not jsut only updates existing records, but also inserts into the table what you inserted into the cursoradapter cursor and also deletes from the table what you deleted from the cursor. In short: it saves changes.

You don't do the "TABLEUPDATE to the base table", like I showed you apply the TABLEUPDATE to the view or cursoradapter alias name, the view or cursoradapter then updates the table.

Bye, Olaf.

 

[Olaf Doschke]

Also, you shouldn't set the grid to a recordsourcetype SQL-Select. That way you're not binding to the view or cursoradapter cursor, but to a readonly temp cursor.

Bye, Olaf.

 

[Olaf Doschke]

Excuse me, you're still missing that you should NOT bind to the table directly.

And in fact the buffering of the table makes it a double staging of the data, once you start working with the cursors correctly. Cursors are buffered, TABLEUPDATE()ing them flushes there buffer to the table buffer, if you set the buffering on for the tables, but I assume you want changes to save directly, then don't buffer the table, only the cursor.

Bye, Olaf.

 

[AlastairOz]

Hi Olaf,
That is working much better, thanks.
A couple of things.
When I refresh the grid, it does not see the PK value in the grid from the table after update. The table has the PK value.
The grid shows 0 for PK, all the other values OK.
Also how do I apply filtering or ordering to a cursor adaptor?

 

[Olaf Doschke]

The PK is genrated in the database when TABLEUPDATE() is done, but the cursor only refreshes with the new PK, if you requery it afterwards or if you use the AfterInsert method of the CA class.

The second option is a but more complex, so for the beginning, do

Thisform.dataenvironment.cursoradapter1.CursorRefresh()
Thisform.Refresh()

Ordering is done the same as with views: By Indexing and SET ORDER.

Bye, Olaf.

 

[AlastairOz]

I have got this grid going now pretty much how I want using the cursoradaptor.
Its refreshing as required. Add, delete, edit, all OK.

I have been struggling with filtering the grid.
I can filter and set order in the select command box, in the bulder, but have had no luck any other way.

I would like to enable custom filters and sorting by way of command buttons on the form.
Any help appreciated.


Regards
Alastair

 

[Olaf Doschke]

You can SET FILTER on the cursor of the CA (cursor adapter) and you can SET ORDER to an index.

To create and use indexes in a sample with person data firstname, lastname:

Form.Load() or Init():

CAPersons.CursorFill() && or Dataenvironment.AutoOpenTables = .T.

Cursorsetprop("Buffering",3,"Persons")
Select Persons
INDEX ON Firstname TAG First
INDEX ON Lastname TAG Last
SET ORDER TO IN Persons && reset to initial Order as in SelectCmd, not by index tag.
Cursorsetprop("Buffering",5,"Persons")

Now in a button "First" in the click event put

SET ORDER TO TAG First IN Persons
Thisform.Grid1.Refresh()

You can also sort DESCENDING by SET ORDER TO TAG ... IN Persons DESCENDING

And to Filter take a Texbox to filter eg Lastname, rename it from Text1 to txtFilterLastname and in Form's Init() put

SELECT Persons 
SET FILTER TO Lastname = Thisform.txtFilterLastname.Value

And put Thisform.Refresh() into the txtFilterLastname.InteractiveChange() event code.

Bye, Olaf.

 

[AlastairOz]

Hi Olaf,
Your a legend, thanks so much. Works perfectly now!
Regards

Alastair