Cannot set Column <name> to be null. Please use DBNull instead.
Using MySQL for Excel 1.2.0 to append excel-generated data to a MySQL table. But I'm unable to resolve errors when a value is null. The Excel formula will populate the cell with a number in most cases (and the cell number category is "number"). But sometimes it needs to be a null value, whereupon the formula sets it to "" which is effectively blank or null.
In prior versions of MySQL for Excel (sorry don't remember which version) the solution was to set the field value to NULL and everything worked. Now it doesn't.
If I use NULL the error is "Input string was not in a correct format. Couldn't store <NULL> expected type is int64." I've tried changing the excel number category to General, Number, Text and Scientific and they all fail. I've also tried unchecking "Use formatted values" in the Advanced Options tab.
If I try to pass a blank value, the error is "Input string was not in a correct format. Couldn't store <> expected type is int64."
In one of the various permutations of options I tried, the error message is "Cannot set column <name> to be null. Please use DBNull instead."
The field I'm trying to populate *is* nullable. Btw, it cannot be set to 0 because another table is dependent on this field either being NULL or a number that corresponds to an existing record elsewhere.
Is there a solution for this? All I can find in web searches about this problem is VBA solutions, and I'm using your tool to avoid having to write code for this transfer!
EDIT: I updated to MySQL for Excel 1.2.1 on the off-chance it would solve my problem. Didn't expect it to, because there's no mention of this type of issue in the release notes. But it was worth a shot. Still no joy, though....
Edited 2 time(s). Last edit at 03/27/2014 02:17PM by Matt Meridius.
Subject
Views
Written By
Posted
Cannot set Column <name> to be null. Please use DBNull instead.
6275
March 26, 2014 08:02PM
1881
March 27, 2014 12:36PM
1722
March 27, 2014 02:28PM
1767
March 27, 2014 08:26PM
2607
March 28, 2014 12:00PM
1712
March 27, 2014 02:19PM
2080
April 07, 2014 11:08AM
1598
April 07, 2014 02:39PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.