ORA_01843: not a valid month

Trying to edit a record in an oracle database and get the "Error editing record. ORA-01843: not a valid month " Error
If Page.IsValid Then
 
            Label2.Text = ""
            'Define ADO.NET objects
            Dim insertSQL As String
            insertSQL = "UPDATE table SET OLD_PERSONNEL_ID = :OLD_PERSONNEL_ID, OLD_CATEGORY = :OLD_CATEGORY, "
            insertSQL += "OLD_NAME = :OLD_NAME, OLD_JOB_TITLE = :OLD_JOB_TITLE, OLD_COMPANY = :OLD_COMPANY, "
            insertSQL += "OLD_PHYSCAL_ACCESS = :OLD_PHYSCAL_ACCESS, OLD_ELECTRONIC_ACCESS_GROUP = :OLD_ELECTRONIC_ACCESS_GROUP,"
            insertSQL += "OLD_PSNL_RISK_ASSESSMENT_DT = :OLD_PSNL_RISK_ASSESSMENT_DT, "
            insertSQL += "OLD_CYBER_SECURITY_TRAINING_DT = :OLD_CYBER_SECURITY_TRAINING_DT, OLD_RECORD_STATUS = :OLD_RECORD_STATUS,"
            insertSQL += "NEW_PERSONNEL_ID = :NEW_PERSONNEL_ID, NEW_NAME = :NEW_NAME, NEW_CATEGORY = :NEW_CATEGORY, "
            insertSQL += "NEW_JOB_TITLE = :NEW_JOB_TITLE, NEW_COMPANY = :NEW_COMPANY, NEW_PHYSCAL_ACCESS = :NEW_PHYSCAL_ACCESS, "
            insertSQL += "NEW_ELECTRONIC_ACCESS_GROUP = :NEW_ELECTRONIC_ACCESS_GROUP, "
            insertSQL += "NEW_PSNL_RISK_ASSESSMENT_DT = :NEW_PSNL_RISK_ASSESSMENT_DT, "
            insertSQL += "NEW_CYBER_SECURITY_TRAINING_DT = :NEW_CYBER_SECURITY_TRAINING_DT, NEW_RECORD_STATUS = :NEW_RECORD_STATUS, "
            insertSQL += "CHANGED_BY = :CHANGED_BY, CHANGE_TIME = :CHANGE_TIME, COMMENTS = :COMMENTS WHERE (CHANGE_TIME = :CHANGE_TIME)"
 
            Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            Dim con As New OracleConnection(ConnectionString)
            Dim cmd As New OracleCommand(insertSQL, con)
 
            ' Add the parameters
            'old
            cmd.Parameters.AddWithValue(":NEW_PERSONNEL_ID", lblNEW_PERSONNEL_ID.Text)
            cmd.Parameters.AddWithValue(":NEW_CATEGORY", lblNEW_CATEGORY.Text)
            cmd.Parameters.AddWithValue(":NEW_NAME", lblNEW_NAME.Text)
            cmd.Parameters.AddWithValue(":NEW_JOB_TITLE", lblNEW_JOB_TITLE.Text)
            cmd.Parameters.AddWithValue(":NEW_COMPANY", lblNEW_COMPANY.Text)
            cmd.Parameters.AddWithValue(":NEW_PHYSCAL_ACCESS", lblNEW_PHYSCAL_ACCESS.Text)
            cmd.Parameters.AddWithValue(":NEW_ELECTRONIC_ACCESS_GROUP", lblNEW_ELECTRONIC_GROUP.Text)
            cmd.Parameters.AddWithValue(":NEW_PSNL_RISK_ASSESSMENT_DT", lblNEW_PSNL_RISK_DT.Text)
            cmd.Parameters.AddWithValue(":NEW_CYBER_SECURITY_TRAINING_DT", lblNEW_CYBER_DT.Text)
            cmd.Parameters.AddWithValue(":NEW_RECORD_STATUS", lblNEW_RECORD_STATUS.Text)
            'new
            cmd.Parameters.AddWithValue(":OLD_PERSONNEL_ID", lblOLD_PERSONNEL_ID.Text)
            cmd.Parameters.AddWithValue(":OLD_CATEGORY", lblOLD_CATEGORY.Text)
            cmd.Parameters.AddWithValue(":OLD_NAME", lblOLD_NAME.Text)
            cmd.Parameters.AddWithValue(":OLD_JOB_TITLE", lblOLD_JOB_TITLE.Text)
            cmd.Parameters.AddWithValue(":OLD_COMPANY", lblOLD_COMPANY.Text)
            cmd.Parameters.AddWithValue(":OLD_PHYSCAL_ACCESS", lblOLD_PHYSCAL_ACCESS.Text)
            cmd.Parameters.AddWithValue(":OLD_ELECTRONIC_ACCESS_GROUP", lblOLD_ELECTRONIC_GROUP.Text)
            cmd.Parameters.AddWithValue(":OLD_PSNL_RISK_ASSESSMENT_DT", lblOLD_PSNL_RISK_DT.Text)
            cmd.Parameters.AddWithValue(":OLD_CYBER_SECURITY_TRAINING_DT", lblOLD_CYBER_DT.Text)
            cmd.Parameters.AddWithValue(":OLD_RECORD_STATUS", lblOLD_RECORD_STATUS.Text)
            'audit
            cmd.Parameters.AddWithValue(":CHANGED_BY", lblCHANGED_BY.Text)
            cmd.Parameters.AddWithValue(":CHANGE_TIME", lblCHANGE_TIME.Text)
            cmd.Parameters.AddWithValue(":COMMENTS", txtComments.Text)
 
 
            'Try to open the database and execute the update
            Dim added As Integer = 0
            Try
                con.Open()
                added = cmd.ExecuteNonQuery()
                'Label11.Text = added.ToString() & " records inserted."
            Catch err As Exception
                Label2.Text = "Error editing record. "
                Label2.Text &= err.Message
            Finally
                con.Close()
            End Try
 
        End If

Open in new window

DotNetNoobyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>            cmd.Parameters.AddWithValue(":CHANGE_TIME", lblCHANGE_TIME.Text)
the lblCHANGE_TIME.Text is string, and not datetime. hence, you allow ado.net + oracle to implicitly "guess" the format, and you get that error message if the string does not match the current (NLS_DATE_xxx) session settings.

you have to make sure the value you pass to AddWithValue is datetime, OR the string value you pass has a given format, and the oracle code will then look like this (for example):
CHANGE_TIME = TO_DATE( :CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS') , 

Open in new window

0
DotNetNoobyAuthor Commented:
So how do I do that when pulling my value from a label?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, a "label/text" is the wrong control to let the user enter dates or times.
better use calendar control, resp dedicated time entry control, so you get the date/time in a controlled format (or directly in a datetime value)
otherwise, you have to use datetime.parse(lblCHANGE_TIME.Text) , eventually an override of that function to specify the culture (which will "specify the format" for the parse) ...

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DotNetNoobyAuthor Commented:
I don't want the user to modify the value I just want them to be able to see it.  So when the value I pull from the label is: 5/26/2009 10:40:41 AM
I will need to datetime.parse(lblCHANGE_TIME.Text) and then my  AddWith Value should work?
            cmd.Parameters.AddWithValue(":CHANGE_TIME", lblCHANGE_TIME.Text)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>just want them to be able to see it.
so, where does it come from?
0
DotNetNoobyAuthor Commented:
I'm selecting a row in a gridview and storing the value from the field as a session variable and sending it to this page where I'm editing
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I mean, where does the VALUE really come from?
I ask, because depending on where it really comes from, you might simply need to pass it around as date, hence avoiding the data type transformation...
0
DotNetNoobyAuthor Commented:
It comes from an ORACLE table
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, from a datetime column?
in that case, just make sure the date goes as date to the session variable, and take it from there for the sql...
0
latorreconsultoresCommented:
as I see, u wanna storage on change_time field where the event ocurrs, if this is correct u must to do the following easy steps:

1. eliminate the following two lines on the construction of ur sql sentence:
-- on insertSQL
CHANGE_TIME = :CHANGE_TIME
-- on Adding parametres:
cmd.Parameters.AddWithValue(":CHANGE_TIME", lblCHANGE_TIME.Text)

2. after that modify the command line of ur insertSQL statement like this:
-- in insertSQL
  insertSQL = "UPDATE table SET CHANGE_TIME=sysdate,OLD_PERSONNEL_ID = :OLD_PERSONNEL_ID, OLD_CATEGORY = :OLD_CATEGORY, "

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
latorreconsultoresCommented:
My comment is rigth,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.