MySQL Forums
Forum List  »  MySQL for Excel

Cannot set Column <name> to be null. Please use DBNull instead.
Posted by: Matt Meridius
Date: March 26, 2014 08:02PM

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.

Options: ReplyQuote




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.