problem with duplicating a record

[nevin2007]

hi all
i have an [blue]indexed[/blue] table and want duplicate current record when user press F5 .i write a procedure and call it by on key label.the problem is that when i fire F5 an error occur.
procedure code :
[blue]
SCATTER TO recdata
INSERT blank
GATHER FROM recdata
[/blue]
error message is :
[red]
INSERT cannot be issued when row or table buffering is enabled or when integrity constraints are in effect.
[/red]
i set buffering off by using this :
[blue]CURSORSETPROP("Buffering", 1)[/blue]
but the error happen again.

i used VFP9. can any one help me ?
thanks.

 

[GriffMG]

You could try replacing your INSERT with an APPEND BLANK,
but it might still accidently duplicate an empty record in the table.

Using the scatter and gather is going to do the same thing - duplicate the record - so you need to find the index that has a unique charactistic and not 'gather' that field either - replace it with a unique value.

scatter to recdata fields except mykeyfield
append blank
gather from recdata fields except mykeyfield
replace mykeyfield with myUniqueValueofSomeKind


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

There are several issues here.

First, if you use an ON KEY LABEL, you have no way of knowing where your application is when the user hits the key in question. You can't make any assumptions about the environment at that particular moment.

In this particular case, you are assuming that the table that you want to update is in the selected work area. The first thing I would suggest is to SELECT the work area before you do anything else.

But that's not ideal, because you then leave that work area selected, which might muck up whatever code was running when the user hit the key. So you really need to save the current work area and restore it again afterwards.

But it would be better if you avoided ON KEY LABEL completely. A better approach is to use the Keypress event of the form, since that will only fire, by definition, when the form is active.

Next point. INSERT BLANK is an old command which is included for backward compatibility only. APPEND BLANK is better.

But both commands could cause problems in certain cases, specifically if you have referential integrity in force, or if you are trying to enforce unique keys through a primary or candidate index. In those cases, it's generally better to use the SQL form of INSERT, so that you can append the record and populate the fields in one shot.

Give this a try, and come back if the problem persists.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[nevin2007]

hi GriffMG & MikeLewis
[blue]In response to GriffMG answer : [/blue]

the table have user define index and user can change it by double clicking header column of grid.if i use your suggestion record insert in wrong place ( if we have 5 record that they have different data but similar index key value and press f5 on first record ,record inserted in bottom of 5th record)

[blue]In response to MikeLewis answer : [/blue]

table shown in a form with data grid Therefore when user press f5 i know current work area name.

[red] The goal is inserting record exactly in bottom of current record [/red]

thanks

 

[Mike Lewis]

The goal is inserting record exactly in bottom of current record

I assume you mean at the bottom of the current table.

But, even so, my point about referential integrity and unique keys is still valid. I suggest you consider the SQL form of INSERT.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

INSERT BLANK requires exclusive access to the table, which implies that many things are possible, although probably slow and inelegant!

Mike, I think he means 'the goal is to insert a record just after the current one' which is not possible if the user has a truely user-defined index key!

The INSERT command is generally ok for free tables, it can't do an 'real' INSERT on a table within a database - but can slip a blank record between two on a free table.

The only snag is - the index might not leave it there! So, you would have to make pseudo indexes of some kind - they could be simulated by creating an index, copying out the data to a temp table and then removing the index and pulling it back in (or just 'SORTing' out and back without the index).

Ok for fifteen records, a pig for 20,000!




Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[markros]

Insert always inserts a record at the very bottom of the table. (if you're not using INSERT BEFORE command, of course).

 

[Mike Lewis]

The point is not where the record is inserted. Rather, if you use INSERT or APPEND BLANK, you get a blank record, which means you risk breaking referential integrity or unique key values.

With the SQL-style INSERT, you insert the record and populate it with data at the same time, so you don't have the same problem.

I assume that's what's relevant here, because of the message re integrity constraints.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

Right, it's obvious as well.

You can do

scatter name loRecord

loRecord.PkField = NewID() && whatever the function name is to generate a new ID
insert into myTable from name loRecord

 

[GriffMG]

Markros.

In a FREE table, INSERT doesn't put the record at the bottom.

It really does put it after the current record.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[markros]

Never seen that before - I always use Insert-SQL, though.

 

[nevin2007]

hi all

I assume you mean at the bottom of the current table.

but i exactly mean [red]under current record[/red]
any one who used foxpro for dos know we do not have copy & paste there. the best way for simulating copy & paste was the way i wrote in first post.

i only wanted to simulate that in vfp9.
now i was discouraged from doing that and thank you all for your suggestion.

I try to find a solution for this problem and if successful , sure I will announce.