Results 1 to 2 of 2

Thread: oracle date time problem.

  1. #1
    belspeed is offline VB.NET Forum Newbie
    .NET Framework
    .NET 3.0
    Join Date
    Jun 2010

    oracle date time problem.

    Hello, i have this piece of code that works perfect if i run it.

            Dim Now As Date = Date.Now
            Dim d As String = Format(Now, "dd/MM/yyyy")
            dms = New OracleDataClass.OracleDataClass(dbdms, userdms, passworddms)
            query = "update cts_shipments set shipping_date = '" + d + "' where status in ('RG','MF')"
            'query = "update cts_shipments set shipping_date = TO_CHAR(CURRENT_DATE, 'DD/MM/YYYY') where status in ('RG','MF')"
            query = query.Replace(ControlChars.NewLine, " ").Replace(ControlChars.CrLf, " ").Replace(ControlChars.Tab, " ").Replace("  ", " ").Replace("  ", " ").Replace("  ", " ").Replace(";", "")
            resultaat = dms.ExecuteNonQuery(query)
    however, when i publish it, i get a 'Not a valid month' dB error.

    the first time i raan this code, it was trying to put into the database 10/06/2010, so even if it was trying american format in european format or vica verca, it would not have had a problem with this since 06/10 or 10/06 are avaible in both formats.

    does anyone ever have a similar problem with a published program?

  2. #2
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Probably a difference in the regional settings of the database/computer between the dev and the publish environment

    I'd recommend that you:

    Always store your dates as DATE columns not strings
    Use parameterized queries! Use date parameters
    If you must pass a string containing a date, convert it using the provided functions

    CREATE TABLE example(name VARCHAR2(100), birthday DATE);

    Now in VB code:
    Dim cmd as New OracleCommand("INSERT INTO example VALUES(:name, :birthday)")
    cmd.Parameters.AddWithValue("name", "John Smith")
    cmd.Parameters.AddWithValue("birthday", New Date(2000, 10, 23))

    That's the proper way to do database access. I don't write code like this any more, because I get Visual Studio to write it for me.. it makes things a lot easier, because it gets it right. Follow the DW2 links in my signature, section Creating a Simple Data App - you'll be introduced to tableadapters. To do your code above all I'd do is:

    Open a dataset
    Right click, choose New Query
    Enter UPDATE cts_shipments SET shipping_date = :shipDate WHERE status in ('RG','MF')
    Call it something like "UpdateShipDatesForRGMF"

    Then in my code:

    Dim ta as New QueriesTableAdapter

    It really is that simple when you use Visual Studio to do the data access, it works first time and you can get on wth important coding

    Read the PQ link in my signature


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts