date insertion error

[Database Administrator]

Hi,

I am developing an ASP VBScript app, and trying the following query via ASP
code:

INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
ADDITIONAL_OWNER, OTHER_OWNER)

VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')

And it is throwing up the following error:

Error Type:
OraOLEDB (0x80040E14)
ORA-01858: a non-numeric character was found where a numeric was expected


But the strange thing is, if I run the same query in SQL Plus, it inserts a
record without throwing any error!

Could anyone shed some light on that?

Thanking you in advance

Regards
--
Yousaf
Linux version 2.4.20-8
gcc version 3.2.2
Red Hat 9

[Database Administrator]

Yousaf wrote:

>Hi,
>
>I am developing an ASP VBScript app, and trying the following query via ASP
>code:
>
>INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
>OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
>ADDITIONAL_OWNER, OTHER_OWNER)
>
>VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
>'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
>And it is throwing up the following error:
>
>Error Type:
>OraOLEDB (0x80040E14)
>ORA-01858: a non-numeric character was found where a numeric was expected
>
>
>But the strange thing is, if I run the same query in SQL Plus, it inserts a
>record without throwing any error!
>
>Could anyone shed some light on that?
>
>Thanking you in advance
>
>Regards

I suspect that OLEDB is not getting the sysdate( there may be a ASP function you need to use instead)- Try using an actual
date ( formatted correctly fro Oracle) instead, and see if you still get the error.


hth,

[Database Administrator]

Yousaf wrote:

>Hi,
>
>I am developing an ASP VBScript app, and trying the following query via ASP
>code:
>
>INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
>OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
>ADDITIONAL_OWNER, OTHER_OWNER)
>
>VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
>'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
>And it is throwing up the following error:
>
>Error Type:
>OraOLEDB (0x80040E14)
>ORA-01858: a non-numeric character was found where a numeric was expected
>
>
>But the strange thing is, if I run the same query in SQL Plus, it inserts a
>record without throwing any error!
>
>Could anyone shed some light on that?
>
>Thanking you in advance
>
>Regards

I suspect that OLEDB is not getting the sysdate( there may be a ASP function you need to use instead)- Try using an actual
date ( formatted correctly fro Oracle) instead, and see if you still get the error.


hth,

[Database Administrator]

Yousaf wrote:

>Hi,
>
>I am developing an ASP VBScript app, and trying the following query via ASP
>code:
>
>INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
>OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
>ADDITIONAL_OWNER, OTHER_OWNER)
>
>VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
>'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
>And it is throwing up the following error:
>
>Error Type:
>OraOLEDB (0x80040E14)
>ORA-01858: a non-numeric character was found where a numeric was expected
>
>
>But the strange thing is, if I run the same query in SQL Plus, it inserts a
>record without throwing any error!
>
>Could anyone shed some light on that?
>
>Thanking you in advance
>
>Regards

I suspect that OLEDB is not getting the sysdate( there may be a ASP function you need to use instead)- Try using an actual
date ( formatted correctly fro Oracle) instead, and see if you still get the error.


hth,

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards


The first thing you do after connecting to oracle in the VB app:
alter session set nls_date_format='DD-MON-YYYY';

.... or whatever date format you like to have in your application as long
as you use that format when you code against the database.

The nls_date_format can be set in several different places. The alter
session overrides them all.




Janne

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards


The first thing you do after connecting to oracle in the VB app:
alter session set nls_date_format='DD-MON-YYYY';

.... or whatever date format you like to have in your application as long
as you use that format when you code against the database.

The nls_date_format can be set in several different places. The alter
session overrides them all.




Janne

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards


The first thing you do after connecting to oracle in the VB app:
alter session set nls_date_format='DD-MON-YYYY';

.... or whatever date format you like to have in your application as long
as you use that format when you code against the database.

The nls_date_format can be set in several different places. The alter
session overrides them all.




Janne

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards

I agree with the other comments but want to point out that SQL*Plus
facilitates what is known as implicit conversion: Oracle will attempt
to make up for your bad coding by changing data types. This is a bad
thing to rely upon and you should always make ALL conversion explicit.

I'd be wraping TO_DATE around the dates and if there are any numeric
fields removing the single quote marks.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards

I agree with the other comments but want to point out that SQL*Plus
facilitates what is known as implicit conversion: Oracle will attempt
to make up for your bad coding by changing data types. This is a bad
thing to rely upon and you should always make ALL conversion explicit.

I'd be wraping TO_DATE around the dates and if there are any numeric
fields removing the single quote marks.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Database Administrator]

Yousaf wrote:

> Hi,
>
> I am developing an ASP VBScript app, and trying the following query via ASP
> code:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER)
>
> VALUES(RISK_NOSeq.nextval, 'ROCKERM' ,'dsfda','HILLJE', SYSDATE, 'H',
> 'TOWDXX', '-','O', '09-AUG-1938', '09-AUG-1938', 'N', '-')
>
> And it is throwing up the following error:
>
> Error Type:
> OraOLEDB (0x80040E14)
> ORA-01858: a non-numeric character was found where a numeric was expected
>
>
> But the strange thing is, if I run the same query in SQL Plus, it inserts a
> record without throwing any error!
>
> Could anyone shed some light on that?
>
> Thanking you in advance
>
> Regards

I agree with the other comments but want to point out that SQL*Plus
facilitates what is known as implicit conversion: Oracle will attempt
to make up for your bad coding by changing data types. This is a bad
thing to rely upon and you should always make ALL conversion explicit.

I'd be wraping TO_DATE around the dates and if there are any numeric
fields removing the single quote marks.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)