How to Update Date Field which is NULL

How to Update Date Field which is NULL

Postby Zero-G. » Thu 10 Apr 2008 13:45

Hey

I creaty dynamicly a Script with Insert Statements. This looks like:
Code: Select all
scrFile.ScriptText &= "Delete From IproHead where manufacturer_code='" & rHead(9).Feldwert & "';"
        scrFile.ScriptText &= String.Format("INSERT INTO `voptneu`.`iprohead` (`version`, " & _
        "`software_id`, `comment`, `uid_manufacturer`, `uid_postedit`, `valid_from`, " & _
        "`valid_until`, `country`, `language`, `manufacturer_code`, `manufacturer_subcode`, " & _
        "`manufacturer_name`, `manufacturer_subname`, `manufactuer_name_1`, " & _
        "`manufacturer_name_2`, `street`, `zip_code`, `city`, `po_box_zip_code`, " & _
        "`po_box_text`, `phone`, `fax`, `phone_order`, `fax_order`, `mail`, `url`, " & _
        "`pricedefinition_cylinder`, `cylindergroup_base`, `cylindergroup_1`, " & _
        "`cylindergroup_2`, `prismgroup_1`, `prismgroup_2`, `prismgroup_3`, " & _
        "`prismgroup_4`, `prismgroup_5`, `currencydescription`, " & _
        "`currencydescription_decimals`, `pricefield_01`, `pricefield_02`, `pricefield_03`, " & _
        "`pricefield_04`, `pricefield_05`, `pricefield_description_01`, " & _
        "`pricefield_description_02`, `pricefield_description_03`, `pricefield_description_04`, " & _
        "`pricefield_description_05`, `characterset`) VALUES (" & _
        "'{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', " & _
        "'{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', " & _
        "'{21}', '{22}', '{23}', '{24}', '{25}', '{26}', '{27}', '{28}', " & _
        "'{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}', '{38}', " & _
        "'{39}', '{40}', '{41}', '{42}', '{43}', '{44}', '{45}', '{46}', '{47}';)", _
        rHead(0).Feldwert, rHead(1).Feldwert, rHead(2).Feldwert, rHead(3).Feldwert, rHead(4).Feldwert, _
        rHead(5).Feldwert, rHead(6).Feldwert, rHead(7).Feldwert, rHead(8).Feldwert, rHead(9).Feldwert, _
        rHead(10).Feldwert, rHead(11).Feldwert, rHead(12).Feldwert, rHead(13).Feldwert, rHead(14).Feldwert, _
        rHead(15).Feldwert, rHead(16).Feldwert, rHead(17).Feldwert, rHead(18).Feldwert, rHead(19).Feldwert, _
        rHead(20).Feldwert, rHead(21).Feldwert, rHead(22).Feldwert, rHead(23).Feldwert, rHead(24).Feldwert, _
        rHead(25).Feldwert, rHead(26).Feldwert, rHead(27).Feldwert, rHead(28).Feldwert, rHead(29).Feldwert, _
        rHead(30).Feldwert, rHead(31).Feldwert, rHead(32).Feldwert, rHead(33).Feldwert, rHead(34).Feldwert, _
        rHead(35).Feldwert, rHead(36).Feldwert, rHead(37).Feldwert, rHead(38).Feldwert, rHead(39).Feldwert, _
        rHead(40).Feldwert, rHead(41).Feldwert, rHead(42).Feldwert, rHead(43).Feldwert, rHead(44).Feldwert, _
        rHead(45).Feldwert, rHead(46).Feldwert, rHead(47).Feldwert)

The two fields Valid From & Valid Until can have NO Data this means in the Values Statement are the fields like = '', ''
But there I get an error. - How to solve this problem? - What di I have to insert, that the Date can be NULL?

The field on the server has Allow Null activated.

THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Thu 10 Apr 2008 14:02

What exception do you receive?
Please post the table definition script.
Check in debug mode, that you have all valid data when inserting is being made.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Fri 11 Apr 2008 13:34

Hey

The error is: Incorrect date value: ' ' for column 'valid_until' at row 1
The Definition of the table is:
Code: Select all
- Tabelle: iprohead

-- DROP TABLE `iprohead`;

CREATE TABLE `iprohead` (
  `version`                       varchar(10),
  `software_id`                   varchar(40),
  `comment`                       varchar(200),
  `uid_manufacturer`              varchar(50) NOT NULL,
  `uid_postedit`                  varchar(50),
  `valid_from`                    date,
  `valid_until`                   date,
  `country`                       varchar(2),
  `language`                      varchar(2),
  `manufacturer_code`             varchar(3),
  `manufacturer_subcode`          varchar(3),
  `manufacturer_name`             varchar(40),
  `manufacturer_subname`          varchar(40),
  `manufactuer_name_1`            varchar(40),
  `manufacturer_name_2`           varchar(40),
  `street`                        varchar(40),
  `zip_code`                      varchar(8),
  `city`                          varchar(40),
  `po_box_zip_code`               varchar(8),
  `po_box_text`                   varchar(40),
  `phone`                         varchar(40),
  `fax`                           varchar(40),
  `phone_order`                   varchar(40),
  `fax_order`                     varchar(40),
  `mail`                          varchar(40),
  `url`                           varchar(40),
  `pricedefinition_cylinder`      varchar(1),
  `cylindergroup_base`            int(1),
  `cylindergroup_1`               int(1),
  `cylindergroup_2`               int(1),
  `prismgroup_1`                  int(2),
  `prismgroup_2`                  int(2),
  `prismgroup_3`                  int(2),
  `prismgroup_4`                  int(2),
  `prismgroup_5`                  int(2),
  `currencydescription`           varchar(3),
  `currencydescription_decimals`  varchar(3),
  `pricefield_01`                 int(2),
  `pricefield_02`                 int(2),
  `pricefield_03`                 int(2),
  `pricefield_04`                 int(2),
  `pricefield_05`                 int(2),
  `pricefield_description_01`     varchar(40),
  `pricefield_description_02`     varchar(40),
  `pricefield_description_03`     varchar(40),
  `pricefield_description_04`     varchar(40),
  `pricefield_description_05`     varchar(40),
  `characterset`                  int(2),
  `id`                            bigint AUTO_INCREMENT NOT NULL,
  /* Schlüssel */
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  CHARACTER SET `utf8` COLLATE `utf8_general_ci`;


THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Fri 11 Apr 2008 14:06

I am running the following script with MyDirect without such error:
Code: Select all
create table a(
id int auto_increment primary key,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');

Do you have an exception running this script?
Please post the version and edition of MyDirect .NET, MySQL server.
My testing configuration is:
MyDirect .NET Professional 4.50.26.0
MySQL server 5.1.11
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Fri 11 Apr 2008 14:17

The whole Exception =
CoreLab.MySql.MySqlException was unhandled by user code
ErrorCode=-2147467259
Message="Incorrect date value: ' ' for column 'valid_until' at row 1"
Source="CoreLab.Data"
StackTrace:
bei CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
bei CoreLab.Common.DbScript.Execute()
bei Ipro_v6.Form1.bWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\Dokumente und Einstellungen\Administrator\Desktop\VS2005\IPRO\Ipro v6\Ipro v6\Form1.vb:Zeile 532.
bei System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
bei System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

my MyDirect.NET = Professional 4.50.26.0
my Server = MySQL 5.0.45-community-nt
my Client = 5.1.11

I tried throug a 3rd Party programm. - There I get an error with your script:
The Create Statement = OK
The first Statement (with null) = OK
The second statement (with '') = ERROR
The third statement(with ' ') = ERROR
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Fri 11 Apr 2008 14:23

Do you recieve the error running this script with MySQLScript component?
Code: Select all
create table a(
id int auto_increment primary key,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Fri 11 Apr 2008 14:26

Yes, the same as in the 3rd Party programm
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Fri 11 Apr 2008 14:39

The problem is in MySQL server.
I've been testing the SQL script with MySQL Command Line Client
on MySQL 4.1.18 and MySQL 5.1.11 - no problem.
But on MySQL server 5.0.26 it throws the mentioned exception.
I suppose it happens with all MySQL 5.0.* versions.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Fri 11 Apr 2008 14:41

It seems. - I gonna download a newer version.

THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Fri 11 Apr 2008 14:43

Please let us know if the problem is solved.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Tue 15 Apr 2008 18:59

Hey

I do use Server 5.1.23 now. - But the error is still there when I try to update a DateField with Value = '' or Value = ' '

THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Wed 16 Apr 2008 08:05

Are you able to run such SQL script on the newer server with MySQL Command Line Client?
Code: Select all
create table a(
id int,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby Zero-G. » Wed 16 Apr 2008 08:13

Hey

No, I have tried on my third Party Programm which is designed for the mySQL. -

This sayed, that the query with null = OK, and the other two: You have an error in your mysql syntax...
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey.mdr » Wed 16 Apr 2008 08:39

Ok. So basically the issue is on the server side.
As a solution, I would suggest to check the date for empty values ('', '_', '__' ,...)
before insert, and replace them with null
if you'd prefer to stick to this version of MySQL server.
This additional check might slow down the application if it makes numerous inserts.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL