Populating 10G XE table with time AND date through VB.Net

I am writing a VB.Net (2005 on Win XP) application that will put Date & Time data into a Date formatted field of an Oracle 10G XE table.

This SQL statement delivered through VB.Net works fine:

        Insert into Vessel_Data (GPS_Date) Values ('19-Mar-2006')

This SQL statement doesn't:

        Insert into Vessel_Data (GPS_Date) Values ('19-Mar-2006 12:34:38') it gives this error:

        ORA-01830: date format picture ends before converting entire input string

This is fair enough - presumably because the NLS_TIME_FORMAT is wrong format. I tested this by looking at it with:

        select * from nls_session_parameters;

"PARAMETER","VALUE"
"NLS_LANGUAGE","AMERICAN"
"NLS_TERRITORY","AMERICA"
"NLS_CURRENCY","$"
"NLS_ISO_CURRENCY","AMERICA"
"NLS_NUMERIC_CHARACTERS",".,"
"NLS_CALENDAR","GREGORIAN"
"NLS_DATE_FORMAT","DD-MON-RR"
"NLS_DATE_LANGUAGE","AMERICAN"
"NLS_SORT","BINARY"
"NLS_TIME_FORMAT","HH.MI.SSXFF AM"
"NLS_TIMESTAMP_FORMAT","DD-MON-RR HH.MI.SSXFF AM"
"NLS_TIME_TZ_FORMAT","HH.MI.SSXFF AM TZR"
"NLS_TIMESTAMP_TZ_FORMAT","DD-MON-RR HH.MI.SSXFF AM TZR"
"NLS_DUAL_CURRENCY","$"
"NLS_COMP","BINARY"
"NLS_LENGTH_SEMANTICS","BYTE"
"NLS_NCHAR_CONV_EXCP","FALSE"
So, I try to change it in VB.Net with the following command from within VB.Net:

        alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SS';

(This is done within a VB.Net OracleCommand, and I copied & pasted the exact SQL string sent above). However, I get an error

        ORA-00911: invalid character

If I run exactly same statement from within Oracle GUI SQL window I get:

	Statement processed.
	0.12 seconds

So, does anyone know why I would get that error from within VB.Net with exactly the same string working fine through the GUI?

Next problem is, that even if I alter the NLS_TIME_FORMAT parameter from within the Oracle GUI, the NLS_TIME_FORMAT shows no difference if I do another SQL command:

        select * from nls_session_parameters;

still shows:

"PARAMETER","VALUE"
"NLS_LANGUAGE","AMERICAN"
"NLS_TERRITORY","AMERICA"
"NLS_CURRENCY","$"
"NLS_ISO_CURRENCY","AMERICA"
"NLS_NUMERIC_CHARACTERS",".,"
"NLS_CALENDAR","GREGORIAN"
"NLS_DATE_FORMAT","DD-MON-RR"
"NLS_DATE_LANGUAGE","AMERICAN"
"NLS_SORT","BINARY"
"NLS_TIME_FORMAT","HH.MI.SSXFF AM"
"NLS_TIMESTAMP_FORMAT","DD-MON-RR HH.MI.SSXFF AM"
"NLS_TIME_TZ_FORMAT","HH.MI.SSXFF AM TZR"
"NLS_TIMESTAMP_TZ_FORMAT","DD-MON-RR HH.MI.SSXFF AM TZR"
"NLS_DUAL_CURRENCY","$"
"NLS_COMP","BINARY"
"NLS_LENGTH_SEMANTICS","BYTE"
"NLS_NCHAR_CONV_EXCP","FALSE"
Is there any way I can change the NLS_TIMESTAMP_FORMAT (or other variable format if I have this completely wrong) via VB.Net to allow me to input date AND time into my Oracle Date field? If possible I want to do this from SQL code as I don't want to mess around with configuration files manually.

Thanks,

Dave Received on Thu Mar 23 2006 - 08:12:15 CST