NULL/EMPTY VALUE FOR PGSQLTIMESTAMP

[JORGEMAL]

I have a field of type PgSqlTimestamp in which I save date information but sometimes such a field must be empty until it is later modified.
How can I assign a field of this type an empty/null value?

Respectfully,
Jorge Maldonado

[Shalex]

You can assign null values to the fields of your date column if the definition for this column doesn't contain the 'NOT NULL' option. The table can be modified with the following SQL:

Code: Select all

ALTER TABLE yourTableName ALTER COLUMN dateColumn DROP NOT NULL;

[JORGEMAL]

If I have a property

public static PgSqlTimeStamp FechaDec { get; set; }

How do I set it to null?

FechaDec = new PgSqlTimeStamp() without parameters return 01/01/0001.

Respectfully,
Jorge Maldonado

[JORGEMAL]

You can assign null values to the fields of your date column if the definition for this column doesn't contain the 'NOT NULL' option. The table can be modified with the following SQL:

Code: Select all

ALTER TABLE yourTableName ALTER COLUMN dateColumn DROP NOT NULL;

If I have a property:
public static PgSqlTimeStamp FechaDec { get; set; }
How do I set it to null?

FechaDec = new PgSqlTimeStamp() without parameters return 01/01/0001.

Respectfully,
Jorge Maldonado

[Shalex]

Try setting your FechaDec property to null in this way:
FechaDec = PgSqlTimeStamp.Null;

Is that what you mean?

[Shalex]

We have removed your thread "HOW TO SET PgSqlTimestamp TO NULL" at this forum as duplicated.

[JORGEMAL]

Try setting your FechaDec property to null in this way:
FechaDec = PgSqlTimeStamp.Null;

Is that what you mean?

I am still in problems with null dates. I followed your suggestion but I get an exception at the time of inserting the record to the table.

I first detect if the date control in my web form is null. If it is then I assing the value PgSqlTimestamp.Null to the property that represents such a control, otherwise I assign the date normally.

After this, I build the query string that will insert the record where the date field is enclosed within single quotes whether it is null or not.

Finally, I perform the insert operation and I get the exception message shown below.

invalid input syntax for type date: ""
PgSqlException
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Artista.Inserta(String& strTipoMensaje, Int16& intTotalMensajes, String[,] arrMensajes, String strOrigen)


I also made a test detecting if the field was null at the time of building the query string and, if it was, I did not include single quotes but I also got an exception. It says *** at or near ")" *** which is precisely where the date field goes.

syntax error at or near ")" at character 269
PgSqlException
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Artista.Inserta(String& strTipoMensaje, Int16& intTotalMensajes, String[,] arrMensajes, String strOrigen)

Everything works pretty fine when there is no null date value.
Thanks in advance for your advice.

[Shalex]

I have tried the following code with the 4.55.49 version of dotConnect for PostgreSQL. It works.

Code: Select all

--script of the table I used
CREATE TABLE datetable
(
  id integer,
  datecolumn date
);
================
//code
class Program {
    static PgSqlTimeStamp time = new PgSqlTimeStamp();
    public static PgSqlTimeStamp FechaDec {
      get { return time; }
      set {time = value; }
    } 

    static void Main(string[] args) {
      string connStr = "host=***;port=***;uid=***;pwd=***;database=***;schema=***;";
      using (PgSqlConnection connection = new PgSqlConnection(connStr)){
        connection.Open();
        PgSqlCommand command = connection.CreateCommand();
        command.Parameters.Add("p1", PgSqlType.TimeStamp);
        FechaDec = PgSqlTimeStamp.Null;
        command.Parameters["p1"].Value = FechaDec;
        command.CommandText = "insert into datetable values (1,:p1)";
        command.ExecuteNonQuery();
      }
    }
  }

Please send us a small test project with the DDL script to reproduce the problem you have encountered.

[JORGEMAL]

I have tried the following code with the 4.55.49 version of dotConnect for PostgreSQL. It works.

Code: Select all

--script of the table I used
CREATE TABLE datetable
(
  id integer,
  datecolumn date
);
================
//code
class Program {
    static PgSqlTimeStamp time = new PgSqlTimeStamp();
    public static PgSqlTimeStamp FechaDec {
      get { return time; }
      set {time = value; }
    } 

    static void Main(string[] args) {
      string connStr = "host=***;port=***;uid=***;pwd=***;database=***;schema=***;";
      using (PgSqlConnection connection = new PgSqlConnection(connStr)){
        connection.Open();
        PgSqlCommand command = connection.CreateCommand();
        command.Parameters.Add("p1", PgSqlType.TimeStamp);
        FechaDec = PgSqlTimeStamp.Null;
        command.Parameters["p1"].Value = FechaDec;
        command.CommandText = "insert into datetable values (1,:p1)";
        command.ExecuteNonQuery();
      }
    }
  }

Please send us a small test project with the DDL script to reproduce the problem you have encountered.

I sent an email to AlexSh at devart*com with the information you asked. Please let me know if you need anything else.

Respectfully,
Jorge Maldonado

[Shalex]

I didn't receive your e-mail. Please contact us via http://www.devart.com/company/contact.html .

[JORGEMAL]

I didn't receive your e-mail. Please contact us via http://www.devart.com/company/contact.html .

I used the contact form and attached the sample code in a text file.

Regards,
Jorge Maldonado

[JORGEMAL]

I didn't receive your e-mail. Please contact us via http://www.devart.com/company/contact.html .

I used the contact form and attached the sample code in a text file.

Regards,
Jorge Maldonado

Did you receive the information I sent using the contact form you mention above?

Best regards,
Jorge Maldonado

[Shalex]

We have answered you by e-mail.

[JORGEMAL]

We have answered you by e-mail.

So, does this mean that you reviewed my code and it is supposed to be correct?

Respectfully,
Jorge Maldonado

[Shalex]

Are you sure that the following check in your code covers all possible incorrect input data?

Code: Select all

if (dteFechaDec.Value == null)
Artista.FechaDec = PgSqlTimeStamp.Null;
else
Artista.FechaDec = new PgSqlTimeStamp(dteFechaDec.Date.Year, dteFechaDec.Date.Month, dteFechaDec.Date.Day);

We recommend you to do the following:
1) execute our test application mentioned above to make sure that there is no problem with our provider;
2) debug your application as we recommended you by e-mail to localize the problem. This will allow us to reproduce the problem in our environment.