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
> );
>