Cannot Update Date Field In Database - stores zeros

V

Vetrijar

Regular
Joined
Sep 1, 2003
Messages
55
Location
Texas
I'm having a really hard time trying to update my database with a date/time stamp.
Using a 'string' storage, I can store date/time but then I can't sort the date's because
it's in string format rather than datetime format in the database.

Right now I'm using NaviCat to manage the database.
The database is for the date field called 'LeafDayTime' is set to store a 'datetime' value.

In visual basic.net, I created an asmx project that accesses the database.
The Dataset's name is 'InfoSet' in which one of the values in the schema is called 'LeafDayTime'.
This also is a 'DateTime' value.

The subroutine I created shows the following to update the database.

Visual Basic: 
    Private Function LeafofTheDay_Set(ByVal name As String) As String
        InfoSystem.InsertCommand.CommandText = "UPDATE Info SET LeafoftheDay = '" & name & "', LeafDayTime = '" & Now & "' "
        ' Update Info
        InfoSystem.InsertCommand.Connection.Open()
        InfoSystem.InsertCommand.ExecuteNonQuery()
        InfoSystem.InsertCommand.Connection.Close()
    End Function

What happens is that the database stores the name correctly and the date as 0/0/0000.

I've tried storing as Now.ToOaDate as another site suggested. doesn't work.

Only way around this is changing everything to a string for the date storing,
but then I'm back to square one in which I cannot sort the database by datetime

Please any help on this would be appreciated!
 
If this is SQL database, can you use "getdate()" ? I think if you use NOW, that's the client machine's datetime..if you use "getdate()"..that's the server's datettime..

InfoSystem.InsertCommand.CommandText = "UPDATE Info SET LeafoftheDay = '" & name & "', LeafDayTime =getdate()
 
Didn't work with the Getdate :-(

Here's the error I get

System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at SecureXML.Service1.LeafofTheDay_Set(String name)
at SecureXML.Service1.LeafofTheDay()


Here's the code I put in there.
Visual Basic: 
Private Function LeafofTheDay_Set(ByVal name As String) As String
        InfoSystem.InsertCommand.CommandText = "UPDATE Info SET LeafoftheDay='" & name & "', LeafDayTime = getdate()"

        ' Update Info
        InfoSystem.InsertCommand.Connection.Open()
        InfoSystem.InsertCommand.ExecuteNonQuery()
        InfoSystem.InsertCommand.Connection.Close()
    End Function
 
Help again !

Tried some more with it. still didn't work but I was able to get rid of that
error message.

Now it still stores the 0/00/0000 datetime stamp.

I forgot the qoute 'getdate()'


I've heard about some people getting 1/1/1900 as a number,
mine is 0/0/0000 instead. Not sure what's going on here!

Didn't think it would be this complex to store the current date!

--- forgot to add ---
It's a MySQL database. not sure if that's different.
 
Last edited:
Anybody out there know how to store the date ? I know I can't be the
only one that wants to store a date in a database..
 
Found Answer

Think I finally found a fix!

Found information on this site



here's what my code looks like now. and it stores the date FINALLY. it was the date format.
http://authors.aspalliance.com/aspxtreme/aspnet/types/datetimeformatstrings.aspx

Visual Basic: 
Dim mydate As Date
        mydate = CDate(data)

        '  0000-00-00 00:00:00  <--- database format needed
        DataTestSystem.InsertCommand.CommandText = "INSERT DataTest SET mydate = '" & mydate.ToString("u") & "'"
        ' Update Info
        DataTestSystem.InsertCommand.Connection.Open()
        DataTestSystem.InsertCommand.ExecuteNonQuery()
        DataTestSystem.InsertCommand.Connection.Close()

Now just need to make sure it will sort correctly..
 
Back
Top