datetime format in table update

    General discussion

  • I am trying to update a oracle table from a tableadapter but I get caught up on the date time format.  I am able to insert  the values without any problems but when i want to update the status (string) of a particular entry based on a time that was inserted, I will get an ora-01843 error,  not a valid month.  This isnt making sense to me because I inserted in just fine with the same time that I am now trying to search by. 

    My table has 4 columns (Name (string), ID (decimal), TriggerTime (timestamp), Status (string)) 

    I use the DBDirect insert command :   INSERT INTO "TERStatusLog" (TER, "RuleID","TriggerTime","Status")
                                                                VALUES (:TER, :RuleID, :TriggerTime, :Status)  
    with this I insert with no problems (and the value for TriggerTime is a Vb Date)

    Then I created an custom update called UpdateStatus :  UPDATE "TERStatusLog" SET "Status"= :status
                                                                               WHERE ("RuleID" =:id) AND ("TriggerTime" = TO_DATE(:time,'MM/DD/YYYY HH24:MI:SS'))

    this gives me the 'not a valid month' error  but I dont know why.
    the trigger time at insert would be for example 10/31/2008 10:27:56 AM  when i do the update - I convert to string

    Dim dt as Date = Format(DateTime.Now, "MM/dd/yyyy HH:mm:ss")
    Dim result as Integer = ta.Insert("Test", 0, dt,"Started")

    Dim t_string as string = dt.tostring("MM/dd/yyyy HH:mm:ss")
    result = ta.UpdateStatus("Failed, 0, t_string)

    I started converting it to a string because, it was not returning any errors but it wasnt updating the data, so I figure it has something to with not being able to find the correct date. 
    This update statement looked like this :   UPDATE "TERStatusLog" SET "Status"= :status
                                                                               WHERE ("RuleID" =:id) AND ("TriggerTime" = :time) 

    I also tried the update without the triggertime and just searching by rule id and that works so it has to be something with the datetime.

    Any help as to what i might be doing wrong would be greatly appreciated.  Thanks!!!!

    • Edited by meriano Friday, October 31, 2008 2:45 PM
    • Changed type Riquel_DongModerator Friday, November 07, 2008 2:23 AM don't follow up with the further information
    Friday, October 31, 2008 2:43 PM

All replies

  • Why convert to string only to insert it into the database and have it converted back?

    I'm not familiar with Oracle, but I imagine that, like Sql Server, dates are not stored in any specific format in the database, but rather as a numeric of some sort.  Just use a DateTime variable and be done with it. 

    Also, this line makes no sense:

    Dim dt as Date = Format(DateTime.Now, "MM/dd/yyyy HH:mm:ss")

    DateTime.Now is already a Date, so this line just creates a string and then converts it back into a date!  Just use your date variable in your parameterized sql.

    From the MSDN article for OracleParameter:

    Public Sub AddOracleParameters()
    ' ...
    ' create dataSet and adapter
    ' ...
    adapter.SelectCommand.Parameters.Add("pEName", OracleType.VarChar, 80).Value = "Smith"
    adapter.SelectCommand.Parameters.Add("pEmpNo", OracleType.Int32).Value = 7369
    End Sub

    For a DateTime variable you would use something like this in the parameter:

        adapter.SelectCommand.Parameters.Add("pEmpNo", OracleType.Timestamp).Value = DateTime.Now

    Someone familiar with Oracle can provide a more specific answer.

    Friday, October 31, 2008 3:09 PM
  • Hi Chris

    Thank you for  your response.  I see what you are saying about the format date line.  I was using it because I thought I would need to put it in some specific format. 

    I removed the format and used the as is and it worked, but jsut once.  When I ran it again after that it still returned 0 as a result. 

    I would love to use your suggestion about command parameters but I have to use tableadapters and there have to structure the query in the wizard

    Thanks again
    Friday, October 31, 2008 3:24 PM
  • OK, I am not an oracle guy but I will give it a shot.

    UPDATE "TERStatusLog" SET "Status"= :status  WHERE ("RuleID" =:id) AND ("TriggerTime" = TO_DATE(:time,'MM/DD/YYYY HH24:MI:SS'))

    I see that in all previous assignments = is followed by : and just wonder if this is the problem.  Here what I would try if I am at your shoes.

    UPDATE "TERStatusLog" SET "Status"= :status  WHERE ("RuleID" =:id) AND ("TriggerTime" =: TO_DATE(:time,'MM/DD/YYYY HH24:MI:SS'))


    UPDATE "TERStatusLog" SET "Status"= :status  WHERE ("RuleID" =:id) AND ("TriggerTime" =: TO_DATE(time,'MM/DD/YYYY HH24:MI:SS'))

    Good Luck,


    Friday, October 31, 2008 7:27 PM
  • Thanks for your suggestions.  I have tried both and neither one of them work.  If I use the to_date function, i have to put in a string which means i have to convert my to a string and give that to the tableadapter query,  correct??

       It really confuses me why this is not working because it seems like such a simple task.  

    Thanks again for responding
    Monday, November 03, 2008 3:27 PM
  • Hi meriano,

    I don't use the Oracle database. It is difficult to reproduce your issue. I just recommend that you write the code to use the parameter to update the table first. Also post the method declaration for the insert and update method to see the more information about this issue.

    Best regards,

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, November 05, 2008 9:42 AM