VBA ADO Command Date Parameter Not Working
От | Josh T |
---|---|
Тема | VBA ADO Command Date Parameter Not Working |
Дата | |
Msg-id | 4B0AAE9D.9030201@rochester.rr.com обсуждение исходный текст |
Ответы |
Re: VBA ADO Command Date Parameter Not Working
Re: VBA ADO Command Date Parameter Not Working |
Список | 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
В списке pgsql-odbc по дате отправления: