ADO and MySQL Error

am 16.12.2004 10:09:15 von Christian Surya

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

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: ADO and MySQL Error

am 16.12.2004 14:39:24 von Daniel da Veiga

I've read about it before...

http://dev.mysql.com/doc/mysql/en/ADO_-_rs.addNew.html

As far as I know, this is a bug, MyODBC doesn't seem to work properly
with text or blob fields when updating, however, an "UPDATE blabla
SET" seem to work, and at the comments of the above link, you'll find
a problem like yours.

You may try using MyVBQL, wich is a good library for MySQL that
doesn't need ODBC drivers or ADO (MDAC) components, I use it with my
app and it works just fine, besides, it was annoying to install MyODBC
and MDAC at all my clients. Check it out:

http://www.icarz.com/mysql/

You may find it useful.

Best regards,

On Thu, 16 Dec 2004 16:09:15 +0700, Christian Surya wrote:
> 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

--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: ADO and MySQL Error

am 16.12.2004 17:03:28 von pharvey

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:csuryas@gmail.com]
> Sent: December 16, 2004 1:09 AM
> To: win32@lists.mysql.com; myodbc@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
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:
> http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
>

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: ADO and MySQL Error

am 16.12.2004 17:03:28 von pharvey

RE: ADO and MySQL Error

am 16.12.2004 23:48:32 von Michael Avila

Hi,

I was getting that all of the time under the last few MySQL beta and
production releases (I believe all the ones I used were 4.x.x). Probably
more of an ODBC problem that MySQL but maybe not. The key to getting the
error is that you must have more than one record in the recordset for that
to happen. If you have only the one record it will work. I gave up and
coded it differently in order to get it to work. I don't have the code in
front of me right now to post what I did. I'd love to see a good workaround
or if it really is a bug, to be fixed ASAP. I consider it a critical issue
but did not have time to research and report it as a potential bug.

Mike

-----Original Message-----
From: Christian Surya [mailto:csuryas@gmail.com]
Sent: Thursday, December 16, 2004 4:09 AM
To: win32@lists.mysql.com; myodbc@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

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=mavila@mich.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: ADO and MySQL Error

am 17.12.2004 03:18:39 von Christian Surya

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

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 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:csuryas@gmail.com]
> > Sent: December 16, 2004 1:09 AM
> > To: win32@lists.mysql.com; myodbc@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
> >
> > --
> > MySQL ODBC Mailing List
> > For list archives: http://lists.mysql.com/myodbc
> > To unsubscribe:
> > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> >
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: ADO and MySQL Error

am 17.12.2004 03:18:39 von Christian Surya

RE: ADO and MySQL Error

am 17.12.2004 07:12:26 von pharvey

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:csuryas@gmail.com]
> Sent: December 16, 2004 6:19 PM
> To: Peter Harvey
> Cc: win32@lists.mysql.com; myodbc@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
>
> 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
> 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:csuryas@gmail.com]
> > > Sent: December 16, 2004 1:09 AM
> > > To: win32@lists.mysql.com; myodbc@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
> > >
> > > --
> > > MySQL ODBC Mailing List
> > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
> > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> > >
> >
> >
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe:
> http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: ADO and MySQL Error

am 17.12.2004 07:12:26 von pharvey

Re: ADO and MySQL Error

am 17.12.2004 08:35:12 von Christian Surya

Thanks a lot peter hope it can fix soon

Best Regards,
Christian

On Thu, 16 Dec 2004 22:12:26 -0800, Peter Harvey 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:csuryas@gmail.com]
> > Sent: December 16, 2004 6:19 PM
> > To: Peter Harvey
> > Cc: win32@lists.mysql.com; myodbc@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
> >
> > 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
> > 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:csuryas@gmail.com]
> > > > Sent: December 16, 2004 1:09 AM
> > > > To: win32@lists.mysql.com; myodbc@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
> > > >
> > > > --
> > > > MySQL ODBC Mailing List
> > > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
> > > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> > > >
> > >
> > >
> >
> > --
> > MySQL ODBC Mailing List
> > For list archives: http://lists.mysql.com/myodbc
> > To unsubscribe:
> > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> >
>
>

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: ADO and MySQL Error

am 17.12.2004 08:35:12 von Christian Surya

RE: ADO and MySQL Error

am 17.12.2004 08:57:06 von Ruben Edna

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

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

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

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

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

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

Dim lngAutoID As Long
lngAutoID = GetLastInsertID(oCon)

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

Best regards,
Ruben Edna

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

Thanks a lot peter hope it can fix soon

Best Regards,
Christian

On Thu, 16 Dec 2004 22:12:26 -0800, Peter Harvey 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:csuryas@gmail.com]
> > Sent: December 16, 2004 6:19 PM
> > To: Peter Harvey
> > Cc: win32@lists.mysql.com; myodbc@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
> >
> > 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
> > 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:csuryas@gmail.com]
> > > > Sent: December 16, 2004 1:09 AM
> > > > To: win32@lists.mysql.com; myodbc@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
> > > >
> > > > --
> > > > MySQL ODBC Mailing List
> > > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
> > > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> > > >
> > >
> > >
> >
> > --
> > MySQL ODBC Mailing List
> > For list archives: http://lists.mysql.com/myodbc
> > To unsubscribe:
> > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> >
>
>

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

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: ADO and MySQL Error

am 17.12.2004 08:57:06 von Ruben Edna

Re: ADO and MySQL Error

am 17.12.2004 09:58:32 von Christian Surya

Hi Ruben,
Thanks for your explanation. I'm too lazy to write SQL Statement :-)
but I'll fix my prob soon using your explanation.
Thanks for your reply

Best Regards,
Christian

On Fri, 17 Dec 2004 08:57:06 +0100, Ruben Edna wrote:
> This happens all the time using ADO and recordset updates. It might work
> perfectly with thousends of records, and then suddenly out of nowhere
> exactly what you explain will happen.
>
> We have a "huge" database system based on MySQL database (last two versions)
> using MyODBC (last three versions). Our largest customer have over 2 million
> table updates every day...
>
> Using recordset updates we fast discovered that there is no logic when your
> problem will occur. Only solution that always works is to never use
> recordset updates. Instead you should use connection SQL execute like this:
> oCon.Execute "UDPATE arapk SET jnsfrm = '" & var1 & "', tglapk = '" & var2 &
> "' WHERE apknum = " & apknum & ";"
>
> You will still have to use recordset to do the "if oRS.EOF then" check.
>
> I also highly recommend you to not use recordset addnew too... use instead
> the following:
> oCon.Execute "INSERT INTO arapk (jnsfrm, tglaps, ...) VALUES ('" & var1 &
> "',' & var2 & ",...);"
>
> If you need to know what autoincrement value your insert got use this
> immediately after:
>
> Dim lngAutoID As Long
> lngAutoID = GetLastInsertID(oCon)
>
> Public Function GetLastInsertID(dbConn As ADODB.Connection) As Long
> Dim rsLastInsertID As New ADODB.Recordset
> rsLastInsertID.Open "SELECT LAST_INSERT_ID()", dbConn, adOpenKeyset,
> adLockOptimistic
> GetLastInsertID = rsLastInsertID![LAST_INSERT_ID()]
> rsLastInsertID.Close
> End Function
>
> Best regards,
> Ruben Edna
>
>
> -----Original Message-----
> From: Christian Surya [mailto:csuryas@gmail.com]
> Sent: 17. desember 2004 08:35
> To: Peter Harvey
> Cc: win32@lists.mysql.com; myodbc@lists.mysql.com
> Subject: Re: ADO and MySQL Error
>
> Thanks a lot peter hope it can fix soon
>
> Best Regards,
> Christian
>
> On Thu, 16 Dec 2004 22:12:26 -0800, Peter Harvey 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:csuryas@gmail.com]
> > > Sent: December 16, 2004 6:19 PM
> > > To: Peter Harvey
> > > Cc: win32@lists.mysql.com; myodbc@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
> > >
> > > 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
> > > 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:csuryas@gmail.com]
> > > > > Sent: December 16, 2004 1:09 AM
> > > > > To: win32@lists.mysql.com; myodbc@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
> > > > >
> > > > > --
> > > > > MySQL ODBC Mailing List
> > > > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
> > > > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> > > > >
> > > >
> > > >
> > >
> > > --
> > > MySQL ODBC Mailing List
> > > For list archives: http://lists.mysql.com/myodbc
> > > To unsubscribe:
> > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> > >
> >
> >
>
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe: http://lists.mysql.com/myodbc?unsub=ruben.edna@edcom.no
>
>

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re[2]: ADO and MySQL Error

am 17.12.2004 10:07:32 von HMax

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:csuryas@gmail.com]
RE> Sent: 17. desember 2004 08:35
RE> To: Peter Harvey
RE> Cc: win32@lists.mysql.com; myodbc@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 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:csuryas@gmail.com]
>> > Sent: December 16, 2004 6:19 PM
>> > To: Peter Harvey
>> > Cc: win32@lists.mysql.com; myodbc@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
>> >
>> > 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
>> > 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:csuryas@gmail.com]
>> > > > Sent: December 16, 2004 1:09 AM
>> > > > To: win32@lists.mysql.com; myodbc@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
>> > > >
>> > > > --
>> > > > MySQL ODBC Mailing List
>> > > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
>> > > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
>> > > >
>> > >
>> > >
>> >
>> > --
>> > MySQL ODBC Mailing List
>> > For list archives: http://lists.mysql.com/myodbc
>> > To unsubscribe:
>> > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
>> >
>>
>>

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

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re[2]: ADO and MySQL Error

am 17.12.2004 10:07:32 von HMax

Re: ADO and MySQL Error

am 17.12.2004 10:27:04 von Christian Surya

Hi Charles,

But my field in my table is set to not null so there is no field
contains NULL. Our problem is a bug from MyODBC and have been
reported since Sep 04 but there is no fixed for that problem. But
today Peter Harvey have been set the bug with Critical severity so i
hope this problem will be fix soon. Thanks for your explanation

Best regards,
Christian

On Fri, 17 Dec 2004 10:34:56 +0200, Charles Metcalfe
wrote:
>
>
> Hi,
>
>
>
> Christian Surya wrote:
>
>
>
> > 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?
>
>
>
>
>
> We use VB6 SP6, ADO2.8 and MySQL database version 4.1.7 and ODBC 3.51.10.
> and have had a similar problem and have found the cause to be as follows.
>
>
>
> If we SELECT a table into a recordset and the selected fields include TEXT
> or DATE fields (we don't use BLOB fields) and any of the TEXT or DATE fields
> contain NULL's then we always get the error "Query-based update failed
> because the row to update could not be found." when we execute a recordset
> update or updatebatch. The only way we have been able to overcome this
> problem is to ensure that all TEXT and DATE fields contain a valid value
> when creating the record.
>
> We have opted to fill TEXT fields with vbcrlf (carriage return, line feed)
> and DATE fields with '1000/01/01' on record creation.
>
>
>
> Hope this helps
>
>
>
> Charles
>
>
>
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004
>
> This email is confidential. It is intended only to be read or used by the
> addressee. If you are not the intended recipient you must not disclose or
> use the information contained herein. If you have received this e-mail in
> error, please notify the sender immediately by return e-mail and delete the
> message. Confidentiality is not waived by reason of mistaken delivery to any
> party other than the intended recipient.
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=csuryas@gmail.com
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Re[2]: ADO and MySQL Error

am 17.12.2004 10:35:22 von Christian Surya

Hi HMax,
but my table have a default value for all table so if i add/update the
logic is MySQL fill the field with its default value. I think the
error raise because I want update the data which is shorter than
before and the datatype is Varchar. But there is another datatype
which this error occur and have been listed on
http://bugs.mysql.com/bug.php?id=5427 as i mentioned before and thanks
to peter who bumped up the
severity and priority for the bug.
Thanks for your explanation

Best Regards,
Christian

On Fri, 17 Dec 2004 10:07:32 +0100, HMax wrote:
> 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:csuryas@gmail.com]
> RE> Sent: 17. desember 2004 08:35
> RE> To: Peter Harvey
> RE> Cc: win32@lists.mysql.com; myodbc@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 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:csuryas@gmail.com]
> >> > Sent: December 16, 2004 6:19 PM
> >> > To: Peter Harvey
> >> > Cc: win32@lists.mysql.com; myodbc@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
> >> >
> >> > 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
> >> > 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:csuryas@gmail.com]
> >> > > > Sent: December 16, 2004 1:09 AM
> >> > > > To: win32@lists.mysql.com; myodbc@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
> >> > > >
> >> > > > --
> >> > > > MySQL ODBC Mailing List
> >> > > > For list archives: http://lists.mysql.com/myodbc To unsubscribe:
> >> > > > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> >> > > >
> >> > >
> >> > >
> >> >
> >> > --
> >> > MySQL ODBC Mailing List
> >> > For list archives: http://lists.mysql.com/myodbc
> >> > To unsubscribe:
> >> > http://lists.mysql.com/myodbc?unsub=peterh@mysql.com
> >> >
> >>
> >>
>
> RE> --
> RE> MySQL ODBC Mailing List
> RE> For list archives: http://lists.mysql.com/myodbc
> RE> To unsubscribe: http://lists.mysql.com/myodbc?unsub=ruben.edna@edcom.no
>
>

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: ADO and MySQL Error

am 24.12.2004 11:32:35 von Bernard LEFEUVRE

Hi,

I had a similar problem some monthes ago.I have upgraded the Jet 4.0
Engine with Service Pack 8 and the problem had disappear.

Bernard

Christian Surya a écrit :

>Hi Charles,
>
>But my field in my table is set to not null so there is no field
>contains NULL. Our problem is a bug from MyODBC and have been
>reported since Sep 04 but there is no fixed for that problem. But
>today Peter Harvey have been set the bug with Critical severity so i
>hope this problem will be fix soon. Thanks for your explanation
>
>Best regards,
>Christian
>
>
>On Fri, 17 Dec 2004 10:34:56 +0200, Charles Metcalfe
> wrote:
>
>
>>Hi,
>>
>>
>>
>>Christian Surya wrote:
>>
>>
>>
>>
>>
>>>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?
>>
>>
>>
>>
>>
>>We use VB6 SP6, ADO2.8 and MySQL database version 4.1.7 and ODBC 3.51.10.
>>and have had a similar problem and have found the cause to be as follows.
>>
>>
>>
>>If we SELECT a table into a recordset and the selected fields include TEXT
>>or DATE fields (we don't use BLOB fields) and any of the TEXT or DATE fields
>>contain NULL's then we always get the error "Query-based update failed
>>because the row to update could not be found." when we execute a recordset
>>update or updatebatch. The only way we have been able to overcome this
>>problem is to ensure that all TEXT and DATE fields contain a valid value
>>when creating the record.
>>
>>We have opted to fill TEXT fields with vbcrlf (carriage return, line feed)
>>and DATE fields with '1000/01/01' on record creation.
>>
>>
>>
>>Hope this helps
>>
>>
>>
>>Charles
>>
>>
>>
>>
>>
>>
>>
>>
>>---
>>Outgoing mail is certified Virus Free.
>>Checked by AVG anti-virus system (http://www.grisoft.com).
>>Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004
>>
>>This email is confidential. It is intended only to be read or used by the
>>addressee. If you are not the intended recipient you must not disclose or
>>use the information contained herein. If you have received this e-mail in
>>error, please notify the sender immediately by return e-mail and delete the
>>message. Confidentiality is not waived by reason of mistaken delivery to any
>>party other than the intended recipient.
>>--
>>MySQL Windows Mailing List
>>For list archives: http://lists.mysql.com/win32
>>To unsubscribe: http://lists.mysql.com/win32?unsub=csuryas@gmail.com
>>
>>
>>
>>
>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org