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

Discussion in 'ASP General' started by clinttoris@hotmail.com, Jun 15, 2006.

  1. Guest

    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')"
     
    , Jun 15, 2006
    #1
    1. Advertising

  2. Turkbear Guest

    On 15 Jun 2006 12:00:36 -0700, 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' )
     
    Turkbear, Jun 15, 2006
    #2
    1. Advertising

  3. Guest

    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


    Turkbear wrote:
    > On 15 Jun 2006 12:00:36 -0700, 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' )
     
    , Jun 16, 2006
    #3
  4. Turkbear Guest

    On 16 Jun 2006 08:10:27 -0700, wrote:

    >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
    >
    >
    >Turkbear wrote:
    >> On 15 Jun 2006 12:00:36 -0700, 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' )


    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') )
     
    Turkbear, Jun 16, 2006
    #4
  5. Turkbear Guest

    Re: Oracle and asp.....still cannot figure this out. - More info

    On Fri, 16 Jun 2006 10:48:51 -0500, Turkbear <> wrote:

    >On 16 Jun 2006 08:10:27 -0700, wrote:
    >
    >>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
    >>
    >>
    >>Turkbear wrote:
    >>> On 15 Jun 2006 12:00:36 -0700, 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' )

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

    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') )"
     
    Turkbear, Jun 16, 2006
    #5
  6. Re: Oracle and asp.....still cannot figure this out. - More info

    Turkbear wrote:

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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 16, 2006
    #6
  7. Guest

    Turkbear wrote:
    > On 16 Jun 2006 08:10:27 -0700, wrote:
    >
    > >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
    > >
    > >
    > >Turkbear wrote:
    > >> On 15 Jun 2006 12:00:36 -0700, 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' )

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




    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'))"
     
    , Jun 16, 2006
    #7
  8. Pedro Leite Guest

    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
    -----------------------------------------------
     
    Pedro Leite, Jun 16, 2006
    #8
  9. Turkbear Guest

    Re: Oracle and asp.....still cannot figure this out. - More info

    On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]" <> wrote:

    >Turkbear wrote:
    >
    >Please do some snipping instead of making us scroll through all the
    >irrelevant previous replies.



    Sorry about that..but some folks also complain when the 'trail' of thread has been lost..
     
    Turkbear, Jun 16, 2006
    #9
  10. Turkbear Guest

    On 16 Jun 2006 09:19:27 -0700, wrote:

    >
    >Turkbear wrote:
    >> On 16 Jun 2006 08:10:27 -0700, wrote:


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


    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)
     
    Turkbear, Jun 16, 2006
    #10
  11. Turkbear Guest

    On Fri, 16 Jun 2006 17:28:35 +0100, "Pedro Leite" <naoehpreciso> wrote:

    >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
    >-----------------------------------------------
    >


    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...
     
    Turkbear, Jun 16, 2006
    #11
  12. Re: Oracle and asp.....still cannot figure this out. - More info

    Turkbear wrote:
    > On Fri, 16 Jun 2006 12:05:36 -0400, "Bob Barrows [MVP]"
    > <> wrote:
    >
    >> Turkbear wrote:
    >>
    >> Please do some snipping instead of making us scroll through all the
    >> irrelevant previous replies.

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


    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.
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 17, 2006
    #12
  13. Guest

    Turkbear wrote:
    > On 16 Jun 2006 09:19:27 -0700, wrote:
    >
    > >
    > >Turkbear wrote:
    > >> On 16 Jun 2006 08:10:27 -0700, wrote:

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

    >
    > 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)




    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?
     
    , Jun 19, 2006
    #13
  14. Turkbear Guest

    On 19 Jun 2006 05:26:43 -0700, wrote:

    >


    >
    >
    >
    >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?


    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..)
     
    Turkbear, Jun 19, 2006
    #14
  15. Pedro Leite Guest

    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.
    ------------------------------------------------------------
    "Turkbear" <> escreveu na mensagem
    news:p...
    > On 19 Jun 2006 05:26:43 -0700, wrote:
    >
    >>

    >
    >>
    >>
    >>
    >>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?

    >
    > 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..)
    >
    >
     
    Pedro Leite, Jun 19, 2006
    #15
  16. Guest

    Turkbear wrote:
    > On 19 Jun 2006 05:26:43 -0700, wrote:
    >
    > >

    >
    > >
    > >
    > >
    > >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?

    >
    > 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..)


    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 & ")"
     
    , Jun 19, 2006
    #16
  17. Guest

    Pedro Leite wrote:
    > 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.
    > ------------------------------------------------------------
    > "Turkbear" <> escreveu na mensagem
    > news:p...
    > > On 19 Jun 2006 05:26:43 -0700, wrote:
    > >
    > >>

    > >
    > >>
    > >>
    > >>
    > >>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?

    > >
    > > 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..)
    > >
    > >


    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.
     
    , Jun 19, 2006
    #17
  18. Pedro Leite Guest

    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


    <> escreveu na mensagem
    news:...
    >
    > Turkbear wrote:
    >> On 19 Jun 2006 05:26:43 -0700, wrote:
    >>
    >> >

    >>
    >> >
    >> >
    >> >
    >> >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?

    >>
    >> 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..)

    >
    > 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 & ")"
    >
     
    Pedro Leite, Jun 19, 2006
    #18
  19. Pedro Leite Guest

    another thing

    just remembered.

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

    -------------------------------------------------------
    <> escreveu na mensagem
    news:...
    >
    > Turkbear wrote:
    >> On 19 Jun 2006 05:26:43 -0700, wrote:
    >>
    >> >

    >>
    >> >
    >> >
    >> >
    >> >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?

    >>
    >> 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..)

    >
    > 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 & ")"
    >
     
    Pedro Leite, Jun 19, 2006
    #19
  20. Turkbear Guest

    On 19 Jun 2006 08:36:43 -0700, wrote:

    >
    >Turkbear wrote:


    >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 & ")"


    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
     
    Turkbear, Jun 19, 2006
    #20
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Darrel
    Replies:
    6
    Views:
    2,663
    Darrel
    Apr 13, 2006
  2. =?Utf-8?B?cm9kY2hhcg==?=

    still trying to figure out datalist event handler

    =?Utf-8?B?cm9kY2hhcg==?=, Oct 25, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    291
    =?Utf-8?B?cm9kY2hhcg==?=
    Oct 26, 2006
  3. JimmyHoffa
    Replies:
    5
    Views:
    472
    JimmyHoffa
    Jan 7, 2007
  4. Jack

    Cannot figure out error in asp code

    Jack, Jul 25, 2004, in forum: ASP General
    Replies:
    2
    Views:
    199
    Bã§TãRÐ
    Jul 27, 2004
  5. Feyruz
    Replies:
    4
    Views:
    2,297
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page