Thread: oracle date time problem.

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

Code:

        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)
        MsgBox(resultaat)

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?

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
ta.UpdateShipDatesForRGMF(DateTime.Now)



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