Skip site navigation (1) Skip section navigation (2)

VBA ADO Command Date Parameter Not Working

From: Josh T <mortonjt(at)rochester(dot)rr(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: VBA ADO Command Date Parameter Not Working
Date: 2009-11-23 15:47:41
Message-ID: 4B0AAE9D.9030201@rochester.rr.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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

     Set cmd = Nothing
     conn.Close
     Set conn = Nothing

End Sub

Database Table definition:

CREATE TABLE test_table (
	test_date date NOT NULL
);

 From psqlodbc.log

[0.000]Driver Version='08.04.0100,200907060001' linking 1400 static
Multithread library
[0.000]Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=255, max_longvarchar_size=8190
[0.016]                disable_optimizer=0, ksqo=1, unique_index=1,
use_declarefetch=0
[0.016]                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
[0.016]                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding=''
[0.578]    [ PostgreSQL version string = '8.3.8' ]
[0.578]    [ PostgreSQL version number = '8.3' ]
[0.578]conn=03044168, query='select oid, typbasetype from pg_type where
typname = 'lo''
[0.594]    [ fetched 0 rows ]
[0.594]    [ Large Object oid = -999 ]
[0.594]    [ Client encoding = 'UTF8' (code = 6) ]
[0.594]conn=03044168,
PGAPI_DriverConnect(out)='DSN=PRHTest;DATABASE=prhtest;SERVER=localhost;PORT=5432;UID=xxx;PWD=xxxxxxxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
[5.750]conn=03044168, query='INSERT INTO test_table (test_date) VALUES
('1900-05-07'::date);'
[7.203]conn=03044168, PGAPI_Disconnect

Responses

pgsql-odbc by date

Next:From: Jan-Peter SeifertDate: 2009-11-23 16:08:05
Subject: SSL timeout?
Previous:From: Hiroshi InoueDate: 2009-11-22 00:21:16
Subject: Re: declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC 8.03.04.00

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group