Bug #35556 .update fail with VB6 / DAO3.60

• Submitted: 25 Mar 2008 16:14
• Modified: 26 Mar 2008 9:28
• Reporter: Herve GAUMER
• Status: Not a Bug
• Category: Connector / ODBC
• Severity: S3 (Non-critical)
• Version: 03.51.23
• OS: Windows (XP)
• CPU Architecture: Any
• Tags: .update DAO 3.60

[25 Mar 2008 16:14] Herve GAUMER

Description:
As I try to modify a record containing double our float with the
.edit and .update method, with some values, the update fails.

How to repeat:
I just take the exemple
25.1.19.2. DAO : rs.addNew, rs.update et scrolls
and I modify the table creation: I add a double(7,2) at the end of the record.

 Set queryDef = conn.CreateQueryDef("", "create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, " _
     & "Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT, Dbl1 DOUBLE(7,2))")

I assign the value  0.99 to this field

For I = 10 To 15
rs.AddNew
rs!Name = "insert record" & I
rs!Id2 = I
rs!Dbl1 = 0.99
rs.Update

Next I

I modify the value of the double for the record id=3
' Dbl1 = Dbl1+1

' modification of the data of the record id=3

For I = 1 To 5

Set rs = conn.OpenRecordset("Select * from my_dao where Id=3", dbOpenDynamic, dbExecDirect, dbOptimistic)

rs.Edit
rs!Name = CStr(rs!Dbl1)
rs!Dbl1 = rs!Dbl1 + 1
rs.Update
rs.Close

Next I

-- Query logging
--
--  Driver name: MySQL ODBC 3.51 Driver  Version: 03.51.23
-- Timestamp: 080325 17:06:32

drop table if exists my_dao;
create table my_dao(Id INT AUTO_INCREMENT PRIMARY KEY, Ts TIMESTAMP(14) NOT NULL, Name varchar(20), Id2 INT, Dbl1 DOUBLE(7,2));
my_dao;
SELECT * FROM my_dao;
insert into my_dao (Name, Id2, Dbl1) values ('insert record10', 10, 9.89999999999999990e-001);
insert into my_dao (Name, Id2, Dbl1) values ('insert record11', 11, 9.89999999999999990e-001);
insert into my_dao (Name, Id2, Dbl1) values ('insert record12', 12, 9.89999999999999990e-001);
insert into my_dao (Name, Id2, Dbl1) values ('insert record13', 13, 9.89999999999999990e-001);
insert into my_dao (Name, Id2, Dbl1) values ('insert record14', 14, 9.89999999999999990e-001);
insert into my_dao (Name, Id2, Dbl1) values ('insert record15', 15, 9.89999999999999990e-001);
Select * from my_dao where Id=3;
update my_dao SET Name='0,99' , Dbl1=1.99000000000000000e+000  WHERE `Name` = 'insert record12' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:33' AND `Id2` = 12 AND `Id` = 3;
Select * from my_dao where Id=3;
update my_dao SET Name='0,99' , Dbl1=1.99000000000000000e+000  WHERE `Name` = 'insert record12' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:33' AND `Id` = 3 AND `Id2` = 12;
Select * from my_dao where Id=3;
update my_dao SET Name='0,99' , Dbl1=1.99000000000000000e+000  WHERE `Name` = 'insert record12' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:33' AND `Id` = 3 AND `Id2` = 12;
Select * from my_dao where Id=3;
update my_dao SET Name='0,99' , Dbl1=1.99000000000000000e+000  WHERE `Name` = 'insert record12' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:33' AND `Id` = 3 AND `Id2` = 12;
Select * from my_dao where Id=3;
update my_dao SET Name='0,99' , Dbl1=1.99000000000000000e+000  WHERE `Name` = 'insert record12' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:33' AND `Id` = 3 AND `Id2` = 12;
my_dao;
SELECT * FROM my_dao;
update my_dao SET Name='updated-string'  WHERE `Name` = 'insert record10' AND `Dbl1` = 9.89999999999999990e-001 AND `Ts` = '2008-03-25 17:06:32' AND `Id` = 1 AND `Id2` = 10;
my_dao;
SELECT * FROM my_dao;

Suggested fix:
I don't know if the SQL command is generated by Microsoft or by the ODBC Driver.

In this case, the driver must take in account the format of the double ( ie 7,2 )

[25 Mar 2008 17:09] Tonci Grgin

Hi Harve and thanks for your report.

Update criteria ADO property governs your update statement creation. In this case, as you didn't set it according to http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1 and it has default value of "adCriteriaAllCols". Please try adding update criteria and retest:
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Properties("Update Criteria").Value = 0
    .Open(strSQL)
End With

Waiting on your result.

[26 Mar 2008 8:27] Herve GAUMER

Thanks for your quick answer

It works fine now
I use DAO, so, I modify the cursor type

'ws.DefaultCursorDriver = dbUseODBCCursor
ws.DefaultCursorDriver = dbUseClientBatchCursor

And I use this criteria for the recordset update
rs.UpdateOptions = dbCriteriaUpdate + dbCriteriaKey

[26 Mar 2008 9:28] Tonci Grgin

Herve, I will close your report now as !Bg. Besides ADO update criteria there is always a problem of storing FLOAT's but I won't go into that discussion now. Check details in http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html.