Working with one record at a time

[99Percent]

I want to add or edit one record at a time only, from a very large table, initially opening the table to a new record. Also this table has an autoincrement field and a detail table.

What would be the correct way to set this up using UniDAC?

[Plash]

Use a SQL statement with WHERE condition:

SELECT * FROM MyTable
WHERE id = :id

Set the ID parameter to ID of a record that you want to edit. Then open or refresh the query. If you need to add a new record, you can set the parameter to Null.

[99Percent]

Thanks for replying.

I tried doing what you suggested and I run into the following problem: when I programmatically post the record (so it can go on to edit the detail table), it doesn't update the 'id' field (which is an autoincrement field). I tried RefreshRecord right after Post but I get the following error: EDatabaseError with message 'Refresh failed. Found 0 records'. Obviously it is trying to refresh with the WHERE clause, with the id parameter value not current.

How to fix this?

[tobias_cd]

Have you set (TUniQuery) KeyFields := 'ID' ?

[Plash]

Which provider are you using? Some providers require setting the DMLRefresh property to True to get new values from the server.

[99Percent]

Thanks for your pointers, I am figuring out what is wrong.

I am using MSSQL.

SQL:
SELECT [id], ClienteId, Fecha, PreviewsMes, Tipo, Usuario, Pendiente, Observaciones
FROM tblPedidosHd2
where [id]=:id

Test 1:
no SQLInsert property
DMLRefresh set to false:
PASS

Test 2:
Same SQL
Custom SQLInsert generated with DMLRefresh=False
INSERT INTO tblPedidosHd2
(ClienteId, Tipo, PreviewsMes, Fecha, Usuario, Pendiente, Observaciones)
VALUES
(:ClienteId, :Tipo, :PreviewsMes, :Fecha, :Usuario, :Pendiente, :Observaciones)
SET :id = SCOPE_IDENTITY()

FAIL

Test 3:
Custom SQLInsert generated with DMLRefresh=True
INSERT INTO tblPedidosHd2
(ClienteId, Tipo, PreviewsMes, Fecha, Usuario, Pendiente, Observaciones)
VALUES
(:ClienteId, :Tipo, :PreviewsMes, :Fecha, :Usuario, :Pendiente, :Observaciones)
SET :id = SCOPE_IDENTITY()
SELECT :ClienteId = ClienteId, :Tipo = Tipo, :PreviewsMes = PreviewsMes, :Fecha = Fecha, :Usuario = Usuario, :Pendiente = Pendiente FROM tblPedidosHd2
WHERE
id = :Old_id
FAIL

Apparently I cannot use the SQLInsert property, which I do need because I need to insert server supplied values like Getdate().

[99Percent]

Have you set (TUniQuery) KeyFields := 'ID' ?

Yes

[99Percent]

for your reference this is the table definition

Code: Select all

CREATE TABLE [tblPedidosHd2] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[ClienteId] [int] NOT NULL ,
	[Tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PreviewsMes] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fecha] [datetime] NULL ,
	[Usuario] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Pendiente] [bit] NULL ,
	[Observaciones] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_tblPedidosHd2] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_tblPedidosHd2_tblClientes2] FOREIGN KEY 
	(
		[ClienteId]
	) REFERENCES [tblClientes2] (
		[id]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[Dimon]

To solve the problem you should set ParamType of the ID parameter to ptInputOutput in the TUniQuery.BeforeUpdateExecute event handler, like this:

Code: Select all

Params.ParamByName('id').ParamType := ptInputOutput; 

Also set the TUniQuery.Options.ReturnParams property to True.

[99Percent]

Thanks Dimon that made it work.

Why doesn't just setting the 'id' parameter to in/out in the TUniquery properties work?

[Dimon]

You can try to set ParamType of the ID parameter to ptOutput.