Re[2]: ADO and MySQL Error - HMax - com.mysql.lists.win32

12 messages in com.mysql.lists.win32Re[2]: ADO and MySQL Error

From	Sent On	Attachments
Christian Surya	16 Dec 2004 01:08
Daniel da Veiga	16 Dec 2004 05:39
Peter Harvey	16 Dec 2004 08:03
Michael Avila	16 Dec 2004 14:48
Christian Surya	16 Dec 2004 18:18
Peter Harvey	16 Dec 2004 22:12
Christian Surya	16 Dec 2004 23:34
Ruben Edna	16 Dec 2004 23:56
Charles Metcalfe	17 Dec 2004 00:34
HMax	17 Dec 2004 01:07
Christian Surya	17 Dec 2004 01:26
Bernard LEFEUVRE	24 Dec 2004 02:32

Subject:	Re[2]: ADO and MySQL Error
From:	HMax (hm...@securibuy.com)
Date:	12/17/2004 01:07:10 AM
List:	com.mysql.lists.win32

Hi List,

We had been facing this problem too and we actually discovered several cases
where it would happen. I remember one of them as follow : - There is a TEXT field in the recordset - If you try to update it to an empty string and the field does not accept NULL
values (it's wierd), the update would fail.

I don't remember the exact context of another case, but the error would occur
too if we updated a TEXT (or VARCHAR, I'm not sure) to a shorter value than it was before (3 chars instead of 10 for
instance).

Best of luck with the debugging ;) HMax

RE> This happens all the time using ADO and recordset updates. It might work RE> perfectly with thousends of records, and then suddenly out of nowhere RE> exactly what you explain will happen.

RE> We have a "huge" database system based on MySQL database (last two versions) RE> using MyODBC (last three versions). Our largest customer have over 2 million RE> table updates every day...

RE> Using recordset updates we fast discovered that there is no logic when your RE> problem will occur. Only solution that always works is to never use RE> recordset updates. Instead you should use connection SQL execute like this: RE> oCon.Execute "UDPATE arapk SET jnsfrm = '" & var1 & "', tglapk = '" & var2 & RE> "' WHERE apknum = " & apknum & ";"

RE> You will still have to use recordset to do the "if oRS.EOF then" check.

RE> I also highly recommend you to not use recordset addnew too... use instead RE> the following: RE> oCon.Execute "INSERT INTO arapk (jnsfrm, tglaps, ...) VALUES ('" & var1 & RE> "',' & var2 & ",...);"

RE> If you need to know what autoincrement value your insert got use this RE> immediately after:

RE> Dim lngAutoID As Long RE> lngAutoID = GetLastInsertID(oCon)

RE> Public Function GetLastInsertID(dbConn As ADODB.Connection) As Long RE> Dim rsLastInsertID As New ADODB.Recordset RE> rsLastInsertID.Open "SELECT LAST_INSERT_ID()", dbConn, adOpenKeyset, RE> adLockOptimistic RE> GetLastInsertID = rsLastInsertID![LAST_INSERT_ID()] RE> rsLastInsertID.Close RE> End Function

RE> Best regards, RE> Ruben Edna

RE> -----Original Message----- RE> From: Christian Surya [mailto:csur...@gmail.com] RE> Sent: 17. desember 2004 08:35 RE> To: Peter Harvey RE> Cc: win...@lists.mysql.com; myo...@lists.mysql.com RE> Subject: Re: ADO and MySQL Error

RE> Thanks a lot peter hope it can fix soon

RE> Best Regards, RE> Christian

RE> On Thu, 16 Dec 2004 22:12:26 -0800, Peter Harvey <phar...@mysql.com> wrote:

I have bumped up the Severity and the Priority of Bug 5427.

Peter Harvey, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification

-----Original Message----- From: Christian Surya [mailto:csur...@gmail.com] Sent: December 16, 2004 6:19 PM To: Peter Harvey Cc: win...@lists.mysql.com; myo...@lists.mysql.com Subject: Re: ADO and MySQL Error

Hi Peter,

This is the code I use for the recordset strSQL="SELECT * FROM arapk WHERE apknum = "+apknum oRS.Open strSQL, oCon,1,3 if oRS.EOF then ors.addnew end if <change data here> ors.update 'generate same error with ors.updatebatch ors.close set ors = nothing

the structure is nomapk double, auto inc jnsfrm char(1) tglapk datetime kodelk double nomdel double jenpiu char(1) nampem varchar(30) alpem1 varchar(30) alpem2 varchar(30)

there is another field but i can't write it here because too long. for information the datatype of all field is one of the following datatype : double,char,varchar and datetime and all field is not null

i'm sorry i can't give you sample data because my company is strict

thanks for your help peter

ps: i have reply from daniel and michael. they say my problem is because the bug from myodbc and have been listed on the http://bugs.mysql.com/bug.php?id=5427 but why there is no fixed for that problem?

On Thu, 16 Dec 2004 08:03:28 -0800, Peter Harvey <phar...@mysql.com> wrote:

Please provide;

- VB code to demonstrate the problem - table structure of the table in question - small amount (4 or 5 rows) of sample data if possible

Peter Harvey, Software Developer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification

-----Original Message----- From: Christian Surya [mailto:csur...@gmail.com] Sent: December 16, 2004 1:09 AM To: win...@lists.mysql.com; myo...@lists.mysql.com Subject: ADO and MySQL Error

Hi, I have a problem with VB6 SP6, ADO2.8 and MySQL database version 4.1.7 and ODBC 3.51.10. i use Update function of the recordset and it raise error when update the database. the error is "Query-based update failed because the row to update could not be found." These error only occur if i want update data not insert.and the error source is from ODBC engine. anyone can help me?

thanks in advance chris

RE> -- RE> MySQL ODBC Mailing List RE> For list archives: http://lists.mysql.com/myodbc RE> To unsubscribe: http://lists.mysql.com/myodbc?unsub=rube...@edcom.no