Problem while inserting into oracle date values frm vb

[nihilks]

i use ADO in VB6 to connect to my Oracle database
i made a connection object 'con' and opened the database using it

when i executed the statement
con.Execute "insert into employee(no,join_date) values(424,'12-SEP-2004')"
i found that he new record was inserted into the database(i checked it using sql*plus) . But when retrieved through vb ,the date column showed NULL value

pls help

[nihilks]

i use ADO in VB6 to connect to my Oracle database
i made a connection object 'con' and opened the database using it

when i executed the statement
con.Execute "insert into employee(no,join_date) values(424,'12-SEP-2004')"
i found that he new record was inserted into the database(i checked it using sql*plus) . But when retrieved through vb ,the date column showed NULL value

pls help

Friends , I got my answer

format of insert statement should be :
con.Execute "insert into employee(no,join_date) values(424,'2004/9/12')"

ie date should be given as 'yyyy/mm/dd'

[aparnesh]

Actually this is an incorrect way of doing it. Oracle is accepting the date as 'YYYY-MM-DD' most probably because the default date format of Oracle has been changed to 'YYYY-MM-DD' in your system. If you export this code to a different machine where the default date system is something else, (for example 'DD-MMM-YYYY'), your code will crash.

To be safe, you should explicitly convert the value to a date using the Oracle to_date function. Your code should be something like

con.execute "INSERT INTO EMPLOYEE (No, join_date) VALUES ( 424, to_date('9-12-2004','DD-MM_YYYY')) "

[nihilks]

That's the correct method for doing it

Thanks a lot Aparnesh