update problem

update problem

am 03.10.2002 18:59:31 von Melchior Rabe

Hi,
I have a strange problem with a VC6 application using MyODBC. I can connect
the database and add/delete records. Updating a record works only in the
first record (the first of the table).
There are no error-messages in the debug window or anything else that could
help.


Thanks for any suggestions

MELLE

PS: is there anywhere a short example how to include MyODBC into a VC
project?

Here is the code I use:
/////////////////////////
try{
if( !m_pClient->IsOpen( ) )
return;
if( !m_pClient->CanUpdate( ) )
return;
m_pClient->GetClient(nId); // get the record
m_pClient->Edit();

// change values

if( !m_pClient->Update( ) )
{
AfxMessageBox(_T("Record not updated."));
return;
}
}
catch(...)
{
AfxMessageBox(_T("ERROR!"));
return;
}




------------------------------------------------------------ ---------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail myodbc-thread6185@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail myodbc-unsubscribe@lists.mysql.com instead.

RE: Update problem

am 09.09.2005 00:29:44 von Carlos Olivas

------=_NextPart_000_000B_01C5B48B.199E0F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Sorry, the version of MyODBC is 3.52.11-2

=20

_____ =20

De: Carlos Daniel Olivas Barba [mailto:carlosolivas@seagarden.com.mx]=20
Enviado el: Jueves, 08 de Septiembre de 2005 01:29 p.m.
Para: 'myodbc@lists.mysql.com'
Asunto: Update problem

=20

Hi,

=20

I have a problem when updating a recordset I got a message that says =
=93The
row for update can=B4t be found: Some of the values may changed since =
last
read=94, or something like that because the original message is in =
Spanish,
this happens only when updating rows when updating tables with datetime =
type
columns. I=B4m using MySQL-4.1.13a-win32 and MyODBC-3.52.06, here is a =
sample
of the code in vb:

=20

rs.Open "select * from traspasos where folio_traspaso=3D'" & folio & "' =
and
origen=3D'" & s & "'", conexion, adOpenKeyset, adLockOptimistic

If rs.RecordCount =3D 0 Then

rs.AddNew

Else

consec =3D rs!consecutivo

End If

rs!folio_traspaso =3D folio

rs!ORIGEN =3D s

rs!DESTINO =3D cmbDestino

rs!fecha =3D format(date, =93YYYY-MM-DD=94)

rs!hora =3D Time

rs!CODRESPON =3D =93carlos=94

rs!TERM =3D =93my-pc=94

rs!status =3D 2

rs!observaciones =3D txtObs.text

rs.Update

rs.Close

=20

Hope you can help me!!

=20

=20

Carlos Olivas.


------=_NextPart_000_000B_01C5B48B.199E0F50--

Re: Update problem

am 09.09.2005 02:36:26 von Daniel Kasak

Carlos Daniel Olivas Barba wrote:

>I have a problem when updating a recordset I got a message that says “The
>row for update can´t be found: Some of the values may changed since last
>read”, or something like that because the original message is in Spanish,
>this happens only when updating rows when updating tables with datetime type
>columns.
>
Have you got a primary key and a timestamp? Maybe send your table
definition ( 'describe traspasos' from the mysql client ).
Actually I'm not sure if you need a timestamp for a VB app, but you need
one with Access.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
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: Update problem

am 09.09.2005 18:47:40 von Carlos Olivas

This is the table definition:

'FOLIO_TRASPASO', 'bigint(20)', '', '', '0', ''
'ORIGEN', 'varchar(15)', '', 'MUL', '', ''
'DESTINO', 'varchar(15)', '', 'MUL', '', ''
'FECHA', 'date', 'YES', '', '', ''
'HORA', 'time', 'YES', '', '', ''
'CODRESPON', 'varchar(15)', 'YES', '', '', ''
'TERM', 'varchar(20)', 'YES', '', '', ''
'STATUS', 'char(1)', 'YES', '', '0', ''
'A_DISCO', 'enum('Y','N')', 'YES', '', 'N', ''
'FOLIO_REF', 'int(11)', 'YES', '', '0', ''
'OBSERVACIONES', 'varchar(200)', 'YES', '', '', ''
'CONSECUTIVO', 'bigint(20) unsigned', '', 'PRI', '', 'auto_increment'
'MOTIVO_CANCELADO', 'varchar(200)', 'YES', '', '', ''

-----Mensaje original-----
De: Daniel Kasak [mailto:dkasak@nusconsulting.com.au]=20
Enviado el: Jueves, 08 de Septiembre de 2005 05:36 p.m.
Para: Carlos Daniel Olivas Barba; myodbc@lists.mysql.com
Asunto: Re: Update problem

Carlos Daniel Olivas Barba wrote:

>I have a problem when updating a recordset I got a message that says =
=93The
>row for update can=B4t be found: Some of the values may changed since =
last
>read=94, or something like that because the original message is in =
Spanish,
>this happens only when updating rows when updating tables with datetime
type
>columns.
>
Have you got a primary key and a timestamp? Maybe send your table=20
definition ( 'describe traspasos' from the mysql client ).
Actually I'm not sure if you need a timestamp for a VB app, but you need =

one with Access.

--=20
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3Dcarlosolivas@seagarden .com.mx




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

Re: Update problem

am 12.09.2005 00:16:23 von Daniel Kasak

Carlos Daniel Olivas Barba wrote:

>This is the table definition:
>
>'FOLIO_TRASPASO', 'bigint(20)', '', '', '0', ''
>'ORIGEN', 'varchar(15)', '', 'MUL', '', ''
>'DESTINO', 'varchar(15)', '', 'MUL', '', ''
>'FECHA', 'date', 'YES', '', '', ''
>'HORA', 'time', 'YES', '', '', ''
>'CODRESPON', 'varchar(15)', 'YES', '', '', ''
>'TERM', 'varchar(20)', 'YES', '', '', ''
>'STATUS', 'char(1)', 'YES', '', '0', ''
>'A_DISCO', 'enum('Y','N')', 'YES', '', 'N', ''
>'FOLIO_REF', 'int(11)', 'YES', '', '0', ''
>'OBSERVACIONES', 'varchar(200)', 'YES', '', '', ''
>'CONSECUTIVO', 'bigint(20) unsigned', '', 'PRI', '', 'auto_increment'
>'MOTIVO_CANCELADO', 'varchar(200)', 'YES', '', '', ''
>
>
I'm not sure about VB, but MS Access can't handle bigint columns -
particularly ones that are primary keys - and *particularly* bigint
unsigned. How many records have you got anyway? I use mediumint unsigned
for my primary keys - that gives you plenty of records to play with. int
will also work. You can try int unsigned if you like.

bigint is probably your problem.

Well ... VB is your problem :)

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
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: Update problem

am 12.09.2005 10:35:51 von David Dindorp

Carlos Daniel Olivas Barba wrote:
> I have a problem when updating a recordset I got a message that says =
"The
> row for update can=B4t be found: Some of the values may changed since =
last
> read", or something like that because the original message is in =
Spanish,
> this happens only when updating rows when updating tables with =
datetime
> type columns.

Turn on query logging on your MySQL server using the 'log' option in =
either
my.ini or the mysqld command line. In the query log, find the UPDATE
statement that fails and examine it's WHERE clause. If you post it =
here,
also include the data row that you expected the update to match.

> This is the table definition:
>=20
> 'FOLIO_TRASPASO', 'bigint(20)', '', '', '0', ''
> 'ORIGEN', 'varchar(15)', '', 'MUL', '', ''
> 'DESTINO', 'varchar(15)', '', 'MUL', '', ''
> 'FECHA', 'date', 'YES', '', '', ''
> 'HORA', 'time', 'YES', '', '', ''
> 'CODRESPON', 'varchar(15)', 'YES', '', '', ''
> 'TERM', 'varchar(20)', 'YES', '', '', ''
> 'STATUS', 'char(1)', 'YES', '', '0', ''
> 'A_DISCO', 'enum('Y','N')', 'YES', '', 'N', ''
> 'FOLIO_REF', 'int(11)', 'YES', '', '0', ''
> 'OBSERVACIONES', 'varchar(200)', 'YES', '', '', ''
> 'CONSECUTIVO', 'bigint(20) unsigned', '', 'PRI', '', 'auto_increment'
> 'MOTIVO_CANCELADO', 'varchar(200)', 'YES', '', '', ''

Perhaps try a 'datetime' column.

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

RE: Update problem

am 12.09.2005 12:12:21 von Martin King-Turner

I have seen a similar problem updating records with datetime columns =
when
the datetime column contains null values. If this is the problem, use
phpMyAdmin, command line, or something similar to populate the datetime
column. I used SQL like: "UPDATE table_name SET datetime_column_name =
=3D
NOW();" Once I'd done this, MyODBC worked fine. (My sample SQL will =
update
*all* the records, so you may need a WHERE clause).

-----Original Message-----
From: David Dindorp [mailto:ddi@dubex.dk]=20
Sent: 12 September 2005 09:36
To: Carlos Daniel Olivas Barba
Cc: myodbc@lists.mysql.com
Subject: RE: Update problem


Carlos Daniel Olivas Barba wrote:
> I have a problem when updating a recordset I got a message that says=20
> "The row for update can=B4t be found: Some of the values may changed=20
> since last read", or something like that because the original message=20
> is in Spanish, this happens only when updating rows when updating=20
> tables with datetime type columns.

Turn on query logging on your MySQL server using the 'log' option in =
either
my.ini or the mysqld command line. In the query log, find the UPDATE
statement that fails and examine it's WHERE clause. If you post it =
here,
also include the data row that you expected the update to match.

> This is the table definition:
>=20
> 'FOLIO_TRASPASO', 'bigint(20)', '', '', '0', ''
> 'ORIGEN', 'varchar(15)', '', 'MUL', '', ''
> 'DESTINO', 'varchar(15)', '', 'MUL', '', ''
> 'FECHA', 'date', 'YES', '', '', ''
> 'HORA', 'time', 'YES', '', '', ''
> 'CODRESPON', 'varchar(15)', 'YES', '', '', ''
> 'TERM', 'varchar(20)', 'YES', '', '', ''
> 'STATUS', 'char(1)', 'YES', '', '0', ''
> 'A_DISCO', 'enum('Y','N')', 'YES', '', 'N', ''
> 'FOLIO_REF', 'int(11)', 'YES', '', '0', ''
> 'OBSERVACIONES', 'varchar(200)', 'YES', '', '', '' 'CONSECUTIVO',=20
> 'bigint(20) unsigned', '', 'PRI', '', 'auto_increment'=20
> 'MOTIVO_CANCELADO', 'varchar(200)', 'YES', '', '', ''

Perhaps try a 'datetime' column.

--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: =
http://lists.mysql.com/myodbc?unsub=3Dmartin.kt@nb2bc.co.uk



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

RE: Update problem

am 12.09.2005 19:45:31 von Carlos Olivas

Hi, I turned on logging, what I got is this:

UPDATE `sweet`.`traspasos` SET
`FOLIO_TRASPASO`=3D1,`ORIGEN`=3D'VD',`DESTINO`=3D'JR',`FECHA `=3D'20050912=
',`HORA`=3D'1
0:39:43',`CODRESPON`=3D'CARLOS',`TERM`=3D'CARLOS',`STATUS`=3 D'2',`OBSERVA=
CIONES`=3D'
ok. guardar para continuar despues' WHERE `FOLIO_TRASPASO`=3D1 AND
`ORIGEN`=3D'VD' AND `DESTINO`=3D'JR' AND `FECHA`=3D'20050908' AND `HORA` =
IS NULL
AND `CODRESPON`=3D'CARLOS' AND `TERM`=3D'CARLOS' AND `STATUS`=3D'2' AND
`OBSERVACIONES`=3D'ok. guardar para continuar despues' AND =
`CONSECUTIVO`=3D1

I think the error is the column FECHA because the date format is
"YYYY-MM-DD" and then column HORA in the selected recordset is not null,
it's "00:00:00".

-----Mensaje original-----
De: David Dindorp [mailto:ddi@dubex.dk]=20
Enviado el: Lunes, 12 de Septiembre de 2005 01:36 a.m.
Para: Carlos Daniel Olivas Barba
CC: myodbc@lists.mysql.com
Asunto: RE: Update problem

Carlos Daniel Olivas Barba wrote:
> I have a problem when updating a recordset I got a message that says =
"The
> row for update can=B4t be found: Some of the values may changed since =
last
> read", or something like that because the original message is in =
Spanish,
> this happens only when updating rows when updating tables with =
datetime
> type columns.

Turn on query logging on your MySQL server using the 'log' option in =
either
my.ini or the mysqld command line. In the query log, find the UPDATE
statement that fails and examine it's WHERE clause. If you post it =
here,
also include the data row that you expected the update to match.

> This is the table definition:
>=20
> 'FOLIO_TRASPASO', 'bigint(20)', '', '', '0', ''
> 'ORIGEN', 'varchar(15)', '', 'MUL', '', ''
> 'DESTINO', 'varchar(15)', '', 'MUL', '', ''
> 'FECHA', 'date', 'YES', '', '', ''
> 'HORA', 'time', 'YES', '', '', ''
> 'CODRESPON', 'varchar(15)', 'YES', '', '', ''
> 'TERM', 'varchar(20)', 'YES', '', '', ''
> 'STATUS', 'char(1)', 'YES', '', '0', ''
> 'A_DISCO', 'enum('Y','N')', 'YES', '', 'N', ''
> 'FOLIO_REF', 'int(11)', 'YES', '', '0', ''
> 'OBSERVACIONES', 'varchar(200)', 'YES', '', '', ''
> 'CONSECUTIVO', 'bigint(20) unsigned', '', 'PRI', '', 'auto_increment'
> 'MOTIVO_CANCELADO', 'varchar(200)', 'YES', '', '', ''

Perhaps try a 'datetime' column.




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

RE: Update problem

am 13.09.2005 08:58:00 von David Dindorp

Carlos Daniel Olivas Barba wrote:
> Hi, I turned on logging, what I got is this:
>
> UPDATE `sweet`.`traspasos` SET
> =
`FOLIO_TRASPASO`=3D1,`ORIGEN`=3D'VD',`DESTINO`=3D'JR',`FECHA `=3D'20050912=
',
> =
`HORA`=3D'10:39:43',`CODRESPON`=3D'CARLOS',`TERM`=3D'CARLOS' ,`STATUS`=3D'=
2',
> `OBSERVACIONES`=3D'ok. guardar para continuar despues'
> WHERE `FOLIO_TRASPASO`=3D1 AND `ORIGEN`=3D'VD' AND `DESTINO`=3D'JR' =
AND
> `FECHA`=3D'20050908' AND `HORA` IS NULL
> AND `CODRESPON`=3D'CARLOS' AND `TERM`=3D'CARLOS' AND `STATUS`=3D'2' =
AND
> `OBSERVACIONES`=3D'ok. guardar para continuar despues' AND
`CONSECUTIVO`=3D1
>
> I think the error is the column FECHA because the date format is
> "YYYY-MM-DD" and then column HORA in the selected recordset is not
null,
> it's "00:00:00".

Ok, so far so good. Never seen that problem before.

The manual:
http://dev.mysql.com/doc/mysql/en/date-and-time-types.html

states that:
"Zero" date or time values used through MyODBC are converted
automatically
to NULL in MyODBC 2.50.12 and above, because ODBC can't handle such
values.

which might be a clue.

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

RE: Update problem

am 12.10.2005 17:50:22 von Carlos Olivas

It worked perfectly, thanks a lot!

Carlos.


-----Mensaje original-----
De: Peter Harvey [mailto:pharvey@mysql.com]=20
Enviado el: Martes, 11 de Octubre de 2005 11:38 p.m.
Para: Carlos Daniel Olivas Barba
Asunto: Re: Update problem

Carlos Daniel Olivas Barba wrote:

>Hi,
>
>=20
>
>I have a problem when updating a recordset I got a message that says =
=93The
>row for update can=B4t be found: Some of the values may changed since =
last
>read=94, or something like that because the original message is in =
Spanish,
>this happens only when updating rows when updating tables with datetime
type
>columns. I=B4m using MySQL-4.1.13a-win32 and MyODBC-3.52.06, here is a =
sample
>of the code in vb:
>
>=20
>
>rs.Open "select * from traspasos where folio_traspaso=3D'" & folio & "' =
and
>origen=3D'" & s & "'", conexion, adOpenKeyset, adLockOptimistic
>
>If rs.RecordCount =3D 0 Then
>
> rs.AddNew
>
>Else
>
> consec =3D rs!consecutivo
>
>End If
>
>rs!folio_traspaso =3D folio
>
>rs!ORIGEN =3D s
>
>rs!DESTINO =3D cmbDestino
>
>rs!fecha =3D format(date, =93YYYY-MM-DD=94)
>
>rs!hora =3D Time
>
>rs!CODRESPON =3D =93carlos=94
>
>rs!TERM =3D =93my-pc=94
>
>rs!status =3D 2
>
>rs!observaciones =3D txtObs.text
>
>rs.Update
>
>rs.Close
>
>=20
>
>Hope you can help me!!
>
>=20
>
>=20
>
>Carlos Olivas.
>
>
> =20
>
Please try this (and let us know how it works for you);

ftp://ftp.mysql.com/pub/mysql/hidden/connectors/odbc/mysql-c onnector-odbc=
-3.
51.12-win32.msi

BTW: Uninstall any existing first and go to windows\system32 and del=20
*myodbc* BEFORE installing this new one.


--=20
Peter Harvey, Software Developer
MySQL AB, www.mysql.com

Are you MySQL certified? www.mysql.com/certification




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