Error on Recordset update after addnew saying a column cannot be null even with a default set to empty string

Question

I've got a program that i've been supporting that recently was installed on a windows 7 machine. Everything has been working however recently this machine has started Receiving the following error when using the recordset.update method after modifying fields i've received the following error.

-2147467259 ([MySQL][ODBC 3.51 Driver][mysqld-4.0.18-nt]Column 'fd_number' cannot be null)

The table 'fd_number' is in looks like this:

CREATE TABLE `bookguide` (
`author` varchar(100) NOT NULL default '',
`title` varchar(100) NOT NULL default '',
`isbn` varchar(13) NOT NULL default '',
`whls` decimal(10,2) NOT NULL default '0.0000',
`oldwhls` decimal(10,2) NOT NULL default '0.0000',
`fd_number` varchar(100) NOT NULL default '',
KEY `isbn` (`isbn`)
) TYPE=MyISAM; 

The code that modifies this table looks like this:

780     Do While Not txtstream.AtEndOfStream
790         Line = txtstream.ReadLine

800         rs.addNew
            'tempwhole = Mid$(line, 2, 9) + Mid$(line, 119, 1)
810         author = Mid$(Line, 10, 15)
820         author = Replace(author, "'", "")
830         author = Replace(author, ",", "")
840         title = Mid$(Line, 25, 70)
850         title = Replace(title, "'", "")
860         title = Replace(title, ",", "")
            'tempisbn = Mid$(tempwhole, 2, 8)
870         rs.fields("isbn").Value = Replace(Trim$(Mid$(Line, 95, 13)), "-", "")
880         rs.fields("author").Value = author
890        rs.fields("title").Value = title
900        rs.fields("whls").Value = Val(Mid$(Line, 139, 5))    
910        rs.Update
920    Loop

This is just one example of where this is failing, There are most likely thousands of updates/inserts done this way through out the entire program.

I've attempted to reinstall of MySQL ODBC 3.51 Driver (3.51.27) several times, including using an older version (3.51.06), which takes away the ADO problem but introduces an error that says something to the effect of ODBC Driver does not support the requested Properties on this section of code right after the above loop.

940    rs.Open "UPDATE bookguide SET oldwhls = whls, whls = 0",conn

Answer 1

It looks like your default value of '' is not being applied on that field. Have you tried the MySQL ODBC driver 5.1.7 http://dev.mysql.com/downloads/connector/odbc/5.1.html?