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