Couldn't perform the edit because another user changed the record

[farmond]

Tenho este erro ao editar um registro em uma aplicação Delphi 7 com conexão via BDE a base de dados MS-SQL 2000:
I've got this error when editing a record on a Delphi 7 app via BDE connection to a MS-SQL 2000 database:
>>
Couldn't perform the edit because another user changed the record
<<
O erro surgiu quando a tabela principal ultrapassou 128.000 registros.
I've got the error when the main table grows over 128.000 records.

Antes de migrar para ADO - o que vai dar muito trabalho, gostaria de encontar alguma solução. Já tentei alterar todas as propriedades da TQuery e da conexão BDE - sem sucesso.
Before begin the migration to ADO - spending too many work time, I'd like to found a solution. I've changed all TQuery properties and BDE connection properties - no success.

SQL Query
SELECT * from prot where prot_codigo = :cod

Tenho 5 tabelas vinculadas, todas com mais de 100.000 registros, sem campos com valores decimais.
I've got 5 linked tables, over 100.000 records each one, no decimal fields.

Espero encontrar alguém que já passou por isso!
Hope to find anyone that got this error!

[[Caral]]

Hello
I don’t know the problem that you have, when publishing with this sentence SQL
Perhaps if you gif us a little more information we pruned to help you.
Please try to right in Spanish, is better for us.
See you.

[[cHackAll]]

Caral, me parece que para farmond es un poco dificil escribir en español; veo que tu entiendes el ingles, una traducción no estaría mal, luego le damos otra traduccion para responderle...

Empezado por farmond

Tuve éste mensaje de error cuando editaba un registro en d7, mediante una conexión con una BD MS-SQL 2000:

Couldn't perform the edit because another user changed the record
No puede realizar una edicion porque otro usuario cambió el registro

El error surgió cuando la tabla principal sobrepasó los 128.000 registros o tuplas.

Antes de migrar a ADO (lo que me dará mucho trabajo), quisiera encontrar una solución. Ya intenté cambiar todas las propiedades del TQuery en la conexión BDE, sin resultados.

Código SQL [-]
SELECT * from prot where prot_codigo = :cod

Tengo 5 tablas "ligadas", cada una con más de 100.000 registros, sin campos con valores decimales.

Espero encontrar alguien que pasó por eso

En lo personal no tengo una respuesta pues nunca ví tal mensaje, pero puede deberse a un intento de edicion sin la clausula respectiva; o puede ser que el usuario no tiene los privilegios suficientes.

Empezado por cHackAll

you see this message(maybe) when you try to add/actualize one record, when you don't define first the filtered record.
When you assign a value to another record, lose the pointer in the recordset. Try to do this:

recorset.fields("prot_codigo")=prot_codigo

Is better to use the recordset class, and the Insert instruction to add new records. See the privileges also.

[D-MO]

The BDE uses rows effected to determine if an UPDATE is successful. SET NOCOUNT ON tells MSSQL to return 0 for rows effected for all UPDATE statements and thereby causing all UPDATES to fail.

Link
Google

Saludos.

[farmond]

Carissimos,
Para mi es mucho dificil escribir en espanol - let's try in english...

Here is part of the delphi code
...
qryProtocolo.FieldByName('userAlt').AsString := Usuario;
qryProtocolo.FieldByName('DataHoraAlt').AsDateTime := AgoraBD;
qryProtocolo.Post; - here is the error
qryProtocolo.Close;
qryProtocolo.ParamByName('prot_codigo').AsString := BuscaProt;
qryProtocolo.Open;
qryProtocolo.Locate('prot_codigo', BuscaProt, [loCaseInsensitive]);

All other queries runs OK, there's no request live queries opened during edit method, there's no decimal fields.

I've tried the following tips, unsuccessfully:

1. SET NOCCOUNT OFF on MS-SQL - this parameter is default on MS-SQL;
2. Use of decimal fields - BDE has a limitation of 15 decimal points - no decimal fields on the tables;
3. Use of timestamp fields - BDE and Windows different size for extended values of timestamp;
4. Change the dataset properties;

Using ADO connection I've got no error - but I'll spend about 3 motnhs to migrate all the code from BDE to ADO!

[[cHackAll]]

Código Delphi [-]
qryProtocolo.Insert; // .Append; .Edit;
qryProtocolo.FieldByName('prot_codigo').AsString := Codigo; // FIRST
 
...
 
qryProtocolo.FieldByName('userAlt').AsString := Usuario;
qryProtocolo.FieldByName('DataHoraAlt').AsDateTime := AgoraBD;
qryProtocolo.Post; 
 
...

[[Caral]]

Hello
How you can keep a data if you do not open the table first
Where do you open it?
Where is the open code, first to post it?

Sorry, my English is very bad.

[[egostar]]

Hi farmond

I have a question.

What type of AgoraBD are you working with?

Also, Could you try to do this?

Código Delphi [-]
 
   qryProtocolo.FieldByName('DataHoraAlt').AsString := '01/01/2007 10:00';

Please let me know the results....

Regards

[[Caral]]

Hi
Of course date and time, can be the problem
Mister Egostar has reason that can be the error.
Mani people from hear speak good English
Great

[farmond]

Para cHackAll - the error only occurs on edit method - insert and delete are OK;

Para Caral - this is part o the code - this form has 3585 lines; The code worked fine before the table grows over 128.000 records - no changes on the code, so the error is not in the code, but in the database connection. In other forms, thar references smaller tables, everything works fine.

Para egostar - AgoraBD : TDateTime;

[[egostar]]

Empezado por farmond

Para cHackAll - the error only occurs on edit method - insert and delete are OK;

Para Caral - this is part o the code - this form has 3585 lines; The code worked fine before the table grows over 128.000 records - no changes on the code, so the error is not in the code, but in the database connection. In other forms, thar references smaller tables, everything works fine.

Para egostar - AgoraBD : TDateTime;

Please check the params properties from the object inspector and verify that DataHoraAlt parameter has the correct values.

datatype = ftDateTime
paramtype = ptInput

Then, change the delphi code to:

Código Delphi [-]
 
   qryProtocolo.FieldByName('DataHoraAlt').Value := AgoraBD;

Regards

[farmond]

Caro egostar,

I've tried your tip, but it doesn't work - the error is not on the code, due on other forms that references to smaller tables it works fine - I think that the problem is on the BDE connection.

Do you have any tip to change the BDE parameters, or a BDE version that works fine?

I'm using Delphi 7.0 build 4.453, BDE Version 5.01, MS-SQL 2000 8.00.194

[[egostar]]

Sorry farmond, I have a mistake, I thought that you were using ParamByName insted of FieldByName.

Are you sure that the record you are trying to update is the correct one?

So, I'll have to keep a close eye on this issue in order to see if I can help you.

Regards.

[[egostar]]

Hi farmond

Can you change the RequestLive property from your qryProtocolo component to True?

Let me know what happends

Regards.

[farmond]

Caro egostar,

I've used Step by step debugging to find the error - the pointer is setting to the edited record. I've passed by all the lines of the code, and error has ocurred just in this event:

qryProtocolo.Post;

I'm using this code as default to other applications that works with smaller tables, and it's working fine.
I've wrote this same code using ADO connection, and it's worked fine.
So, I should tell tou that the code is right - the trouble occurs in the BDE connection - and it's very difficult to finda a solution.

I'm posting to this forum because I've found interesting articles here.
Sorry if I'm typing in enlglish, but I don't know how to write anything in Spanish.

[[egostar]]

Hi farmond,

Check this link

Regards

[[eduarcol]]

A ver lo coloco en español y cualquier cosa me ayudan

Se me ocurre que esa tabla debe estar siendo trabajada en red, por lo tanto algun otro usuario modifico el registro antes del post y se genera esa excepcion, es lo que estoy entendiendo de la traduccion del error.

Como el amigo no escribe mas codigo en el que esta la apertura y edicion de la tabla es muy dificil imaginar que puede suceder entre el edit y el post.

Empezado por traductor de google

My english is very bad, excuse me

It is happened to me that that table must be being worked in network, therefore some other user modify the registry before post and that exception is generated, is what I am understanding of the translation of the error.

As the friend does not write but code in which this the opening and edition of the table is very difficult to imagine that it can happen between edit and post.

[farmond]

Caro egostar,
the link you've send me is listed above, comments in red:
<<
Dear All,
I am porting a Delphi6 & BDE Paradox project onto MySQL 4.1 Server. To access MySQL, I am using BDE & MyODBC 3.51.12

SIMPLY
1. Set "return mactching rows" to enabled. It's MySQL property.
2. Alter your table(s) so there are enough number of unique fields(I altered one field as primary key). I just have 1 primary key


DETAILED
STEP 1. I got same error message. Then, I checked the table which error occurs. I set the "Return matching rows" to checked (in system data sources ->Configure->Advanced->Flags1 /3.51.12.00).

Then I saw that error message changed (to something like "more records when expecting only one). So, I checked my table. There were a lot of dublicate records. No, I had no primary, unique, index, etc. to prevent this situation on SQL server side, but I have code to prevent in client side (A code simply using TDataSet Find method). I understood that Find method does not work well.

STEP 2. I deleted the duplicatd records and alterer one of fields in table as primary key. Ooops, the error message was gone No duplicate records
>>

I'd like to know if someone has an ODBC version newer then 3.525.1117.0 (for Win2K / XP)

[[Gabo]]

Empezado por eduarcol

Se me ocurre que esa tabla debe estar siendo trabajada en red, por lo tanto algun otro usuario modifico el registro antes del post y se genera esa excepcion, es lo que estoy entendiendo de la traduccion del error.

Creo lo mismo, ese error me parece un problema de concurrencia...

Perdonen si digo una tontera, pero ¿no es un problema de la configuración de la transacción? Por ejemplo, en el caso de usar un componente IBTransaction, ¿no se solucionaría estableciendo la propiedad Read Comitted?

[[eduarcol]]

Empezado por Gabo

Creo lo mismo, ese error me parece un problema de concurrencia...

Perdonen si digo una tontera, pero ¿no es un problema de la configuración de la transacción? Por ejemplo, en el caso de usar un componente IBTransaction, ¿no se solucionaría estableciendo la propiedad Read Comitted?

Seria maravilloso pero esta trabajando con el BDE