Populating 10G XE table with time AND date through VB.Net
- From: Dave Baker <newsgroup_poster@xxxxxxxxxx>
- Date: Thu, 23 Mar 2006 22:12:15 +0800
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
.
- Follow-Ups:
- Re: Populating 10G XE table with time AND date through VB.Net
- From: Jim Smith
- Re: Populating 10G XE table with time AND date through VB.Net
- From: Chom Noamsky
- Re: Populating 10G XE table with time AND date through VB.Net
- From: Carlos
- Re: Populating 10G XE table with time AND date through VB.Net
- Prev by Date: Filesystem space not freed when drop tablespace
- Next by Date: empty block vs no. freelist blocks
- Previous by thread: Filesystem space not freed when drop tablespace
- Next by thread: Re: Populating 10G XE table with time AND date through VB.Net
- Index(es):
Relevant Pages
|
