Cannot Update Date Field In Database - stores zeros

[Vetrijar]

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.

 

   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!

[eramgarden]

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

[Vetrijar]

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.

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

[Vetrijar]

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.

Edited March 27, 2004 by Vetrijar

[Vetrijar]

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..

[Vetrijar]

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

 

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..