none
How to tableupdate in grid (excel style)

    Pregunta

  • Hello Experts

    Im trying to figure out the best way to save records in my GRID.  My grid is designed so that when I reach the last column of my GRID, hitting ENTER or the DOWN ARROW will add a new record.

    How would I tableupdate? and should I put a Save button so everytime user is done entering there data they can simply hit the save button to save.

    please help

    Thanks

    jueves, 22 de diciembre de 2011 13:06

Respuestas

  • Well, you had a good idea initially: A Save button.

    It doesn't matter when you do a tableupdate. A grid doesn't hinder you to do it anywhen, so to say. The grid modifies it's recordsource alias (or it's buffer, if in buffer mode), tablupdate() saves changes in the buffer to the dbf. Those two layers are totally independent. So there is no such best place or moment.

    A Button already will solve the only problem of the current records current field value (not) being updated, as it takes the focus from the grid, so the grid goes through the last valid event, saving the current value to the grid cursor and you save all, that is displayed.

    Why should there be any hurdle? Do you have had any problem with any combination of grid cursor and tableupdate()? I remember one related to the change of the current field: A Save button in a Toolbar. That differs from a command button on the form in NOT taking the focus away from the grid. That can result in the last editing of the current field not being saved, as it is not yet even saved to the grid recordsource (buffer). But that's the only case I can remember. It's easiest to avoid toolbars for that matter.

    On the other side: If the table is totally unbuffered, you are really very near to editing the DBF with a grid, it's very like a browse of the table, after leaving any field, that already is stored to the dbf. That has that advantage, but also the disadvantage of many single dbf file changes, many locks etc. and can often lead to problems in multi user environments. Even though the locks for such small fieldwise changes are needed rather short, LAN latency can make that a bad experience in case even only two users change the same data.

    Besides, the tableupdate() can of course have the same problems in form of conflicts, than with editing the table in any other way than via a grid, the grid itself doesn't contribute to that problems in any way, that's merely the conflicts due to changes of the same table records of different users. You always have to cope with that.

    Overall, why do you think the grid is special in any way? It's just another control modifying records, there is no other difference to other controls.

    Bye, Olaf.

    jueves, 22 de diciembre de 2011 20:23

Todas las respuestas

  • The grid is not contributing on how you would need to update the source tables, this solely depends on the type of alias you have and let the user modify.

    TABLEUPDATE() only applies to updatable local/remote view cursors, updatable sql passthrough cursors, updatable cursoradapter cursors or tables. 

    If you SELECT ... FROM sometable INTO CURSOR gridalias, you get a cursor that doesn't update it's source tables via TABLEUPDATE(), but this cursor therefore is independant and can be filled in more ways than just by a single query, you can append to it without yet appending new records to the table, you can do all kind of stuff to it. And in generating them you can more easily filter data via dynamically created sql statements, you can dynamically include filters as where clauses, join different data via joins etc. But you would rather do so for display of data, not for editing data.

    So, what type of cursor do you bind to grid? That's the main question.

    If I assume the type of cursors you use ar the last mentioned, as I and others recommended to go the SQL route.

    Then as one solution you would repeat the changes to the readwrite grid cursor to the table(s) they should go in.

    It's not that complicated, if you do cursorsetprop("Buffering",5,"gridcursoralias") right after the grid cursor was generated, because then foxpro puts every change in a buffer of the readwrite cursor and at any time you can detect the operations/modifications done from the initial state of the cursor created by an SQL.

    To get to the point - you can loop over changed, deleted and new records with a simple loop:

    Set Deleted Off && to include looping through deleted records
    Go Top in gridalias && begin at the top of the cursor
    lnModifiedRecno = GetNextModified(0,"gridalias",.f.) && returns the number of the first record modified or new
    Do While lnModifiedRecno<>0 && if lnModifiedRecno=0 means no (further) changes
      Go lnModifiedRecno in gridalias && go to that record
      ? lnModifiedRecno, GetFldState(-1,"gridalias") && output it's fieldstate
      lnModifiedRecno = GetNextModified(lnModifiedrecno,"gridalias",.f.) && get next modified record
    Enddo
    Set Deleted On && to turn off display of deleted records again

    obviously search&replace "gridalias" with your cursors alias name here.

    This is a starter to demonstrate how easy you get just the records you need to take care of, via GETNEXTMODIFÌED(). At the line outputting ? lnModifiedRecno, GetFldState(-1,"gridalias") you get info about what changed. The return value is a string of values 1-4 in each position, length of the string is the number of fields plus 1, because the first digit is the record deletion flag status.

    Much more details about this in the help topic of GETFLDSTATE.

    You will always get a mix of either 1s and 2s, or 3s and 4s: If a record was read in and changed, it's fldstate will consist of 1s and 2s only, if a record was added as new it's fldstate will consist of 3s and 4s only.

    A fldstate consisting of 1s only means no change, you will not get a record of that kind in the loop given, as it only loops through changed or new records. Well, it loops through modified records. What you can have is a fieldstate only consisting of 3s, meaning a newly added record, which fields all have stayed with their default value. 2s and 4s denote changed fields, changed from their read in value (2) or changed from the initial new value (4).

    Instead of printing/outputting the found record numbers you can call a routine saving that current modified record via a) an update, if a "2" is contained in the fieldstate string returned b) a deletion of the record, if the first fldstate digit is 2, meaning deletion flag has turned from not deleted to deleted c) inserting a new record for fldstates containing 3s and 4s. And d) you can skip records, which fieldstate start with a 4, as those are new records, but new records that also were deleted before being saved, so you can skip them.

    It doesn't actually matter which fields have status 1,2,3 or 4, in any case you can just open the source table, move to the record with the same id as in the gridalias and then use SCATTER of the gridcursor record and GATHER into the corresponding table row or APPEND+GATHER for new records and, well, DELETE for deleted records.

    I could expand on code needed, but the main question is, if you really would want to go that route. Obviously this is more work to do, than a simple Tableupdate(), but it can pay. As long as your table is small, the easier way would be binding the grid to the table and go with Tableupdate(). The moment it pays do not do so, is, if tables get larger, filtering will get sluggish, also if data you want to let the user edit comes from more than one table obviously you would need a cursor created by some sql and in the save routine would need to distribute the modified data in those tables.

    The good thing is you can mix on how to go about this depending on wanting to only list/display data or use a grid as data entry control. Most people will rather not use the grid for data entry, but have tables opened additionally to grid cursors, using the grid just for navigating to a record, then going to the corresponding record in tables and allow editing that record with single row controls, like textbox, combobox.

    Bye, Olaf.





    • Editado Olaf Doschke jueves, 22 de diciembre de 2011 15:55
    jueves, 22 de diciembre de 2011 15:14
  • I actually took the example from Marcia Atkins to get the behaviour I mentioned above.

    http://social.msdn.microsoft.com/forums/en-US/visualfoxprogeneral/thread/dd4514d9-4d3a-4f98-96f4-5211d3425241/

    having said that, do I still need to do the loop? does this change the way i would update my table?

    jueves, 22 de diciembre de 2011 15:54
  • Well, as far as I browsed through that code it just is about how to handle the grid control to add new records to the grid cursor and focus on the next line.

    It doesn't have anyhting to do with saving into the source table. That only and solely depends on the type of rowsource you have bound to the grid. If you bind the table to the grid, adding records to the grid alias means adding records to the table and you have only to do tableupdate(), if the table is buffered.

    It only depends on what the rowsource is.

    You could have referred to that thread, inititally, couldn't you?

    Bye, Olaf.


    • Editado Olaf Doschke jueves, 22 de diciembre de 2011 16:11
    jueves, 22 de diciembre de 2011 16:05
  • yes I could have sorry olaf :)
    jueves, 22 de diciembre de 2011 16:11
  • Well, Marcias code works on any grid rowsource that can be writte to. All that matters in concern about updating a dbf is what you bind to the grid.

    I still don't know and so still can't answer your coe question. Do you bind a table to the grid? 

    You could also do without that code of Marcia, you just need to set all the "Allow.." properties of the grid to .t. to allow editing and adding new records. It's merely a matter of comfort to automatically add new records when reaching the last column.

    Bye, Olaf.

    jueves, 22 de diciembre de 2011 16:51
  • yes I do bind a table to the grid

    do you mean if set allowaddnew .t. and simply do something like this?

    LPARAMETERS nColIndex
     
    if m.nColIndex = this.ColumnCount and !mdown() and readkey()=15
    keyboard '{DNARROW}'
    ENDIF

     

     

    • Editado mikelvfp jueves, 22 de diciembre de 2011 17:03
    jueves, 22 de diciembre de 2011 17:01
  • Well, have a read on the grid help topic about AllowAddNew:

    "If AllowAddNew is set to True (.T.), you can add a new record to a table in a grid by pressing the down arrow while positioned on the last record in the grid, as long as the grid is read/write."

    Simply try this:

     

    Create Cursor curTest (iid i autoinc, cText C(10))
    Append Blank && for some reason it doesn't work, if the grid is completly empty, we need 1 row initially

    _screen.AddObject("grid1","grid")
    _screen.grid1.AllowAddNew = .t.
    _screen.grid1.RecordSource = "curTest"
    _screen.grid1.Visible = .t.

    Now press dwnarrow in the grid and you get a second row, don't you?

     

    Bye, Olaf.

    Edit: The code you provided will also add a new row when you press ENTER, because it translates ENTER (which means readkey=15) to DWNARROW. But it would do so in any row, so you would also add rows when not in the last row. If you can't cope with only adding records by downarrow, then check out, if you're at EOF in the grid cursor by the code provided by marcia (SKIP 1, then check EOF) and only apply the KEYBOARD '{DnArrow}' in that case of course.




    • Editado Olaf Doschke jueves, 22 de diciembre de 2011 17:26
    jueves, 22 de diciembre de 2011 17:07
  • Yes this works fine, I just need to know how to place my tableupdate and where since my grid is bound to a table?

    jueves, 22 de diciembre de 2011 18:57
  • Well, you had a good idea initially: A Save button.

    It doesn't matter when you do a tableupdate. A grid doesn't hinder you to do it anywhen, so to say. The grid modifies it's recordsource alias (or it's buffer, if in buffer mode), tablupdate() saves changes in the buffer to the dbf. Those two layers are totally independent. So there is no such best place or moment.

    A Button already will solve the only problem of the current records current field value (not) being updated, as it takes the focus from the grid, so the grid goes through the last valid event, saving the current value to the grid cursor and you save all, that is displayed.

    Why should there be any hurdle? Do you have had any problem with any combination of grid cursor and tableupdate()? I remember one related to the change of the current field: A Save button in a Toolbar. That differs from a command button on the form in NOT taking the focus away from the grid. That can result in the last editing of the current field not being saved, as it is not yet even saved to the grid recordsource (buffer). But that's the only case I can remember. It's easiest to avoid toolbars for that matter.

    On the other side: If the table is totally unbuffered, you are really very near to editing the DBF with a grid, it's very like a browse of the table, after leaving any field, that already is stored to the dbf. That has that advantage, but also the disadvantage of many single dbf file changes, many locks etc. and can often lead to problems in multi user environments. Even though the locks for such small fieldwise changes are needed rather short, LAN latency can make that a bad experience in case even only two users change the same data.

    Besides, the tableupdate() can of course have the same problems in form of conflicts, than with editing the table in any other way than via a grid, the grid itself doesn't contribute to that problems in any way, that's merely the conflicts due to changes of the same table records of different users. You always have to cope with that.

    Overall, why do you think the grid is special in any way? It's just another control modifying records, there is no other difference to other controls.

    Bye, Olaf.

    jueves, 22 de diciembre de 2011 20:23
  • Thanks Olaf I will do the Save Button Idea .

     

    THanks

    jueves, 22 de diciembre de 2011 20:34
  • Hi Mikeisnew2vfp,

    Welcome to the MSDN forum.

    How about Save Button Idea going? If you fixed your issue finally, would you like to share your solutions and experience here, it will be very beneficial for other community members who have similar questions. At the same time, please mark the helpful replies as answer.

    Sorry for any inconvenience and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    jueves, 29 de diciembre de 2011 6:42