NULL/EMPTY VALUE FOR PGSQLTIMESTAMP

NULL/EMPTY VALUE FOR PGSQLTIMESTAMP

Postby JORGEMAL » Sun 18 Oct 2009 22:42

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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Mon 19 Oct 2009 10:32

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;
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Mon 19 Oct 2009 19:31

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
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby JORGEMAL » Tue 20 Oct 2009 13:50

Shalex wrote: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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Wed 21 Oct 2009 15:39

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

Is that what you mean?
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 21 Oct 2009 16:53

We have removed your thread "HOW TO SET PgSqlTimestamp TO NULL" at this forum as duplicated.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Thu 22 Oct 2009 22:15

Shalex wrote: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.
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Fri 23 Oct 2009 10:08

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.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Fri 23 Oct 2009 15:44

Shalex wrote: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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Mon 26 Oct 2009 16:13

I didn't receive your e-mail. Please contact us via http://www.devart.com/company/contact.html .
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Mon 26 Oct 2009 18:40

Shalex wrote: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
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby JORGEMAL » Wed 28 Oct 2009 14:51

JORGEMAL wrote:
Shalex wrote: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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Wed 28 Oct 2009 15:02

We have answered you by e-mail.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Wed 28 Oct 2009 18:11

Shalex wrote: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
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Thu 29 Oct 2009 08:20

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.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL