BorlandTalk.com

magnifico · Guest

I get that error when i try to change any field of a table, what is
it?????? thanks

--- posted by geoForum on http://delphi.newswhat.com

Bill Todd · Guest

magnifico wrote:

magnifico · Guest

Hi
I tried what you suggestes for but i still get the error...The table has
a foreign key associated to the primary key of other table...Any ideas?
I'm new to databse programming, maybe i'm doing a very basic mistake.

Bill Todd · Guest

The table _must_ have a primary key. The primary key is the field or
combination of fields that uniquely identifies each row in the table.

--
Bill Todd (TeamB)

magnifico · Guest

I solver the problem setting the opallowmultirecordupdates property in
the DataSetProvider but look what is happening now:

the tables has 3 columms:
1 - A FOREIGN KEY
2 - A DATE
3 - a BLOB

I'm updating/editing only the blob of one record but when i update it
all other records that have the same foreign key and date became copies
of the record that i've just updated.I want to update only one record..!

--- posted by geoForum on http://delphi.newswhat.com

magnifico · Guest

thanks , everything working now

magnifico · Guest

i thought everything was working but it is not, after the changes that
solved the previous problem, 2 new problems came on the same table:

1 - I do insert a new record, it's ok.Then i insert a second one and it
says key violation ( i have a generator as the primary key, i did a
generator for my other table in the same way and i have no problems )

2 - I can update any record except the one that i have just created, if
i try to update, it says "Record not found or Changed by Another User"

magnifico · Guest

One observation, the insert problem only happens if trying to insert
more than one record that have the same foreign key and i can insert as
many records as i want with the same foreign key as far as i restart the
application, iserting one per execution.

--- posted by geoForum on http://delphi.newswhat.com

Bill Todd · Guest

What database are you using?

Please post the metadata for the table including the indices and
foreign keys. What we need to see is the CREATE TABLE and CREATE INDEX
statements for the table.

Please post the SELECT statement you are using?

--
Bill Todd (TeamB)

Bill Todd · Guest

Which Firebird driver are you using?

Do you have the pfInKey ProviderFlag set for PKEY on the SQLDataSet
(not the ClientDataSet)?

Do you have an OnReconcileError event handler for each CDS?

You should use ApplyUpdates(0) instead of -1. Normally there is no
reason to try other updates if one fails.

When you insert a record then insert another record without applying
updates how are you assigning a unique primary key value in the CDS? If
the CDS has a unique index on the primary key then each row you insert
must have a unique value.

--
Bill Todd (TeamB)

magnifico · Guest

I'm using Firebird 1.5 with dbexpress+clientdataset

The Metadata:

/* Table: HISTORICO_PACIENTES, Owner: SYSDBA */

CREATE TABLE "HISTORICO_PACIENTES"
(
"PKEY" INTEGER NOT NULL,
"CODIGO" INTEGER NOT NULL,
"DATA" DATE,
"DADOS" BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
PRIMARY KEY ("PKEY")
);
ALTER TABLE "HISTORICO_PACIENTES" ADD FOREIGN KEY ("CODIGO")
REFERENCES "PACIENTES" ("CODIGO") ON UPDATE CASCADE ON DELETE CASCADE;
SET TERM ^ ;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER "TRIGGER_HPACIENTE" FOR "HISTORICO_PACIENTES"
ACTIVE BEFORE INSERT POSITION 0
as
begin
New.Pkey=Gen_id(hpaciente_gen, 1);
end
^

COMMIT WORK ^
SET TERM ;^

This select the data that will be displayed:
(DataSet2 is the table with problems, DataSet1 is the other table that
is ok)

DataModule1.ClientDataSet2.Close;
paciente := DataModule1.ClientDataSet1.FieldByName('CODIGO').AsInteger;
DataModule1.SQLDataSet2.CommandText := 'SELECT * FROM
HISTORICO_PACIENTES WHERE CODIGO=:c_paciente ORDER BY DATA DESC';
DataModule1.SQLDataSet2.ParamByName('c_paciente').asinteger := paciente;
DataModule1.ClientDataSet2.Open;

And this is what i use to add a new record:

DataModule1.ClientDataSet2.Insert;

and then

DataModule1.ClientDataSet2.ApplyUpdates(-1);

The same for the Update but using DataModule1.ClientDataSet2.Edit;

--- posted by geoForum on http://delphi.newswhat.com

Bill Todd · Guest

The InterBase driver is not tested against and does not support
Firebird. It make work now but it is not likely to work with future
versions of Firebird.

You _must_ have an OnReconcileError event handler. Use the
ReconcileError Dialog in the Object Repository for a start. Read the
comments at the beginning of the unit file for instructions on how to
use it.

--
Bill Todd (TeamB)

magnifico · Guest

I have the ProviderFlag set as you said. I noticed another thing now,
if everytime before inserting or editing i call all that code where the
select statement is located, there is no error, i can edit and insert
with no problems.

About your question, i don't know what you mean with "assigning a unique
primary key value in the CDS"

--- posted by geoForum on http://delphi.newswhat.com

magnifico · Guest

On my SQLConnection it says "DriverName: Interbase",
i have not done anything in the OnReconcileError event yet

--- posted by geoForum on http://delphi.newswhat.com

magnifico · Guest

Hi,
after many tries i solved the problem executing ClientDataSet.Refresh;
right after every ApplyChanges used to insert data.(it was not needed
for edit)
Do you think it's ok?Is not it a bad pratice? thanks