Re: Cant use VB6 to addnew or edit Informix 5 columns of datetime type

Jak wrote: > I'm using VB6 and Informix 5 on Solaris. > I have a table with a column named TimeIn of type "datetime hour to > second". I get a Runtime error '3146', ODBC-Call Failed when trying to > enter a value into the TimeIn column. The problem is the delimiter you are using in your datetime strings to separate the date parts. Informix supports only the ANSI datetime standard which uses dashes rather than the more common slash. ALso the ANSI format requires the date fields ordered differently: YYYY-MM-DD hh:mm:ss.fffff Where YYYY is the 4 digit year, MM the month, DD the day, hh - hours (24 hour clock), mm - minutes, ss - seconds, and fffff fractions of a second. Obviously if your DATETIME column does not include fractional seconds or is of lower resolution you only need to include those parts which correspond to the resolution of the column. So the strings should be: "12/25/2005 11:11:11" ===> "2005-12-25 11:11:11" 12/25/2005 1:11:11 PM ===> "2005-12-25 13:11:11" Art S. Kagel > Here is the code > > Dim wksp As DAO.Workspace > Dim db As DAO.Connection > Dim rs As DAO.Recordset > > Set wksp = CreateWorkspace("", "admin", "", dbUseODBC) > wksp.DefaultCursorDriver = dbUseODBCCursor > Set db = wksp.OpenConnection("", dbDriverNoPrompt, False, > "ODBC;DSN=myDSN;UID=myuserid;PWD=mypassword") > > Set rs = db.OpenRecordset("select * from table;", dbOpenDynaset, 0, > dbOptimistic) > > With rs > .addnew > ' !TimeIn = Format$(Now, "HH:MM:SS") > ' !TimeIn = "12/25/2005 11:11:11" > ' !TimeIn = "11:11:11" > ' !TimeIn = Time > ' !TimeIn = #12/25/2005 1:11:11 PM# > ' !TimeIn = #11:11:11 AM# > ' !TimeIn = Now > !TimeIn = Null > .update > End With > > > The only attempt to not return an error was the Null value. > Any help is greatly appreciated. > Thanks, Jak >

When enter "2005-12-25" in VB, it automatically converts to 12/25/2005. I would also except that on a "hour to second" datetime field, that I would only have enter the time part, at least in SQL that works. I'll attempt to force dashes in somehow in VB and see what happens. Thanks