I am trying to read through a table and for each row execute a few lines of code.
(The lines of code are taking an data type integer column which contains a time HHMM and a data type datetime column which contains a date.

The code combines the time into the date column since they should have both been in a datetime column to begin with.)

I created a Windows Forms Application project.
In that project I created a Dataset containing the table.
I dragged the table to the form creating a DataGridView.
I added a button.
In the buttons Click event I added the forllowing code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
Dim ServedDateTime As DateTime
 For Each row As DataRow In Chelan400DataSet.Tables("Sheriff_Civil_Master").Rows
 Dim timeServed As Integer = CInt(row(21))
 Dim TS As New TimeSpan(timeServed \ 100, timeServed Mod 100, 0)
 ServedDateTime = Convert.ToDateTime(row(39)) + TS
 row(39) = ServedDateTime
 Next
 
End Sub

When I click the button the code runs and updates the date column in the datagridview as I intended.
I then click the save button on the binding navagator toolbar to save the changes to the table.

Private Sub Sheriff_Civil_MasterBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Sheriff_Civil_MasterBindingNavigatorSaveItem.Click
 
Me.Validate()
 Me.Sheriff_Civil_MasterBindingSource.EndEdit()
 Me.TableAdapterManager.UpdateAll(Me.Chelan400DataSet)
 
End Sub

The changes disappear from the Datagridview and don't update the table.

If I type a change directly into the datagridview instead of using code to make the changes, then click the save button on the navagator toolbar, the changes still appear in the datagridview and update the table.

I don't understand why the changes made with code will show in the DGV but disappear when the saved button is clicked but changes typed into the DGV will be saved when the save button is clicked.

Any and all help is very much appreciated.

Wouldn't it be way easier to update your records directly in the db?

What kind of db are you updating?

Wouldn't it be way easier to update your records directly in the db?

What kind of db are you updating?

I am just learning VB after 20+ years with an old language.
I'm using a database on a MS 2008 SQL Server.
I would think it would be easier without even having a form but I haven't learned that yet and using a DataSet bound to a DGV, which is something the books show you right off, seemed very easy.(I should have known better).

I am working through the books I have to learn a simpler way to do this.
Right now I just need to get the time into the date field.

That said, that won't help me understand why if I type data into a cell in the DGV and click the Save button it does update the database and if I use my code to change the data and then click the Save button it doesn't update the database.

Maybe something to do with me reading through the rows in the table instead of the rows in the DGV, which I tried to do but haven't got past the error I get when I try to go that route.

"Value of type 'Date' cannot be converted to 'System.Windows.Forms.DataGridViewCell'"

Dim ServedDateTime As DateTime
        For Each row As DataGridViewRow In scmDataGridView.Rows
            Dim timeCell As DataGridViewCell = row.Cells(21)
            Dim timeServed As Integer = CInt(timeCell.Value)
            Dim TS As New TimeSpan(timeServed \ 100, timeServed Mod 100, 0)
            Dim dateCell As DataGridViewCell = row.Cells(39)
            ServedDateTime = Convert.ToDateTime(dateCell) + TS
            row.Cells(39) = ServedDateTime
        Next

Any thoughts on this would be appreciated.

I'm guessing that you get the error here:

row.Cells(39) = ServedDateTime

And that is because a cell cannot be equal to a value.

Why not change it to

row.Cells(39).value = ServedDateTime

PS: You also don't need the timeCell and dateCell vars. You can use

dim timeServed as integer = CInt(row.Cells(21).Value)

Adam,

Thanks for your advice, you were correct about the error.

I found my problem with the DGV cell that was changed with code not updating in the file. The file was being updated but just not that column. The column was defined as a datatype of Date not DateTime so even though I was updating the field with a DateTime it could only accept the Date portion.

My next issue is with some of the dates showing 12/30/1899 in the database.
Looking at the old system, these were dates with all zeros in them. The data was migrated over to the SQL database through an excel spreadsheet. I've read that Access databases uses 12/30/1899 to represent a zero date and SQL uses 01/01/1900 so it looks like I need to change all the 1899 dates.

Thanks for your help!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.