Dataset can't lock row for edit or delete

[nrsgzz]

Hi.
I have a fairly intricate query in a DOA dataset connected to a devexpress TcxDBGrid.
Even though ROWID for the editing table is selected as first column and I have set the UpdatingTable property, it still doesn't work ("Record has been changed by another user").

This is the query (TAB_A is the table I need to work on):

SELECT
	TAB_A.ROWID,
	TAB_A.ID_LAVORO,
	TAB_A.ID_DISPOSIZIONE,
	TAB_A.ID_TARIFFA,
	TAB_A.ID_TIPO_TURNO,
	TAB_A.ID_MANSIONE,
	TAB_A.ID_IMPRESA,
	TAB_A.ID_TIPO_OPERAZIONE,
	TAB_B.ID_MANDATO AS RIF,
	'DISPOSIZIONE' AS TIPO_RIF,
	TAB_B.ID_POSIZIONE,
	TAB_B.ID_MERCE,
	TAB_C.NOME AS NOME_MERCE,
	TAB_D.NOME AS IMBALLAGGIO,
	TAB_B.NOME_VETTORE,
	TAB_B.NUMERO_40FT,
	TAB_B.NUMERO_20FT,
	TAB_B.TIPO_LAVORO,
	TAB_B.TONNELLATE,
	TAB_B.COLLI,		
	TAB_A.PESO,
	TAB_A.QUANT,
	TAB_A.CNTR_40FT,
	TAB_A.CNTR_20FT,
	TAB_B.DATA,
	TAB_B.NOTE,
	TAB_E.NOME_IMPRESA,
	TAB_F.NOME AS NOME_TARIFFA,
	TAB_H.NOME AS NOME_OPERAZIONE,
	TRUNC(TAB_A.DATA_INIZIO) AS DATA_INIZIO,
	TRUNC(TAB_A.DATA_FINE) AS DATA_FINE,
	TAB_A.ORE_PESO,
	TAB_A.ORE_ASSISTENZA,
	TAB_A.ORE_CARRELLO_1,
	TAB_A.ORE_CARRELLO_1 * TAB_G.TARIFFA_CARRELLO_1 AS COSTO_CAR1,
	TAB_A.ORE_CARRELLO_2,
	TAB_A.ORE_CARRELLO_2 * TAB_G.TARIFFA_CARRELLO_2 AS COSTO_CAR2,
	TAB_A.ORE_CARRELLO_3,
	TAB_A.ORE_CARRELLO_3 * TAB_G.TARIFFA_CARRELLO_3 AS COSTO_CAR3,
	TAB_A.NUMERO_PESO,
	TAB_A.FATTURA,
	TAB_F.TARIFFA AS COSTO_TARIFFA,
	(
		CASE WHEN
			TAB_F.TIPO_TARIFFA = 1
		THEN
			NVL(((TAB_A.ORE_PESO+TAB_A.ORE_ASSISTENZA)*TAB_F.TARIFFA), 0)
		ELSE
			NVL(((TAB_A.NUMERO_PESO/1000)*TAB_F.TARIFFA), 0)
		END
	) AS TOTALE_TARIFFA_ORE,
	(
		(TAB_A.ORE_CARRELLO_1 * TAB_G.TARIFFA_CARRELLO_1) +
		(TAB_A.ORE_CARRELLO_2 * TAB_G.TARIFFA_CARRELLO_2) +
		(TAB_A.ORE_CARRELLO_3 * TAB_G.TARIFFA_CARRELLO_3)
	) AS TOTALE_TARIFFA_CARRELLO,
	(
		(
		CASE WHEN
			TAB_F.TIPO_TARIFFA = 1
		THEN
			NVL(((TAB_A.ORE_PESO+TAB_A.ORE_ASSISTENZA)*TAB_F.TARIFFA), 0)
		ELSE
			NVL(((TAB_A.NUMERO_PESO/1000)*TAB_F.TARIFFA), 0)
		END
		)
		+
		(
			(TAB_A.ORE_CARRELLO_1 * TAB_G.TARIFFA_CARRELLO_1) +
			(TAB_A.ORE_CARRELLO_2 * TAB_G.TARIFFA_CARRELLO_2) +
			(TAB_A.ORE_CARRELLO_3 * TAB_G.TARIFFA_CARRELLO_3)
		)
	) AS TOTALE_TARIFFA
FROM
	TAB_A,
	TAB_B,
	TAB_D,
	TAB_H,
	TAB_E,
	TAB_F,
	TAB_C,
	TAB_G
WHERE
	TAB_B.ID_MANDATO = TAB_A.ID_RIFERIMENTO
AND
	TAB_A.TIPO_RIFERIMENTO = 1
AND
	TAB_G.ID_TARIFFA_CARRELLO = 1
AND
	TAB_C.ID_MERCE = TAB_B.ID_MERCE
AND
	TAB_A.ID_TARIFFA=TAB_F.ID_TARIFFA
AND
	TAB_A.ID_TIPO_OPERAZIONE=TAB_H.ID_OPERAZIONE
AND
	TAB_F.ID_IMPRESA=TAB_E.ID_IMPRESA
AND
	TAB_D.ID_IMBALLAGGIO = TAB_B.ID_IMBALLAGGIO

Any clues?

 

[Marco Kalter]

The problem is most likely caused by an expression like this:

TRUNC(TAB_A.DATA_INIZIO) AS DATA_INIZIO

This modifies the field data, so that it appears that the information in the dataset does not match the data in the database table.

To fix this, include roAllFIelds in the TOracleDataSet.RefreshOptions property. See User's Guide for more information.

 

[nrsgzz]

I haven't tested the solution yet but do you thing using oracle's to_char() instead of trunc() will present the same issue?

 

[Marco Kalter]

It will present the same issue. You can alternatively use a column alias with a different name than the column itself to fix it.