Dataset can't lock row for edit or delete

Forums Products Direct Oracle Access Dataset can't lock row for edit or delete

Print Thread

#49842 12/03/14 07:52 AM

Joined: Mar 2013

Posts: 8

nrsgzz

Member

nrsgzz

Member

Joined: Mar 2013

Posts: 8

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):

SQL Query

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?

Re: Dataset can't lock row for edit or delete nrsgzz #49843 12/03/14 09:43 AM
Joined: Aug 1999 Posts: 22,219 Marco Kalter Member
Marco Kalter Member Joined: Aug 1999 Posts: 22,219	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. Marco Kalter Allround Automations

Re: Dataset can't lock row for edit or delete Marco Kalter #49845 12/03/14 12:08 PM
Joined: Mar 2013 Posts: 8 N nrsgzz OP Member
OP nrsgzz Member N Joined: Mar 2013 Posts: 8	I haven't tested the solution yet but do you thing using oracle's to_char() instead of trunc() will present the same issue?

Re: Dataset can't lock row for edit or delete nrsgzz #49849 12/04/14 09:56 AM
Joined: Aug 1999 Posts: 22,219 Marco Kalter Member
Marco Kalter Member Joined: Aug 1999 Posts: 22,219	It will present the same issue. You can alternatively use a column alias with a different name than the column itself to fix it. Marco Kalter Allround Automations

Moderated by support

Link Copied to Clipboard