Oracle and asp.....still cannot figure this out.

C

clinttoris

Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"
 
T

Turkbear

Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"
Click to expand...


The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )
 
C

clinttoris

Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.


Thanks again
 
T

Turkbear

Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.


Thanks again

The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )
Click to expand...
Click to expand...

Hi..
Please do not top-post..it makes it difficult to follow the thread..


You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )
 
T

Turkbear

Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.


Thanks again

On 15 Jun 2006 12:00:36 -0700, (e-mail address removed) wrote:

Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"


The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )
Click to expand...
Click to expand...

Hi..
Please do not top-post..it makes it difficult to follow the thread..


You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )
Click to expand...
to further clarify the code needed, be sure to concatenate the Now() with the Sql string accurately..

Something like

to_date(" & Now() & ",'dd/mm/yyyy hh24:mi:ss AM') )"
 
B

Bob Barrows [MVP]

Turkbear wrote:

Please do some snipping instead of making us scroll through all the
irrelevant previous replies.
 
C

clinttoris

Turkbear said:
Hello Turkbear,

This was an excellent suggestion as now I have the insert statement
that I need to be processed into the database. However, now I am
confused as to why it is not inserting. Once I try and actually insert
this into the database I get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-01843: not a valid month
/itsurvey/testSubmission.asp, line 72

Any ideas?

Here is the insert string
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', '16/06/2006 11:09:20 AM')

I have used the function Now() for datetime.


Thanks again

On 15 Jun 2006 12:00:36 -0700, (e-mail address removed) wrote:

Could some please finish this line of code. For the life of me
whatever I try it does not work.

I am trying to insert the current date time into oracle. Now I know
that in oracle if I run

select to_char(sysdate, 'dd/mm/yy hh24:mi:ss') from dual

result would be 15/06/06 14:49:35

Now I want to insert this syntax into my asp code so what I have done
is created a variable and in my insert statement called the variable.
Still does not work

testing = to_char(sysdate, 'dd/mm/yy hh24:mi:ss')
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , "& testing & ", 'user')"

Also, I would like to insert into my insert statement but have no idea
Dim userrequested
userrequested = Request.ServerVariables("LOGON_USER")

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "' , '" &
answerChoices(answerChoice) & "' , to_char('&sysdate&', 'dd/mm/yy
hh24:mi:ss'), 'userrequested')"


The best way I have found to debug concatenated strings ( especially those with single and double quotes) is to use a
Response.Write, so instead of sending your
statement to Oracle, do a

Response.Write(ssqlCheckBox)
assume :
Survey_ID=Meantest
objRS("Question_ID") =23
answerChoices(answerChoice) = Yes
user =JVG
then it will need to end up ( somehow) like this:

Insert into Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_ID)
VALUES ('Meantest','23','Yes',To_char(sysdate,'dd/mm/yy hh24:mi:ss'),'JVG' )
Click to expand...
Click to expand...

Hi..
Please do not top-post..it makes it difficult to follow the thread..


You MUST use the to_date Function with the correct mask , not the raw result on NOW() for the database -
like: to_date(Now(),'dd/mm/yyyy hh24:mi:ss AM').

Doing this wil result it a insert statement something like this:

INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,User_Id)
VALUES (1,'1', 'e', to_date('16/06/2006 11:09:20 AM','dd/mm/yyyy hh24:mi:ss AM') )
Click to expand...



Hi Turkbear,

Sorry about Top posting. I hope I understood you correctly. If not
please indicate waht top posting is. I took it to mean that I posted
my message at the top of the text as opposed to the end. If I am
mistaken please explain.

Thanks for the suggestion. It does insert into the database however
the time is not showing in the database. I still only get the date.
Any other suggestions.
here is the insert statement

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer) VALUES
(" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'))"
 
P

Pedro Leite

hi

just a thought but cant you make the default value on the table set to
system date ?
most likely you have tried it but sometimes we miss the simple solution.
don't even know if is possible in oracle.

hth

Pedro Leite
-----------------------------------------------
 
T

Turkbear

Turkbear wrote:

Please do some snipping instead of making us scroll through all the
irrelevant previous replies.
Click to expand...


Sorry about that..but some folks also complain when the 'trail' of thread has been lost..
 
T

Turkbear

Hi Turkbear,

Sorry about Top posting. I hope I understood you correctly. If not
please indicate waht top posting is. I took it to mean that I posted
my message at the top of the text as opposed to the end. If I am
mistaken please explain.

Thanks for the suggestion. It does insert into the database however
the time is not showing in the database. I still only get the date.
Any other suggestions.
here is the insert statement

ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer) VALUES
(" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'))"
Click to expand...

That was correct...Oracle, by default, will not show the date and time unless you specifically request both ..

Select to_char(datetimefield,'dd/mm/yyyy hh24:mi:ss AM') from table...

To get Just date, but in that format omit the time mask ( the hh24:mi:ss AM part)
To get just the time , use ONLY that part.


It is the 'reverse' of the to_date function..

Oracle , by default, shows dates as DD-MON-YY ( the exact format depends on your NLS_ parameters)
 
T

Turkbear

hi

just a thought but cant you make the default value on the table set to
system date ?
most likely you have tried it but sometimes we miss the simple solution.
don't even know if is possible in oracle.

hth

Pedro Leite
Click to expand...

Yes, you can use a

'Alter table table_name modify column_name defgalt SYSDATE;'

Sql statement so that all future inserts that do not specify a date will insert the current sysdate value.

Good catch...
 
B

Bob Barrows [MVP]

Turkbear said:
Sorry about that..but some folks also complain when the 'trail' of
thread has been lost..
Click to expand...

I did not say to snip it all: just snip the stuff that is irrelevant to the
reply you are making. Leave just enough that we see the context for your
reply.

Yes, this is sometimes a hard judgement to make. But in general it is better
to err on the snip-too-much side rather than the snip-not-enough side.

That said (Eugene), one should always quote just enough of the post to which
you are replying to provide context for your readers.
 
C

clinttoris

Turkbear said:
That was correct...Oracle, by default, will not show the date and time unless you specifically request both ..

Select to_char(datetimefield,'dd/mm/yyyy hh24:mi:ss AM') from table...

To get Just date, but in that format omit the time mask ( the hh24:mi:ss AM part)
To get just the time , use ONLY that part.


It is the 'reverse' of the to_date function..

Oracle , by default, shows dates as DD-MON-YY ( the exact format depends on your NLS_ parameters)
Click to expand...



Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?
 
T

Turkbear

Hmmm, I'm confused on the last statement made only because I have
specified both date and time and it is still only submitting date.
Here is the code

to_char('" & Now() & "','dd/mm/yyyy hh24:mi:ss PM') Any other ideas?
Click to expand...

Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember : To_Date when INSERTING, To_Char when retreiving..)
 
P

Pedro Leite

I REMEMBER

had this trouble before on an MS Product.

it was a month thing

English --> Portuguese

JAN --> JAN
FEV --> FEV
MAR --> MAR
APR --> ABR **
JUN --> JUN
JUL --> JUL
AUG --> AGO **
SEP --> SET
OCT --> OUT **
NOV --> NOV
DEC --> DEZ **

some months were ok, some not, and it was the literal form, ie, trying to
insert ABR when shoule be APR.

Locale was the man.

hth

Pedro Leite From Portugal.
------------------------------------------------------------
 
C

clinttoris

Turkbear said:
Please post the actual output from
Response.Write(ssqlCheckBox )...

And from

Response.Write(Now())

You maty have to assign the output of the Now() function to a variable an duse that in the concatenation:

Dim cdandt = Now();
to_date('" & cdandt & "','dd/mm/yyyy hh24:mi:ss PM') ( Remember : To_Date when INSERTING, To_Char when retreiving..)
Click to expand...

Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"
 
C

clinttoris

Pedro said:
I REMEMBER

had this trouble before on an MS Product.

it was a month thing

English --> Portuguese

JAN --> JAN
FEV --> FEV
MAR --> MAR
APR --> ABR **
JUN --> JUN
JUL --> JUL
AUG --> AGO **
SEP --> SET
OCT --> OUT **
NOV --> NOV
DEC --> DEZ **

some months were ok, some not, and it was the literal form, ie, trying to
insert ABR when shoule be APR.

Locale was the man.

hth

Pedro Leite From Portugal.
Click to expand...

hello Pedro,

Locale. How do I check this. You are referring to the locale on the
server. Any idea of the comand in Oracle to check this? Thanks.
 
P

Pedro Leite

hi

as turkbear suggested, response.write both now() and the sqlstatement
generated by the asp scrip.
still on your side, copy + paste to oracle sql debugger ( whatever ) and
execute it.

what shows ?? errors or execute ok ??

Locale is the country settings on client and server machines ( assuming ms )
check control panel > regional settings. see if there is any difference on
language, date format, something related.

PLeite
 
P

Pedro Leite

another thing

just remembered.

when constructing a date string, i had to enclose the string in hashes "#",
but for sql server.

-------------------------------------------------------
 
T

Turkbear

Turkbear wrote:
Click to expand...
Hello Turkbear,

yes I did assign a variable and I read from the variable and it has not
been working. Here is what you requested.

Here is the output of Response.Write(ssqlCheckBox )...
INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer) VALUES
(1,'1', 'b', to_date('19/06/2006 11:33:06 AM','dd/mm/yyyy hh:mi:ss
PM'))

And here is the output of Response.Write(Curdate)
19/06/2006 11:34:32 AM

here is my syntax
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & Now() & "','dd/mm/yyyy
hh:mi:ss PM'), " & user & ")"
Click to expand...

Shouldn't it be:
CurDate= Now()
ssqlCheckBox = "INSERT INTO
Survey_User_Answer(Survey_Id,Question_Id,Answer_Id,Date_Answer,
user_Id) VALUES (" & SurveyID & ",'" & objRS("Question_Id") & "', '" &
answerChoices(answerChoice) & "', to_date('" & CurDate & "','dd/mm/yyyy hh:mi:ss PM'), " & user & ")"


Now, please show the output you get with a

Select to_char(Date_Answer,'dd/mm/yyyy hh:mi:ss PM') from Survey_User_Answer;

in SqlPlus
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ExecuteScalar with Oracle 1
Java and mySQL 5
Really need help ASAP with ASP, looping, inserting from database SP 2
DBI Oracle error 0
Problems with UPDATE 2
ASP and Access SQL Problem 3
Cannot get to format the date field correctly yet 10
final sub problem 1

Members online

No members online now.
Total: 30 (members: 0, guests: 30)
Robots: 327

Forum statistics

Threads
473,755
Messages
2,569,536
Members
45,007
Latest member
obedient dusk

Latest Threads

Top