How to insert blank data in tclientdataset?

Question

This is a simple database.

CREATE TABLE A(FIELD1 INT PRIMARY KEY, FIELD2 VARCHAR(10));  
INSERT INTO A (FIELD1, FIELD2) VALUES (1, 'A');

tbl1 is opened and it contains the data in table A. I want to insert blank data, 6 records with blank data, null data in the Tclientdataset for make labels. In this example i get an error for 'Key violation'.

  procedure TForm1.btn1Click(Sender: TObject);

var
  i:Integer;
  cdsEti:TClientDataSet;
  dtstprvEti:TDataSetProvider;
  iNroEspaciosBlanco: Integer;
begin
  iNroEspaciosBlanco := 6;
  dtstprvEti := TDataSetProvider.Create(nil);
  cdsEti:= TClientDataSet.Create(nil);
  dtstprvEti.DataSet := tbl1;

  cdsEti.Data := dtstprvEti.Data;
  dtstprvEti.Constraints := False;
  cdsEti.ReadOnly := False;
  for i := 0 to (cdsEti.Fieldcount-1) do
  begin
    cdsEti.fields[i].ReadOnly := false;
    cdsEti.Fields[i].Required := false;
    cdsEti.FieldDefs[i].Attributes := [];
  end;
  cdsEti.Active := True;
  cdsEti.First;
  for i := 1 to iNroEspaciosBlanco do
  begin
    cdsEti.Insert;
    cdsEti.Post;
  end;
  FreeAndNil(cdsEti);
  FreeAndNil(dtstprvEti);
end;

dfm contains this. Simply a button, a connection and a table.

object Form1: TForm1
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object btn1: TButton
    Caption = 'btn1'
    TabOrder = 0
    OnClick = btn1Click
  end
  object con1: TADOConnection
    Connected = True
    Provider = 'SQLOLEDB.1'
  end
  object tbl1: TADOTable
    Connection = con1
    TableName = 'A'
  end
end

Does cdsEtiquetas have any indexes defined on it and if so what are they? Is cdsEtiquetas a "stand-alone" CDS or does it have any connections, e.g. to a DataSetProvider or is it in a master-detail relationship? — MartynA, Feb 25 '20 at 09:09
No, the use of this clientdataset is only for this porpouse. Not master-detail or any conexions with any table. I've seen the index, there is a index for CODIGO_ARTICULO called DEFAULT_ORDER that is inherit from the dataset but not ixprimary or unique. In previous tests i've checked to use this cdsEtiquetas.IndexDefs.Clear but not runs. — Alejandro Laorden, Feb 25 '20 at 10:15
You've defined a primary key,which means it can't be null. There should be no need to insert blank rows for labels. Do that in your presentation (user interface or report), not in the table itself. The table should only contain actual data that complies with it's constraints. — Ken White, Feb 26 '20 at 13:38
Preciselly its the intention to use the TclientDataset as a Datasource of the report. I dont want to insert blank data in the table but yes in the TClientDataSet. The porpouse is for save in the labels... You could use one but reuse the rest of the labels. — Alejandro Laorden, Feb 27 '20 at 12:11

MartynA · Answer 1 · 2020-02-25T10:37:36.143

I think your problem must be caused by a detail of your project which you have not mentioned in your q, because I cannot reproduce your problem in a minimal test project.

1 I created a test table, ClientCodes, on an MS Sql Server 2014, with this definition

CREATE TABLE [dbo].[clientcodes](
  [ClientCode] [int] NOT NULL,
  [Name] [varchar](40) NULL,
PRIMARY KEY CLUSTERED
(
  [ClientCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I then inserted 3 test rows

1  One
2  Two
3  Three

2 I then created a new Delphi project containing an AdoConnection to the Sql Server, an AdoQuery which does a 'select * from ClientCodes', a DataSetProvider connected to the AdoQuery and the unqryCliPrint CDS connected to that and a second CDS named cdsEtiquetas.

Note: All the properties of the components were left at their defaults apart from the ones necessary to connect up the components as described above.

The only code in the project is this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  unqryCliPrint.Open;
  cdsEtiquetas.Data := unqryCliPrint.Data;

  cdsEtiquetas.InsertRecord([0, 'zero']);
  cdsEtiquetas.InsertRecord([0, 'zero']);
  cdsEtiquetas.InsertRecord([0, 'zero']);
end;

As you can see, this inserts 3 copies of the same row. The code executes correctly, without raising any kind of exception or error.

I suggest you create a similar project using your server. If you get the same result, you will need to try to identify what it is in your actual project which causes the error.

Btw, there seems to be an error in your code:

  cdsEtiquetas.DisableControls;
  cdsEtiquetas.DisableConstraints;

I cannot see anywhere where you undo these steps.

Update From the DFM you have added, I see that your cdsEtiquetas is connected to something else, namely to dtstprvEtiquetas, despite what you said in your comment. And this DSP is connected back to unqryCliPrint! So I imagine this is where your error is coming from. If cdsEtiquetas doesn't need a DSP, and you haven't shown any reason why it does, just clear its ProviderName property.

The problem is not insert redudance data. Its when I insert a blank data. — Alejandro Laorden, Feb 25 '20 at 20:03
Considering the time I spent looking into this, I have to say I don't find the comment "Its when I insert a blank data." at all helpful, especially when your example attempts to insert a row with the ClientCode set to zero, which is precisely what my code does. In any case, I only included the second and third calls to InsertRecord to show that no error occurs when inserting duplicated rows - my first call to InsertRecord shows that contrary to your example, no error occurs on inserting the first row. — MartynA, Feb 25 '20 at 20:10
Btw, my code still works **without** error if I specify Null as the ClientCode, rather than zero. Like I said, clear your `cdsEtiquetas`'s `ProviderName` property and see whether you still get the error you reported. — MartynA, Feb 25 '20 at 20:16
"i cannot reproduce your code" What do you mean by that? Have you done what I said about the ProviderName? — MartynA, Feb 25 '20 at 21:03
the code with minimum data, with a simple table (only two fields) and a button in a form. — Alejandro Laorden, Feb 25 '20 at 21:21

score 0 · Answer 2 · answered Feb 26 '20 at 12:00

When I create a table in Firebird, I automatically have a "not null" for index - it's DDL for this table:

CREATE TABLE A (
    FIELD1  INTEGER NOT NULL,
    FIELD2  VARCHAR(10)
);
ALTER TABLE A ADD PRIMARY KEY (FIELD1);

So - index value can't be null and also must be UNIQUE!

There is an info from the web:

"A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields".

I'm trying to import the data of the table in a Tclientdataset and deactivate the constraints (primary key is a constraint) for manipulate it and insert blank data. — Alejandro Laorden, Feb 27 '20 at 11:23

How to insert blank data in tclientdataset?

2 Answers2