[RESOLVED] DataGridView won't refresh

[Fedaykin]

I'm throwing myself on the mercy of the court here. I cannot get this DataGridView that is nested in a TabControl Tabpage3 to refresh without refreshing it twice. It's like a zombie DataGridView. I'm opening up a new form and writing dates to the database. When I close the pop-up form I'm calling TabControl1.SelectedIndexChanged which in turn calls this SchTabLoad. Everything fires, but the grid will not refresh until I manually refresh it. I really hope I'm just doing something stupid, because this is driving me nuts.

Dim dtSch As New DataTable 'DataGridView4
    Dim bs4 As New BindingSource
 
    Public Sub SchTabLoad(sender As Object, e As EventArgs)
 
        bs4.ResetBindings(False)
        DataGridView4.DataSource = Nothing
        bs4.DataSource = Nothing
        dtSch.Clear()
 
        'Schedule Tab Production DataGridview4
        Dim sqCon As New SqlClient.SqlConnection(DB)
        Dim sqCmd As New SqlClient.SqlCommand
 
        Dim Adapter As New SqlDataAdapter
        Dim sql As String = "SELECT * FROM  Table"
 
        sqCmd.Connection = sqCon            'create the DB connection 
        sqCon.Open()                        'open the connection
        
        Adapter.SelectCommand = New SqlCommand(sql, sqCon)
        Adapter.Fill(dtSch)
        sqCon.Close()
 
        bs4.DataSource = dtSch
        Me.DataGridView4.DataSource = bs4
        Me.DataGridView4.Refresh()
 
    End Sub

[wes4dbt]

What do you mean by "Manually refresh it" are you talking about the code you posted or some other code you didn't post.

btw - Do you really have a table in your database called "Table"?

[jmcilhinney]

I really hope I'm just doing something stupid

You are. There should be no need to refresh anything. The second form should not be making any changes to the database so there's no need to get anything from the database. The second form should be simply passing the data back to the first form. The first form then adds the data to its own DataTable and anything bound to that DataTable will reflect that. The first form then saves the changes from its DataTable to the database.

[Fedaykin]

Love the quote and reply, I should have seen that coming. However, if it's stupid then I would like to learn why it is stupid.

I do see the efficiency in the second form simply supplying the data to the first form, that's awesome. However, why would this ordering matter and how would it cause the DataGridView not to update? After all, a write to the database is a write to the database, is it not? And if the code to clear and refill the datatable is fired then why won't the DataGridView reflect its new datasource?

[jmcilhinney]

if the code to clear and refill the datatable is fired then why won't the DataGridView reflect its new datasource?

If you do want to do it that way then it shouldn't have a new data source either. Create a DataTable, populate it and bind it to the control. When you want to refresh the data, clear the DataTable and repopulate it. There's no need for a new DataTable or rebinding the control. The grid reflects what's in the data source so simply refresh the existing data source, e.g.

Public Class Form1
 
    Private table As DataTable
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        table = PopulateTable()
        BindingSource1.DataSource = table
        DataGridView1.DataSource = BindingSource1
    End Sub
 
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        RepopulateTable(table)
    End Sub
 
    Private Function PopulateTable() As DataTable
        Dim table As New DataTable
 
        With table.Columns
            table.PrimaryKey = { .Add("Id", GetType(Integer))}
            .Add("Name", GetType(String))
        End With
 
        With table.Rows
            .Add(1, "First")
            .Add(2, "Second")
            .Add(3, "Third")
        End With
 
        Return table
    End Function
 
    Private Sub RepopulateTable(table As DataTable)
        With table.Rows
            .Clear()
            .Add(1, "First")
            .Add(3, "Third 2.0")
            .Add(4, "Fourth")
            .Add(5, "Fifth")
        End With
    End Sub
 
End Class

Run that example and you'll see the first three rows initially and then the grid will refresh with one row gone, one edited and two added when you click the Button. While that will work, you can make it more efficient by preventing the BindingSource raising any events during the repopulation process and then forcing it to raise just one when it's done:

Private Sub RepopulateTable(table As DataTable)
    'Don't raise any events while repopulating.
    BindingSource1.RaiseListChangedEvents = False
 
    With table.Rows
        .Clear()
        .Add(1, "First")
        .Add(3, "Third 2.0")
        .Add(4, "Fourth")
        .Add(5, "Fifth")
    End With
 
    'Commence raising events on changes again.
    BindingSource1.RaiseListChangedEvents = True
 
    'Force a change event now so the grid refreshes.
    BindingSource1.ResetBindings(False)
End Sub

Note that the BindingSource should be added to the form in the designer, just as the DataGridView is.

[Fedaykin]

Thank you for the very detailed explanation. However, I don't understand why my original posted code just called again would not accomplish the same thing. The datatable is cleared, it queries the database and it is bound to the DataGridView as you have described. If the database has been updated how could the original code called again not update it to the DataGridView? Would simply binding the source in the form Designer fix it? I'm not sure what to change here.

[wes4dbt]

Public Sub SchTabLoad(sender As Object, e As EventArgs)

You never answered my first question. Where is this code called from and when. This is not the form load event handler, there is no handles claus "Handles Me.Load".

That said, jmc has given you a better solution, I would suggest going with that.

[Fedaykin]

@wes4dbt - SchTabLoad is called from Me.Load. SchTabLoad is called again from the second form closing. (No, I do not have a Table called 'Table' in the database, my code was just sanitized for posting).

@jms - I applied the BindingSource1.RaiseListChangedEvents = False, BindingSource1.RaiseListChangedEvents = True and BindingSource1.ResetBindings(False) to my code, but no joy. I think if I am reading your solution correctly that calling SchTabLoad again also reassigns the DataGridView4.DataSource again which is what messes everything up? Therefore the datasource should only be assigned once, then make all writes to the datasource and database separately?

[wes4dbt]

I'd suggest you post all the current relevant (actual) code so we don't have to guess what you doing. You don't have to sanitize it, I've had my shots.

[techgnome]

@wes4dbt - SchTabLoad is called from Me.Load. SchTabLoad is called again from the second form closing.

That right there is probably the issue. your secodn form should know noting about your first form. Form 1 should show form 2... form 2 should then close... form 1 should then get what it needs from form 2, and add it to the datatable... and that alone should just update your grid.

My guess is that you're form 2, calling a method which is invoking the hidden default instance of form 1 and adding the data there... that's why it never looks like it's updating to you - you're updating the wrong instance. It "works" when you then save and "reload" because it gets it from the database.

-tg

[Fedaykin]

Okay, that makes sense. I suspected there was some kind of default instance of a form lurking around in the background. Let me reconfigure this and I will post back with what works or doesn't work.

[Fedaykin]

Still not able to make this refresh. I'm trying to send only strings from the second form back to the first form and I have bound the DataGridView to a BindingSource/TableAdapter/DataSet in the form designer.

The second form checks to see if the Work Order number exists and sends these strings and the dbWO boolean back to the first form to update the TableAdapter:

Public Sub UpdateStartDate(WONO As String, StartDate As String, uName As String, dbWO As Boolean)
 
        'Don't raise any events while repopulating.
        KITPRODWIPBindingSource.RaiseListChangedEvents = False
 
        If dbWO = True Then
            KITPRODWIPTableAdapter.UpdateStartDate(StartDate, WONO)
        ElseIf dbWO = False Then
            KITPRODWIPTableAdapter.InsertStartDate(WONO, StartDate, uName)
        End If
 
        'Commence raising events on changes again.
        KITPRODWIPBindingSource.RaiseListChangedEvents = True
 
        'Force a change event now so the grid refreshes.
        KITPRODWIPBindingSource.ResetBindings(False)
 
        'DataGridView4.Update()
        'DataGridView4.Refresh()
        DataGridView4.RefreshEdit()
 
    End Sub

I tried a SELECT statement right after the UPDATE and INSERT statements in the DataSet query and that doesn't work either.

[techgnome]

Still not able to make this refresh. I'm trying to send only strings from the second form back to the first form and I have bound the DataGridView to a BindingSource/TableAdapter/DataSet in the form designer.

The second form checks to see if the Work Order number exists and sends these strings and the dbWO boolean back to the first form to update the TableAdapter:

Ungh.... the second form shouldn't do ANYTHING. Period. Full stop.
All the second form should do is collect the data. The first form should show the second, then get the data from the second form and do all the database stuff. The second form shouldn't do anything to the first.

Maybe at most, it does data validation. And checking that the WO exists. But it shouldn't do any more than that.


-tg

[Fedaykin]

Believe me, I'm as frustrated as you are! ha ha. The code I posted is on the FIRST form. The second form is just a DateTimePicker and a button to change the date.

Second pop-up form code:

Private Sub SetStartBttn_Click(sender As Object, e As EventArgs) Handles SetStartBttn.Click
 
        'Determine if data exists in Table
        Me.StartPicker.CustomFormat = "yyyy-MM-dd"
        Dim StartDate As String = Me.StartPicker.Value
        Dim dbWONO As Boolean = False
        Dim User As String = Environment.UserName
        Dim WONO As String = Me.WONOText.Text
 
        'Determine if the WONO record already exists in table ProjDates
        Dim sqConRd As New SqlClient.SqlConnection(MAINCONN)
        Dim sqCmdRd As New SqlClient.SqlCommand
 
        sqCmdRd.Connection = sqConRd            'create the DB connection 
        sqConRd.Open()                        'open the connection
        sqCmdRd.CommandText = "SELECT WONO FROM ProjDates WHERE WONO = '" & WONO & "'"
 
        Dim sqReader As SqlDataReader = sqCmdRd.ExecuteReader()        'execute the SQL command
        If sqReader.HasRows Then
            While sqReader.Read()
                If Not IsDBNull(sqReader.Item("WONO")) Then
                    dbWONO = True
                End If
            End While
        End If
        sqReader.Close()
        sqConRd.Close()
 
        Call Form12_ProdMeet.UpdateStartDate(WONO, StartDate, User, dbWONO)
 
    End Sub

First Form code:

Public Sub UpdateStartDate(WONO As String, StartDate As String, uName As String, dbWO As Boolean)
     
            'Don't raise any events while repopulating.
            KITPRODWIPBindingSource.RaiseListChangedEvents = False
     
            If dbWO = True Then
                KITPRODWIPTableAdapter.UpdateStartDate(StartDate, WONO)
            ElseIf dbWO = False Then
                KITPRODWIPTableAdapter.InsertStartDate(WONO, StartDate, uName)
            End If
     
            'Commence raising events on changes again.
            KITPRODWIPBindingSource.RaiseListChangedEvents = True
     
            'Force a change event now so the grid refreshes.
            KITPRODWIPBindingSource.ResetBindings(False)
     
            'DataGridView4.Update()
            'DataGridView4.Refresh()
            DataGridView4.RefreshEdit()
     
        End Sub

I believe this might have to do with the fact that the DataGridView is tied to a SQL view with joins rather than a SQL table? The UPDATE statement is writing to a single table that is tied to the SQL view and it works, the data is there if you right-click the same row again, but the row displays the old date. I included a 'exec sp_refreshview [dbo.KITPRODWIP]' statement right after the INSERT and UPDATE statements to try to force a refresh of the view, but still not working.

I really am at a loss, I have worked with many DataGridViews tied to SQL views and there is always some special handling and silly-ness required for updating the grid but this one is stubborn as hell.

[techgnome]

 
        Call Form12_ProdMeet.UpdateStartDate(WONO, StartDate, User, dbWONO)
 
    End Sub

This... this right there... that's exactly what I'm trying to say you should NOT be doing.

in fact, now that I see how that "check to see if the WO exists" flag is used, none of that should be in form 2...

It should be something like this:

Form 1:
button_click()
dim someForm = new Form2
if someForm.ShowDialog() = DialogResult.OK then
   ' Assuming these have been setup as read only properties on Form2
   WONO = someForm.WONO
   StartDate = someForm.StartDate
   User = someForm.User
   dbWONO = someForm.dbWONO
  ' Do the database thing here
End if

Clearly I've over simplified it, but that's the idea... then the code in form2 is simple... there's almost no code... just the properties which return their values. That's it. There should be two buttons, OK & Cancel that should have their FormDialogResult property set at design time to OK and Cancel respectively...

-tg

[Fedaykin]

I have reconfigured the code exactly as you described, but that does not resolve the issue. I have no trouble with code firing, all the methods fire regardless of their location. The DataGridView will not refresh unless it is completely reloaded.

NOTE that we are loading the DataGridView from an SQL VIEW, not a table. AND we are adding columns to that view at run time. Maybe there is no way to refresh this without reloading it?


Form 1:

If ProjDatesForm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            StartDate = ProjDatesForm.StartPicker.Value
            Call UpdateStartDate(WONO, StartDate, UserName, dbWONO)
        End If

Form1:

Public Sub UpdateStartDate(WONO As String, StartDate As String, uName As String, dbWO As Boolean)
 
        'Don't raise any events while repopulating.
        KITPRODWIPBindingSource.RaiseListChangedEvents = False
 
        If dbWO = True Then
            KITPRODWIPTableAdapter.UpdateStartDate(StartDate, WONO)
        ElseIf dbWO = False Then
            KITPRODWIPTableAdapter.InsertStartDate(WONO, StartDate, uName)
        End If
 
        'Commence raising events on changes again.
        KITPRODWIPBindingSource.RaiseListChangedEvents = True
 
        'Force a change event now so the grid refreshes.
        KITPRODWIPBindingSource.ResetBindings(False)
 
        DataGridView4.Refresh()
 
    End Sub

Inside the KITPRODWIPdataset. NOTE that these statements are INSERTing and UPDATEing a single table that is part of the SQL View (not table).

INSERT ProjDates (WONO, StartDate, UserName) VALUES (@WONO, @StartDate, @UserName)
exec sp_refreshview [dbo.KITPRODWIP]

UPDATE ProjDates Set StartDate = @StartDate WHERE WONO = @WONO
exec sp_refreshview [dbo.KITPRODWIP]

[techgnome]

That's because you're never adding it to the datatable... you're just adding it straight to the database.... so, yes, of course you'd need to reload it. Sorry, I didn't realize that sooner.
Usually you'd add the data to the bindingsource... then call the update... the adaptor has the insert and update statements associated with them. Based on the rowstate, it knows if it needs to call insert or update (or delete) ... and will call it for you. But you're bypassing that process and doing it yourself. Meawhile the datatable doesn't know that hte database has changed. Why should it? It's disconnected. It's doesn't know jam from jelly.

Form 1:
button_click()
dim someForm = new Form2
if someForm.ShowDialog() = DialogResult.OK then
   ' Assuming these have been setup as read only properties on Form2
   WONO = someForm.WONO
   StartDate = someForm.StartDate
   User = someForm.User
   dbWONO = someForm.dbWONO
  ' Update the datatable here...
  datatable.update ' this will update the database and datasource and refresh your grid.

End if

-tg

[Fedaykin]

No need to apologize, I'm very grateful for your help and insight. I have relied on this forum for years, I'm the sole programmer for my company so this is an invaluable resource for me.

I could not get the Adapter to generate the UPDATE and INSERT statements because this is a complicated SQL VIEW and not a table. I will need to do some more research on the Adapter to see if there is a manual way to create these statements.

[techgnome]

adaptor.InsertStatement =
adaptor.UpdateStatement =
adaptor.DeleteStatement =

That's all there is... I don't remember if they are strings or not (probably not)... but shouldn't be too hard to figure out what it should be.

-tg

[Fedaykin]

Ahh, okay. Where do you put those? In the wizard?

[Fedaykin]

Okay, I was really already doing the Table Adapter update here as you suggested. I named it UpdateStartDate in the TableAdapter itself. However, since it is a complicated SQL View with joins it will not automatically refresh as it would with a table. Looks like I'm forced to do another fill like this:

Private Sub UpdateStartDate(WONO As String, StartDate As String, uName As String, dbWO As Boolean)
 
        If dbWO = True Then
            KITPRODWIPTableAdapter.UpdateStartDate(StartDate, WONO)
        ElseIf dbWO = False Then
            KITPRODWIPTableAdapter.InsertStartDate(WONO, StartDate, uName)
        End If
 
        Me.KITPRODWIPTableAdapter.Fill(Me.KITPRODWIPdataset.KITPRODWIP)
 
    End Sub

[wes4dbt]

Your code has problems. You use dataadapters on one and TableAdapters on the other. You talk about using databinding to push the changes and now your talking about using properties. Both will work if you use them correctly.

Here is a bare bones examples of using databinding,

Add a datagridview and button to PassBoundData

Imports System.Data.SqlClient

Public Class PassBoundData
    'I always store my connectionstrings in the project Setting
    Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
    Private da As New SqlDataAdapter("Select BookId, BookName, Author From Books ORDER BY BookName", con)
    Private dt As New DataTable

    'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
    Private bs As New BindingSource

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands if your table has a PrimaryKey
    Private cmdBuilder As New SqlCommandBuilder(da)

    Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load

        Try
            con.Open()

            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        Try
            bs.EndEdit()
            da.Update(dt)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub


    Private Sub DataGridView1_RowHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick
        Dim row As DataRowView = DirectCast(bs.Current, DataRowView)

        Dim frm As New ReceiveDataRow(row)

        frm.ShowDialog()
        bs.EndEdit()
    End Sub
End Class

Add two textboxes to ReceiveDataRow

Imports System.ComponentModel

Public Class ReceiveDataRow

    Private rowEdit As DataRowView

    Public Sub New(row As DataRowView)

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        rowEdit = row
    End Sub

    Private Sub ReveiveDataRow_Load(sender As Object, e As EventArgs) Handles Me.Load

        Me.TextBox1.Text = rowEdit("BookName").ToString
        Me.TextBox2.Text = rowEdit("Author").ToString
    End Sub

    Private Sub ReceiveDataRow_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
        rowEdit("BookName") = Me.TextBox1.Text
        rowEdit("Author") = Me.TextBox2.Text
    End Sub
End Class

The changes you make in ReceiveDataRow will be pushed back to PassBoundData and will show in the dgv.

[Fedaykin]

Oh wow! Very interesting, thank you I will try this.

Thank you to everyone who responded, I use DataGridView all the time and I definitely got a few more methods to add to my library.