Page 1 of 1
Working with one record at a time
Posted: Tue 19 Jan 2010 00:37
by 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?
Posted: Tue 19 Jan 2010 10:13
by 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.
Posted: Wed 20 Jan 2010 00:05
by 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?
Posted: Wed 20 Jan 2010 05:31
by tobias_cd
Have you set (TUniQuery) KeyFields := 'ID' ?
Posted: Wed 20 Jan 2010 09:06
by Plash
Which provider are you using? Some providers require setting the DMLRefresh property to True to get new values from the server.
Posted: Wed 20 Jan 2010 18:01
by 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().
Posted: Wed 20 Jan 2010 18:07
by 99Percent
tobias_cd wrote:Have you set (TUniQuery) KeyFields := 'ID' ?
Yes
Posted: Thu 21 Jan 2010 02:05
by 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
Posted: Thu 21 Jan 2010 14:03
by 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.
Posted: Thu 21 Jan 2010 19:05
by 99Percent
Thanks Dimon that made it work.
Why doesn't just setting the 'id' parameter to in/out in the TUniquery properties work?
Posted: Fri 22 Jan 2010 14:38
by Dimon
You can try to set ParamType of the ID parameter to ptOutput.