Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?
From: Dennis Longfellow (xxxdrlongfellowxxx_at_xxxdirectracx.xxx.com)
Date: 06/01/04
- Next message: Leon: "Re: Urgent Question to Microsoft People"
- Previous message: Craig Berntson: "Re: Urgent Question to Microsoft People"
- Next in thread: Stefan Wuebbe: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Reply: Stefan Wuebbe: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 1 Jun 2004 16:00:51 -0500
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: Leon: "Re: Urgent Question to Microsoft People"
- Previous message: Craig Berntson: "Re: Urgent Question to Microsoft People"
- Next in thread: Stefan Wuebbe: "Re: How do I update a cursor after adding a record to the underlying table? Or vice versa?"
- Reply: Stefan Wuebbe: "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
|
