Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?
From: Stefan Wuebbe (stefan.wuebbe_at_gmx.de)
Date: 06/18/04
- Next message: christophe: "vfp and MSexplorer ole error"
- Previous message: christophe: "Re: multiselect getfile ?"
- In reply to: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Next in thread: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Reply: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 18 Jun 2004 08:58:55 +0200
Hi Dennis,
Late reply, sorry. FWIW, after reading your description I believe
similar scenarios do work for me with
- "surrogate" primary keys
> Table 1, master surveys, has a record id field and a master survey id field.
> The default value for the record id field is NewID(). The default value for
> master survey id is the value of record id. Master survey id is the primary
> key and record id is a candidate key.
I'd suggest to do it the other way around - use the "meaningless",
surrogate record id field as PK...
> Another complicating factor is that the master questions need to have
> version control. In other words, if I change the
> wording of the question text to the point which it changes the meaning of
> the question, a new version (meaning a new record) of the master question
> needs to be created.
... and do the version control by inserting the archived question
content into a separate history table. Would that work in your
case?
- joined and calculated columns not updatable
> Now, the problem. When I inserted a record to the view, I could not see the
> question text on the grid. I needed to see the text so I could revert the
> insert if I happened to pick the wrong master question.
Right, the view does not "calculate" joined (and calculated) columns
until you TableUpdate() and Requery() / Refresh(1,1,'TheView').
Since the foreign key is the crucial thing, I think it's a good idea
to keep the dependent / joined "question" field read-only.
IOW, if you want to display the presumed new value of r/o table2
"question" field in an Append Blank / Replace situation, I'd suggest
to let the GUI care for display issues - i.e. use a combobox with
boundcolumn = 'table3.foreignKey' and displayvalue = table2.question
or use something like Lookup() to update the value of the r/o
"question" control, until the next tableupdate.
-Stefan
"Dennis Longfellow" <xxxdrlongfellowxxx@xxxdirectracx.xxx.com> schrieb im Newsbeitrag
news:uCIcBuBSEHA.2404@TK2MSFTNGP09.phx.gbl...
> Stefan,
>
> Thanks for addressing my problem. I was hoping that someone would.
>
> I did abbreviate the description somewhat because I just wanted to let Rob
> know that there was possible issue with using a view on a grid when records
> are inserted to the view.
>
> I will now describe the problem in more detail and hopefully further
> discussion will help others as well as me.
>
> First let me give you a little background. We are in the process of
> developing a database that contain responses to surveys we conduct each time
> we perform a certain type of activity. Currently there are three surveys,
> two which contain the same bank of questions. For these two latter surveys,
> the manager of the activity fills out one as a sort of a checklist and the
> functions responsible to see that tasks covered by each question fill out
> the second survey. In order to have reusable surveys, I set the database up
> using a master survey concept and three surveys for each activity are
> generated via their master surveys.
>
> Since my particular problem involved updating master surveys, here is the
> detail of how I designed that part. Table 1 contains the master survey
> header record with such data as the survey name and the type of activity
> that it covers. Table 2 contains the master questions containing such
> information as the question text and the allowed responses. Table 3 is a
> junction table between table 1 and table 2 to set up a many to many
> relationship between the two table. (Remember, two of the surveys used the
> same bank of questions.) Another complicating factor is that the master
> questions need to have version control. In other words, if I change the
> wording of the question text to the point which it changes the meaning of
> the question, a new version (meaning a new record) of the master question
> needs to be created.
>
> Table 1, master surveys, has a record id field and a master survey id field.
> The default value for the record id field is NewID(). The default value for
> master survey id is the value of record id. Master survey id is the primary
> key and record id is a candidate key. (In case you are wondering, the
> second id field was used to give us the option of assigning a value other
> than that of record id to the primary key field. This happens in another
> table in the database.)
>
> Table 2, master questions, has a record id field as well with NewID() as the
> default value. A second field, question master id, is set to the value of
> record id for the orginal version of the master question. A third field,
> version, is initally set at "000". The question master id and version field
> are combined to form the primary key. Record id is a candidate key. If a
> new version of the master question is called for, a new record is created
> with a new record id, the same question master id, and a new version, which
> is the old version incremented by 1.
>
> Table 3, survey questions, also has the record id field with the default
> value generated by NewID() and this is a candidate key. It also has a
> master survey id field whose value is set to the value of the master survey
> id of its parent master survey and a question master id field whose value is
> set to the value of the question master id for its master question. These
> two fields are combined to form the primary key for the table. I was able
> to get away with this, because there is only only survey question allowed
> for master question regardless of it version. The idea is that it only
> points to the current version of the master question. I cheated a little to
> do that. I included another field in table 3 that contains the record id of
> the current version of the master question although in retrospect, I could
> have included version in the primary key because that is a field in table 3.
>
> Tables 1 and 3 are used to generate the individual surveys for each
> activity. The questions with the individual surveys have foreign key on the
> primary key of table 2.
>
> I designed a view which I will call view1 on tables 3 and 2 with all fields
> from table 3 and the question text from table 2. All of the table 3 fields
> are marked as updateable and the master survey id and question master id
> fields are marked as key fields. The question text field is not updateable
> and I could not select that option in the view designer. I just tried it in
> my test database.
>
> The form that was giving me the problems was a one to many form with table 1
> as the parent table and view1 on the grid as the child table. When the form
> is loaded, buffering on the view is changed from optimistic row to
> optimistic table. To add a survey question to the grid, I would pop up a
> form with a list of available master questions, double click on the one I
> wanted to add and the appropriate survey question is supposed to be added to
> the grid by inserting into the view via the INSERT - SQL command. The
> command contains values for the master survey id and question master id
> fields.
>
> Now, the problem. When I inserted a record to the view, I could not see the
> question text on the grid. I needed to see the text so I could revert the
> insert if I happened to pick the wrong master question. A solution was to
> change the buffering to optimistic row. Of course, this causes a new
> problem. As soon as I move the record pointer, the insert becomes
> permanent.
>
> By the way, I solved this by just accepting the fact that the user could
> inadvertently add a wrong survey question. What made that possible is that
> I already gave him/her the option to deactivate a survey question so that it
> would not be used with a survey. The user can just deactivate a survey
> question if he/she picked the wrong question master. Not a great solution,
> but since only three users (more than likely, moi) will be doing this for
> now, we can live with it.
>
> Sorry to be so long winded and thanks for your patience if you took the time
> to read all of this.
>
> Dennis Longfellow
>
> "Stefan Wuebbe" <stefan.wuebbe@gmx.de> wrote in message
> news:%238wZlxVREHA.2112@TK2MSFTNGP11.phx.gbl...
> > > a viable option; however, I have had problems using a view on a grid
> when
> > > adding records to the view. I found that I could not see a newly
> inserted
> > > record on the grid until I issued a TABLEUPDATE() then issued a
> REQUERY() on
> > > the view
> >
> > Sounds as if a (primary) key value may have been different
> > before and after the Requery() ...
> >
> > > defeating one of the purposes for having the data buffered which is
> > > the ability to cancel the insert. It is my understanding that a view
> uses
> > > the actual table and not the buffered data when the REQUERY() is issued.
> >
> > You're right. So, as a guess, one way to get the effect you
> > described could be -
> > have a local view with SendUpdates=.T. (intentional, non default)
> > but key-field Updatable=.F. (forgotten default)
> > and a base table with a PK field default value=NewID()
> > then Append Blank in view, and Replace With <new values>.
> > Now, when you TableUpdate('theView'), the default value
> > function of the base table field assigns a new PK value.
> > The view cannot "know" about it, until the next Requery()
> >
> > On the other hand, when you make the view's PK field
> > updatable and insert all final values at once
> > Insert Into theView (PK, otherfields) ;
> > Values ( NewID('BaseTable'), 'some value')
> > or when you make the view PK field have a default value instead
> > of the base table field, you still would need update the (unbuffered)
> > base table(s) - but afterwards no Requery() since all values should
> > be congruent.
> >
> >
> > > I use VFP6 SP5 and maybe this behavior is different in later versions.
> >
> > No, view implementation did not change much since Vfp3.
> > Seems surprising to me that "they" invented such a cool feature
> > back in 1993 :-) Especially the default handling for various
> > DbSetProps is extremely smart, even more advanced than the
> > new CursorAdapter, IMO, e.g. tables list, updatable-fields list,
> > data types etc.
> >
> >
> >
> > hth
> > -Stefan
> >
> > "Dennis Longfellow" <xxxdrlongfellowxxx@xxxdirectracx.xxx.com> schrieb im
> Newsbeitrag
> > news:OLZachMREHA.3300@TK2MSFTNGP09.phx.gbl...
> > > Hi, Rob,
> > >
> > > Since you are using the TABLEUPDATE() function, I assume that the
> underlying
> > > table data is buffered. This implies to me that you can choose to
> cancel
> > > the insert using the TABLEREVERT() function. If that is the case, then
> > > after you insert the record to the cursor and decide to save it
> permanently,
> > > why not issue a SQL INSERT against the underlying table just before
> issuing
> > > the TABLEUPDATE(). Of course, you will have to issue a TABLEUPDATE()
> for
> > > both the cursor and the underlying table.
> > >
> > > I see that Altman and Gerben have recommended using a view which is
> probably
> > > a viable option; however, I have had problems using a view on a grid
> when
> > > adding records to the view. I found that I could not see a newly
> inserted
> > > record on the grid until I issued a TABLEUPDATE() then issued a
> REQUERY() on
> > > the view defeating one of the purposes for having the data buffered
> which is
> > > the ability to cancel the insert. It is my understanding that a view
> uses
> > > the actual table and not the buffered data when the REQUERY() is issued.
> I
> > > use VFP6 SP5 and maybe this behavior is different in later versions. Of
> > > course, I could be missing something and maybe someone else can
> enlighten
> > > us.
> > >
> > > Hope that helps,
> > >
> > > Dennis Longfellow
> > >
> > > "Rob Richardson" <notreally@n2net.net> wrote in message
> > > news:%23FYbLMLREHA.3628@TK2MSFTNGP12.phx.gbl...
> > > > Greetings!
> > > >
> > > > I want to use an SQL SELECT statement to get data from a table into a
> > > > cursor. I have a grid bound to the cursor. Occasionally, I will need
> to
> > > > add a record to the table. I use an SQL INSERT statement for that. I
> > > want
> > > > the new record to be permanently stored, and I want the new record to
> > > appear
> > > > immediately in my grid. I can do one or the other, but not both. If
> I
> > > > insert the record into my cursor, call TABLEUPDATE() and call
> > > > thisform.Refresh, the new record appears in the grid, but it does not
> get
> > > > into the underlying table. The next time I run the program, the
> record I
> > > > thought I had added does not appear. If I insert the record into the
> > > > underlying table and then call REQUERY to update the cursor, I get an
> > > error
> > > > saying that Requery is not valid for native tables. Do I have to
> close my
> > > > work area and rerun the original SQL SELECT statement?
> > > >
> > > > Thanks very much!
> > > >
> > > > Rob
> > > >
> > > >
> > >
> > >
> >
>
>
- Next message: christophe: "vfp and MSexplorer ole error"
- Previous message: christophe: "Re: multiselect getfile ?"
- In reply to: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Next in thread: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Reply: Dennis Longfellow: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
