MySQL Bugs: #3855: can't update varchar field through an ADO recordset

Bug #3855	can't update varchar field through an ADO recordset
Submitted:	22 May 2004 2:59	Modified:	11 Feb 2005 19:21
Reporter:	Adrian Sandor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.07	OS:	Windows (Windows XP)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
I'm using ADO 2.8, MyODBC 3.51.07 and MySQL 4.0.18.
I'm working with this table:

#
# Table structure for table `actions`
#

CREATE TABLE `actions` (
  `ID` int(11) NOT NULL auto_increment,
  `Task` int(11) NOT NULL default '0',
  `Done` int(3) default NULL,
  `Date` date NOT NULL default '0000-00-00',
  `Comment` varchar(100) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `Task` (`Task`)
) TYPE=InnoDB AUTO_INCREMENT=58 ;

The only thing that matters is the Comment field. Whenever I try to update this field in a record, through a recordset, I get an error.
There are 4 cases:

1. field was null, I set it to null

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Invalid string or buffer length

2. field was null, I set it to a string

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

3. field had a string value, I set it to null

In this case, it works. ONLY in this case!

4. field had a string value, I set it to a string

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

How to repeat:
//javascript asp
//first, open the ADO connection "cnn" using a system dsn, then:
var rst=Server.CreateObject("ADODB.Recordset");
rst.Open("SELECT * FROM actions WHERE ID=1", cnn, 1 /*adOpenKeyset*/, 2 /*adLockPessimistic*/);
//(supposing we have a field with ID=1)
rst.Fields("Comment").Value=null; //or  ="test";
rst.Update();

Please test with 3.51.08 or .09, and if still fails, attach testcase to this bug report.

I tested with 3.51.09 and there is no difference.
The test case is identical, is there any other information you need?
(And btw, why don't you test it on your side?)

I have solved a similar problem adding in the options "Don't optimize columns Width" value=1
Now my VB program with ADO(2.8) and myODBC(3.51.09) work fine with edit and update all the fields.

I have this problem too:

> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value,
if available. No work was done

I am using mysql 4.1.7 and ODBC 3.51.10 in a windows 2003 environment. It is strange, because it works in another application, but not in everyone.

Has somebaody still a solution?

I 've found the solution in a forum and have added "Option = 3" to my connection String and now it works.

-> cApplication="ODBC database; Option = 3"
Conn.Open "DSN="&cApplication

Thanks guys for the input :)
I remember that I knew (some long time ago) that I had to select the 2 options "Don't Optimize Column Widths" and "Return Matching Rows" when creating the DSN. However, when I tested (before submitting the bug), somehow it didn't work, and I removed them.
So I decided to try again now; I selected those options and tried modifying a field - same error. BUT after restarting the MySQL and w3svc services, it worked! (amazing...)
When I saw that "Option = 3" suggestion, I was intrigued and I tried to find out what it does. And I found http://www.dwam.net/mysql/asp_myodbc.asp which shows that "Option = 3" is the same thing as selecting the two checkboxes I mentioned above!

Well, I'm glad we sorted out this problem :) Now I can think about doing more advanced things with ADO and MySQL.
I'm not sure if it still qualifies as a bug; if not then moderators feel free to close it.

Support - please consider Closing.