Datetime UPDATE issue

[vb8web]

So here we go...
I am creating a "finance dhasboard" for mycompany and am running into some formatting issues and feel as though i have tried everything... Here is my code..

  Dim i As Integer
        For i = 0 To DataGridView1.Rows.Count - 1

            Using connection As New MySqlConnection("Data Source=mydatasource;User Id=financedashboard;password=mypassword;Persist Security Info=True;database=financedashboard;allowzerodatetime=true")
                Using command As New MySqlCommand
                    Using adapter As New MySql.Data.MySqlClient.MySqlDataAdapter()
                        Using dataset As New DataSet
                            command.CommandText = "UPDATE ar SET trips=@trips, miles=@miles, estowed=@estowed, pricollected=@pricollected, seccollected=@seccollected, totalcollected=@totalcollected, paymentdate=@paymentdate, baddebt=@baddebt, notes=@notes WHERE ID=@ID;"
                            command.Parameters.AddWithValue("@ID", DataGridView1.Rows(i).Cells("ID").Value)
                            connection.Open()
                            command.Connection = connection
                            adapter.SelectCommand = command
                            Try
                                command.Parameters.AddWithValue("@trips", DataGridView1.Rows(i).Cells("NOOFTRIPS").Value)
                                command.Parameters.AddWithValue("@miles", DataGridView1.Rows(i).Cells("TOTALMILEAGE").Value)
                                command.Parameters.AddWithValue("@estowed", DataGridView1.Rows(i).Cells("ESTOWED").Value)
                                command.Parameters.AddWithValue("@pricollected", DataGridView1.Rows(i).Cells("PRIMARYCOLLECTED").Value)
                                command.Parameters.AddWithValue("@seccollected", DataGridView1.Rows(i).Cells("SECONDARYCOLLECTED").Value)
                                command.Parameters.AddWithValue("@totalcollected", Val(DataGridView1.Rows(i).Cells("PRIMARYCOLLECTED").Value) + Val(DataGridView1.Rows(i).Cells("SECONDARYCOLLECTED").Value))
                                If IsDate(DataGridView1.Rows(i).Cells("PAYMENTDATE").Value) = True Then
                                    command.Parameters.AddWithValue("@paymentdate", CDate(DataGridView1.Rows(i).Cells("PAYMENTDATE").Value).ToString("yyyy-MM-dd"))
                                Else
                                    command.Parameters.AddWithValue("@paymentdate", "0000-00-00")
                                End If


                                command.Parameters.AddWithValue("@baddebt", Val(DataGridView1.Rows(i).Cells("ESTOWED").Value) - (Val(DataGridView1.Rows(i).Cells("PRIMARYCOLLECTED").Value) + Val(DataGridView1.Rows(i).Cells("SECONDARYCOLLECTED").Value)))
                                command.Parameters.AddWithValue("@notes", DataGridView1.Rows(i).Cells("NOTES").Value)
                                command.ExecuteNonQuery()


                                connection.Close()
                            Catch exError As MySqlException
                                MsgBox("An Error Occurred. " & exError.Number & " ? " & exError.Message)
                                connection.Close()
                                Exit Sub
                            End Try
                        End Using
                    End Using
                End Using
            End Using

        Next
        MsgBox("Update Complete.", vbOKOnly, "Update Complete")

What I am trying to do is fill the datagridview with information from the database. The paymentdate column is initially a zerodatetime 0000-00-00 to allow the user to know that the paymentdate has not yet been recorded. The column is a Date column in my database. I didn;t know how else to do this because the dashboard needs to develop reports and if payments havent been received yet obviously they will not have a date to it. So it pulls everything fine including dates and when i pull it back with reader.GetMySqlDateTime("paymentdate") the dates come back in the format of 1/1/2013 or 0/0/0000. Well if the user changes the cell value to a date such as 1/2/2013 it Updates fine but the already recorded dates such as 1/2/2013 are recorded as 0000-00-00. Its as if the mysqldatetime format is invalid in the datagridview and VB.NET doesn;t recognize it. I am not sure if i explained this well enough but i need help. Thank you in advance!

Again if i pull some back it shows:
1 | 1/2/2013
2 | 1/3/2013
3 | 1/3/2013
4 | 0/0/0000
5 | 0/0/0000
in the datagridview and when i run this update statement if i change the values to this:
1 | 1/2/2013
2 | 1/3/2013
3 | 1/3/2013
4 | 1/4/2013
5 | 1/5/2013
it ends up in the database as
1 | 0/0/0000
2 | 0/0/0000
3 | 0/0/0000
4 | 1/4/2013
5 | 1/5/2013
as of the original mysql dates were invalid.
My original SELECT statement:

 DataGridView1.Rows.Clear()
        Using connection As New MySqlConnection("Data Source=mydatasource;User Id=financedashboard;password=mypassword;Persist Security Info=True;database=financedashboard;allowzerodatetime=true")
            Using command As New MySqlCommand
                Using adapter As New MySql.Data.MySqlClient.MySqlDataAdapter
                    Using dataset As New DataSet
                        command.CommandText = "SELECT * FROM ar WHERE date BETWEEN '" & DateTimePicker1.Value.ToString("yyyy-MM-dd") & "' AND '" & DateTimePicker2.Value.ToString("yyyy-MM-dd") & "' ORDER BY date;"
                        connection.Open()
                        command.Connection = connection
                        adapter.SelectCommand = command
                        adapter.Fill(dataset, "0")
                        Dim reader As MySql.Data.MySqlClient.MySqlDataReader = command.ExecuteReader
                        Do While reader.Read
                            DataGridView1.Rows.Add(reader("ID"), reader.GetMySqlDateTime("DOT"), reader("prid"), reader("patient"), reader("office"), reader.GetMySqlDateTime("date"), reader("trips"), reader("miles"), reader("payer"), reader("estowed"), reader("pricollected"), reader("seccollected"), reader("totalcollected"), reader.GetMySqlDateTime("paymentdate"), reader("baddebt"), reader("tripnumber"), reader("notes"))
                        Loop


                    End Using
                End Using
            End Using
        End Using

 

[jmcilhinney]

There's a lot wrong with that code. Firstly, with regards to the date column, if there is no value then the field should be NULL, i.e. no value. That will be displayed in the grid as an empty cell, which is what you would expect if there was no value.

What you should be doing is calling Fill on a data adapter to populate a DataTable, binding that to the grid and then calling Update on the same data adapter when you're done. Check out the thread below for the data retrieval and saving bit:

Retrieving and Saving Data in Databases

To bind the data, just set the grid's DataSource property.

 

[vb8web]

There's a lot wrong with that code. Firstly, with regards to the date column, if there is no value then the field should be NULL, i.e. no value. That will be displayed in the grid as an empty cell, which is what you would expect if there was no value.

What you should be doing is calling Fill on a data adapter to populate a DataTable, binding that to the grid and then calling Update on the same data adapter when you're done. Check out the thread below for the data retrieval and saving bit:

Retrieving and Saving Data in Databases

To bind the data, just set the grid's DataSource property.

Awesome! That is a start.. The next problem is that if there is a date pulled back the update is not updating the table properly. It pulls the date back in a format in which is can;t be updated. So it will come back 1/2/2013 from the mysql table but the update statement wont recognize 1/3/2013.

 

[jmcilhinney]

Format SHOULD be irrelevant because binary date values are just numbers. What is the data type of your paymentdate column? It looks like it's actually text, which is not what you should be using for dates.