ADO/MyODBC Error with TIME fields.

ADO/MyODBC Error with TIME fields.

am 15.12.2006 16:23:36 von rafarife

Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="us-ascii"

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'

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.Execute "Use test", RAfec, adCmdText + adExecuteNoRecords

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

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!Doc = "AA00123456"
MiSet!Time1 = "01:00"
MiSet.Update 'ERROR ...
Exit Sub
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"
'It's ok!!! I have only the problem with MiSet!HoraInicio="01:00"
Is an ADO or MyODBC problem?

Thanks in advance,

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