Adodb.command date writing issue

Hello i use european date format in my system (DD/MM/YYYY), i works
with VB6 sp5 and Access 2002 DB.
I've a strange problem with adodb.parameters

I do, through adodb.command, two insert in a table (be aware if you try
the example, it will delete the entire table data).
Both the insert use the same two parameters with the same values
(except the id).

The debug shows that both the date variable and the parameter value are
correct.
*BUT* after inserting, if you read the DB, the month and the day of the
second insert are switched!

This is the debug output:
First insert
Variable: day 11 - month 1 - year 2005
Parametro: day 11 - month 1 - year 2005
--------------
Second insert
Variable: day 11 - month 1 - year 2005
Parametro: day 11 - month 1 - year 2005
--------------
Record 1: day 11 - month 1 - year 2005
Record 2: day 1 - month 11 - year 2005

As you can see the Record 2 is wrong.
Can someone give me an explanation?


thanks,
termoPilucco

P.s. my code:

'---------------------------------------------------
Option Explicit

Private Sub Command1_Click()
Dim adoCmd As ADODB.Command
Dim adoCnn As ADODB.Connection
Dim dateValue As Date

Set adoCnn = xxxxx ' make an adodb connection to an access DB
' with a table named "mytable" with two
' fields: 'id' (long) and 'mydate' (date)

adoCnn.Execute "delete from mytable"

' preparing command with generic values
Set adoCmd = New ADODB.Command
With adoCmd
.CommandText = "INSERT INTO mytable (id,mydate) values (?,?)"
.Parameters.Append .CreateParameter("id", adInteger, _
adParamInput, , Null)
.Parameters.Append .CreateParameter("mydate", adDate, _
adParamInput, , Null)
.ActiveConnection = adoCnn
End With

dateValue = DateSerial(2005, 1, 11)

' first insert
Debug.Print "First insert"
Debug.Print "Variable: day " & Day(dateValue), _
"- month " & Month(dateValue), _
"- year " & Year(dateValue)
adoCmd.Parameters(0).Value = 1
adoCmd.Parameters(1).Value = dateValue
Debug.Print "Parametro: day " & Day(adoCmd.Parameters(1).Value), _
"- month " & Month(adoCmd.Parameters(1).Value), _
"- year " & Year(adoCmd.Parameters(1).Value)
adoCmd.Execute
Debug.Print "--------------"

' second insert
Debug.Print "Second insert"
Debug.Print "Variable: day " & Day(dateValue), _
"- month " & Month(dateValue), _
"- year " & Year(dateValue)
adoCmd.Parameters(0).Value = 2
adoCmd.Parameters(1).Value = dateValue
Debug.Print "Parametro: day " & Day(adoCmd.Parameters(1).Value), _
"- month " & Month(adoCmd.Parameters(1).Value), _
"- year " & Year(adoCmd.Parameters(1).Value)
adoCmd.Execute
Debug.Print "--------------"
Set adoCmd = Nothing

' reading the stored data:
Dim rsReading As ADODB.Recordset

Set rsReading = adoCnn.Execute("select * from mytable order by id")
While Not rsReading.EOF
Debug.Print "Record " & rsReading!id & ":" & _
" day " & Day(rsReading!mydate), _
"- month " & Month(rsReading!mydate), _
"- year " & Year(rsReading!mydate)
rsReading.MoveNext
Wend
rsReading.Close
Set rsReading = Nothing

adoCnn.Close
Set adoCnn = Nothing
End Sub

.

Relevant Pages

  • Re: Adodb.command date writing issue
    ... > Dim adoCnn As ADODB.Connection ... > Dim dateValue As Date ... > Set adoCmd = New ADODB.Command ... > Set adoCnn = Nothing ...
    (microsoft.public.vb.database.ado)
  • Adodb.command date writing issue
    ... Dim adoCnn As ADODB.Connection ... Dim dateValue As Date ... Set adoCmd = New ADODB.Command ... Set adoCnn = Nothing ...
    (microsoft.public.vb.database.ado)
  • Re: Problem adding record using ADO
    ... >> Dim db As Connection ... >> Dim strDBPath As String ... >> Dim strInsertSQL As String ... > Set adoCmd = New ADODB.Command ...
    (microsoft.public.vb.database.ado)
  • Re: Problem adding record using ADO
    ... > Dim db As Connection ... > Dim strDBPath As String ... > Dim strInsertSQL As String ... Set adoCmd = New ADODB.Command ...
    (microsoft.public.vb.database.ado)