Re: VBA ADO Command Date Parameter Not Working

Josh T wrote:
> Hello,
>
> I'm using the PSQL-ODBC driver from Excel 2002 VBA with a ADO Command
> object.  What is the correct way to pass a date into a parameter so it
> gets into the database correctly? - everything I've tried results in
> 1900-05-07 added to the DB instead of the correct date. I also get
> 1900-05-07 in the DB if I leave the date empty.  I've simplified my code
> into the following test case - I've had no problems with other data
> types.  I've already tried changing adDBDate, passing Date objects or
> strings.
>
> Thanks,
> Josh
>
> Sub TestDateInsert()
>     Dim conn As ADODB.Connection
>     Dim cmd As ADODB.Command
>
>     Set conn = New ADODB.Connection
>     conn.Open "DSN=PRHTest"
>
>     Set cmd = New ADODB.Command
>     cmd.ActiveConnection = conn
>     cmd.CommandType = adCmdText
>     cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"
>     cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
> adParamInput, , #11/23/2009#)


>     cmd.Execute , adExecuteNoRecords

It seemd 2 commas are needed, i.e.

    cmd.Execute ,, adExecuteNoRecords

regards,
Hiroshi Inoue

>     Set cmd = Nothing
>     conn.Close
>     Set conn = Nothing
>
> End Sub
>
> Database Table definition:
>
> CREATE TABLE test_table (
>     test_date date NOT NULL
> );
>