ADO/MyODBC Error with TIME fields.

----------MB_8C8EE741FCAC3B7_17D8_142D_WEBMAIL-RA21.sysops.a ol.com
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="us-ascii"

Hello,
I have a problem when I try to create a record with a TIME field using a client-cursor.

I am using Visual Basic 6.0, MyOdbc 3.51.12, Windows 2000 and ADO 2.8, and
I have found the problem both in Mysql 4.0 and Mysql 4.1 (latest versions) with InnoDB.

Here is the table:
-----------------
CREATE TABLE `prueba` (`Doc` varchar(10) NOT NULL default '',
`Time1` time NOT NULL default '00:00:00'
,PRIMARY KEY (`Doc`)) TYPE=InnoDB;

Here is the code:
----------------
Private Sub Command1_Click()
On Error GoTo mal

Dim Conexion As ADODB.Connection
Dim Opciones As Long, RAfec As Long

Set Conexion = New ADODB.Connection

Opciones = 1 + 2 + 8 + 16384

'Open the connection
Conexion.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=myServer;" _
& "UID=root;PWD=myPWD;OPTION=" & Str$(Opciones)
Conexion.Open

'Use TEST
Conexion.Execute "Use test", RAfec, adCmdText + adExecuteNoRecords

'open the cursor
Dim MiSet As ADODB.Recordset, MiSQL As String
Set MiSet = New ADODB.Recordset

'SELECT
MiSQL = "select * from prueba"

'Configuring then cursor
MiSet.CursorLocation = adUseClient
MiSet.CursorType = adOpenStatic
MiSet.LockType = adLockOptimistic
MiSet.Properties("Update Criteria") = adCriteriaKey
MiSet.Properties("Update Resync") = adResyncNone

'Open the cursor
MiSet.Open MiSQL, Conexion, , , adCmdText 'This opens the cursor. (Ok)

'Let's go
MsgBox "Now, We will try to create a new record"

'This does not run
MiSet.AddNew
MiSet!Doc = "AA00123456"
MiSet!Time1 = "01:00"
'
'Update!
MiSet.Update 'ERROR ...
Exit Sub
mal:
MsgBox Err.Description
'I get the following error:
'[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt-max-log]
'Something is wrong in the sintax ''100:00:00)' en la linea 1
'ADO/MyODBC tries the following SQL:
'INSERT INTO `prueba` (`Doc`,`Time1`) VALUES ('AA00123456','100:00:00)
'
End Sub

'You can see that '100:00:00) is wrong
'
'If I change
'
'...
'MiSet!HoraInicio="01:15" or MiSet!HoraInicio="02:00"
'MiSet.update
'
'It's ok!!! I have only the problem with MiSet!HoraInicio="01:00"
'
Is an ADO or MyODBC problem?

Thanks in advance,
Rafa

____________________________________________________________ ____________
Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading spam and email virus protection.

----------MB_8C8EE741FCAC3B7_17D8_142D_WEBMAIL-RA21.sysops.a ol.com--

Re: ADO/MyODBC Error with TIME fields.

rafarife@netscape.net wrote:
> Hello,
> I have a problem when I try to create a record with a TIME field using a client-cursor.
>
> I am using Visual Basic 6.0, MyOdbc 3.51.12, Windows 2000 and ADO 2.8, and
> I have found the problem both in Mysql 4.0 and Mysql 4.1 (latest versions) with InnoDB.
>
> Here is the table:
> -----------------
> CREATE TABLE `prueba` (`Doc` varchar(10) NOT NULL default '',
> `Time1` time NOT NULL default '00:00:00'
> ,PRIMARY KEY (`Doc`)) TYPE=InnoDB;
>
> Here is the code:
> ----------------
> Private Sub Command1_Click()
> On Error GoTo mal
>
> Dim Conexion As ADODB.Connection
> Dim Opciones As Long, RAfec As Long
>
> Set Conexion = New ADODB.Connection
>
> Opciones = 1 + 2 + 8 + 16384
>
> 'Open the connection
> Conexion.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
> & "SERVER=myServer;" _
> & "UID=root;PWD=myPWD;OPTION=" & Str$(Opciones)
> Conexion.Open
>
> 'Use TEST
> Conexion.Execute "Use test", RAfec, adCmdText + adExecuteNoRecords
>
> 'open the cursor
> Dim MiSet As ADODB.Recordset, MiSQL As String
> Set MiSet = New ADODB.Recordset
>
> 'SELECT
> MiSQL = "select * from prueba"
>
> 'Configuring then cursor
> MiSet.CursorLocation = adUseClient
> MiSet.CursorType = adOpenStatic
> MiSet.LockType = adLockOptimistic
> MiSet.Properties("Update Criteria") = adCriteriaKey
> MiSet.Properties("Update Resync") = adResyncNone
>
> 'Open the cursor
> MiSet.Open MiSQL, Conexion, , , adCmdText 'This opens the cursor. (Ok)
>
> 'Let's go
> MsgBox "Now, We will try to create a new record"
>
> 'This does not run
> MiSet.AddNew
> MiSet!Doc = "AA00123456"
> MiSet!Time1 = "01:00"
>

Have you tried:
MiSet!Time1 = "01:00:00"

I've found it's more reliable ( and portable ) if you don't use
recordset objects like this for updates. You can *read* from them, but
any updates I always do by assembling an 'update table' command and
sending it via the ADO connection.

--
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: ADO/MyODBC Error with TIME fields.

----------MB_8C8F091D82D439F_794_32A9_WEBMAIL-RA01.sysops.ao l.com
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="utf-8"

=20
Yes, but MiSet!Time1=3D"01:00:00" doesn´t work!
=20
And you are right, the 'update table' command is the only solution I've foun=
d.
=20
thanks! =20
____________________________________________________________ ____________
Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading s=
pam and email virus protection.

----------MB_8C8F091D82D439F_794_32A9_WEBMAIL-RA01.sysops.ao l.com--

Re: ADO/MyODBC Error with TIME fields.

Hi,

> I have found the problem both in Mysql 4.0 and Mysql 4.1 (latest versions)
> with InnoDB.
>
> Here is the table:
> -----------------
> CREATE TABLE `prueba` (`Doc` varchar(10) NOT NULL default '',
> `Time1` time NOT NULL default '00:00:00'
> ,PRIMARY KEY (`Doc`)) TYPE=InnoDB;

According to our MyODBC gurus, there are may be several problems that
affects you...

Please, try to use '1:00:00' or '01:00:00' when you are setting value for
your `Time1` column below:

> 'This does not run
> MiSet.AddNew
> MiSet!Doc = "AA00123456"
> MiSet!Time1 = "01:00"

and test if it will make any difference.

Best regards,
--
Valeriy Kravchuk, Support Engineer
MySQL AB, Kiev, Ukraine, www.mysql.com

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


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: ADO/MyODBC Error with TIME fields.

----------MB_8C8F16125009573_143C_67C8_WEBMAIL-RA12.sysops.a ol.com
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="utf-8"

=20
Hi,=20
=20
>According to our MyODBC gurus, there are may be several problems that affec=
ts you...=20
>=20
>Please, try to use '1:00:00' or '01:00:00' when you are setting value for y=
our `Time1` column below:=20
=20
>and test if it will make any difference.=20
=20
MiSet!Time1=3D"01:00:00" doesn´t work!. There isn´t any difference=
..
=20
I have to do an 'update/insert into table' command to get it because the 'sq=
l command' generates by
ADO isn´t ok.
=20
Regards.
____________________________________________________________ ____________
Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading s=
pam and email virus protection.

----------MB_8C8F16125009573_143C_67C8_WEBMAIL-RA12.sysops.a ol.com--