Problem while recovering null data to a Datatable in C#

Asked

Viewed 985 times

3

I am working with C# and the Postgresql Database, I have a field of type Date where it can be filled dd/mm/yyyy or null empty. the same problem refers to any field of the table, if it is empty, even if it may be, the error occurs.

When I then select to recover all the data and assign to a Datatable the following error occurs

An exception of type 'System.Data.ConstraintException' occurred in System.Data.dll but was not handled in user code

Additional information: Falha ao ativar restrições. Uma ou mais linhas contêm valores que violam as restrições non-null, unique ou foreign-key.

Method Used

public DataTable RetornaDT(string sSQL)
{
    DataTable dtDados = new DataTable();
    if (ConectaBanco())
    {
        _comando.CommandText = sSQL;
        dtDados.Load(_comando.ExecuteReader());
        DesconectaBanco();
    }
       return dtDados;
 }

Where I have the following table corresponding in the database

CREATE TABLE conta
(
  con_codigo serial NOT NULL,
  con_descricao character varying(60),
  con_valordaconta real,
  con_valoraserpago real,
  con_valorjapago real,
  con_formapagamento character varying(2),
  con_dinheiroouporc character varying(2),
  con_desconto real,
  con_juros real,
  con_jurosam real,
  con_multaporatraso real,
  con_datalancamento date,
  con_datavencimento date,
  con_datapagamento date,
  CONSTRAINT contas_pkey PRIMARY KEY (con_codigo)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE conta
  OWNER TO postgres;
c# postgresql date
  • 1

    Which SQL did you use to create the table in question? Are you sure you didn’t put any index in the field or put it as not null?

  • The error says that the non-null restriction of a foreing key has been violated, I think it would be interesting to post the classes involved.

  • I didn’t, because when I save con_datapayment with null, because the payment has not yet occurred it can save normally, in my view the problem is the non-null restriction when clicking to the Datatable, and with respect to classes, I just ask a Datatable, and I just do Datatable.Load by running a Reader to later use it on a Datagrid

  • Error not only consists of Date but any field that is empty

2 answers

1


The problem is the used version of npgsql, version 3.2 requires that the data is not null or empty, well I do not know why this occurs, but when returning to an earlier version of npgsql I was able to solve the problem.

0

Depending on the version used in Npgsql this error may occur, one way to solve this is to treat this in your sql, instead of you sending the field as null you can change it and send blank.

CASE WHEN DateColuna IS NULL THEN '' ELSE to_char(DateColuna, 'dd/mm/yyyy') END As Data

  • This even solves the problem, but has no other way to solve the error by Datatable itself?

  • Error not only consists of Date but any field that is empty

Browser other questions tagged c# postgresql date

You are not signed in. Login or sign up in order to post.