0

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

2
Contributors
3
Replies
6
Views
11 Years
Discussion Span
Last Post by nihilks
0

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'

0

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')) "
0

That's the correct method for doing it

Thanks a lot Aparnesh

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.