Board index Database

Adding a new record to a table after a delete

Adding a new record to a table after a delete

Post by Marc Wrigh » Tue, 21 Aug 2001 19:25:34



Dear all I wonder if you could help.

Im using the visual foxpro ODBC driver and sql passthrough commands

I have a table setup with a primary key of say ref.

After adding a record to a table and then deleting it using the following

sqlexec(nconnection,"DELETE from tablename where ref='x'")
followed by a tableupdate(.t.)
the record disappears.

but when i try to add another record with the same ref it comes up with a
'index key violated error' which tells me it hasnt actually deleted the
record.

If i do exactly the same thing but use the microsoft SQL 7 it works fine?

Can you help???

Yours Marc

 
 
 
Top

Adding a new record to a table after a delete

Post by Tom Pipe » Tue, 21 Aug 2001 22:25:09


Hi Marc,

Foxpro does not support the setting of SET DELETE ON, so any record that you
delete is still in the Table until you do a PACK of the Table. Actually, it
is generally accepted practice to not attempt to re-use primary key values.

Best regards,
Tom


Quote:> Dear all I wonder if you could help.

> Im using the visual foxpro ODBC driver and sql passthrough commands

> I have a table setup with a primary key of say ref.

> After adding a record to a table and then deleting it using the following

> sqlexec(nconnection,"DELETE from tablename where ref='x'")
> followed by a tableupdate(.t.)
> the record disappears.

> but when i try to add another record with the same ref it comes up with a
> 'index key violated error' which tells me it hasnt actually deleted the
> record.

> If i do exactly the same thing but use the microsoft SQL 7 it works fine?

> Can you help???

> Yours Marc


 
 
 
Top

1. deleting records in a table and then adding new records

I am accessing an Access database table via a VB program and displaying its
records in a data grid on a form.  One of my procedures consists of deleting
all the records of this particular table in the database , and immediately
repopulating the table with new records.

My problem is that occasionally, the repopulated table does not display,
say, the first 20 of its records in the data grid view. But when I check the
table in Access all the new records are there.  But there is a catch: the
table is really corrupted, for if you "repair" the database, this table now
only contains the fewer number of records that were displaying the in the
repopulated data grid.

I delete the records with the following code:

    Dim DBtemp As dao.Database
    Set DBtemp = OpenDatabase(WF) 'the working file
    DBtemp.Execute "delete * from invoices_pending"
    DBtemp.Close
    Set DBtemp = Nothing

and then immediatly repopulate the table via more code.

Could my problem be that the above deletion process doesn't finish before
the repopulation starts, resulting in the first 20 ro so new records not
really being recorded?

This is a bit hard to debug, since the problem occurres erratically.  I have
put a pause in between the deletion and repopulation of the table, and it
seems to cure the problem. But I have thought that I had the problem cured
before only to have it come back.

2. Remote Distributor Option Unavailable

3. Cannot add record to Visual Foxpro table after deleting all records in table

4. Trusted connection

5. ADO2.5, can't add new record to an empty table using record binding

6. pulling up a record from a DBGrid into another form

7. Adding record in detail table or create a new master table for it

8. d3 Index Question

9. Deleting all records then adding new ones in a single update

10. adding new records & new record

11. Add new table and add fields to table - Syntax - Working Code Example

12. Can't add new records to access tables moved to SQL Server

13. how are new records added to a table


All times are UTC
Board index