We help IT Professionals succeed at work.

vb.net How to refresh Main Form to display new data in DVG

Rick Willeford
on
I have a DataViewGrid with dynamic data range. The DVG is on my Main Form. How do I 'refresh' the DVG (or the form)  to display the updated data?
Thanks!
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer

Commented:
You need some kind of event trigger . . . either data_changed (depending on your control this could be selectedindex for dropdown, datetime changed for a date control, text changed for a textbox, button_click for a button, etc).

In the event trigger you would basically call your function to re-run the query and rebind it to the DGV.

Author

Commented:
I am very new to vb.Net, so you may have to be more precise.....

On my Main Form, I click a button to Add A New Office with some data to a list in a range in Excel. (Poor man's database, since I am more comfortable in Excel.)
After running more subs to gather, edit and sort data in Sub Main, I end up by Calling frmMain.PopulateDVG(). That actually adds records to DVG. (Apparently this is an Unbound grid.)

The final step I am missing is to display the updated DVG on the screen. (I can Quit and Reopen the project, and the initialization process creates the new display; but I was hoping for something less drastic and more automated than that.)

Thoughts?
Kyle AbrahamsSenior .Net Developer

Commented:
I would recommend NOT using excel.  As you'll run into file reading / writing issues.  

Sub Main
  'do stuff
  'this should be a function to retrieve and sort data'
    GetData()
' do more stuff
end sub

sub GetData()
 'implement the Logic you have in form main with regards to data retreiving
  PopulateDVG()
end sub

sub populateDVG()
  'clear the grid first
  myDVG.Rows.Clear()
 'then do your logic to add the rows since it's not bound.
end sub

'a button which when clicked calls this event handler which will refresh invoke getdata again.
btnRefresh_Click(byval sender as object, byval e as EventArgs)
    GetData()
end sub

Author

Commented:
Unfortunately, I have too much time into using Excel and it is integrated throughout the existing code.

I think I have gotten as far as repopulating the DGV, and I am down to the one yard line. I just need to refresh the display of DGV in the frmMain. Is there not a simple way to do that?
Most Valuable Expert 2012
Top Expert 2014

Commented:
As I mentioned in your other question, try adding a button on your form and calling PopulateDGV in the click event of that button.

Author

Commented:
But what code goes at the tail end of PopulateDGV to get the form to display the re-populated data?
Or am I missing something?

As I said earlier, if I just Close and Quit and then restart the Project, the revised DGV is displayed. So the data is already there.

Sorry to be dense!!
Thanks for your patience!
Kyle AbrahamsSenior .Net Developer

Commented:
From the looks of it you should be calling this with your populateDVG:

            With Me.dgvOffices 'DataGridView
                Dim loadArray(,) As Object
                Dim k As Integer = 1    'Counter below
                loadArray = wbHomeWorkbook.Sheets("Maint").Range("F3:P9").value
                For i As Integer = 0 To 10 '6 to 16,  Col 0 is first in Array for actual Col. 6
                    For j As Integer = 0 To LastRowNum - 3    ' 3 To LastRowNum
                        If i = 0 Then   'First Col 6 needs new Row.
                            dgvOffices.Rows.Add(0 + k)
                            k = k + 1
                        End If
                        dgvOffices.Rows(j).Cells(i).Value = loadArray(j + 1, i + 1)
                    Next
                Next
            End With

Open in new window

Author

Commented:
Yes, that is what I am doing so far.

I get the impression that you are thinking that that is sufficient, but the new data does not display in the actual grid.
I have a modeless main form that I can see while the program is running the sub. It feels like that form/grid has to be 'refreshed' somehow.
Might it be that, once compiled, the new data will automatically appear?
Kyle AbrahamsSenior .Net Developer

Commented:
How are you doing your updates?  

Normally what I would do is put everything in a datatable, and bind the datatable to the gridview.

Can you add a row to your spreadsheet dynamically?

You may need to close and reopen wbHomeWorkbook for the changes to take effect.  Try to make your program add a row and then see if you see that row while you're processing the spreadsheet.

Author

Commented:
Yes, I am dynamically adding rows to LoadArray range in wbHomeWorkbook.

Background:
I have a separate workbook, say, wbOfficesDatabase. I import about 100 datapoints from individual client/office workbooks into a Working worksheet there, one at a time as new offices appear.
I then transfer that individual office's raw data to another worksheet that contains data for, say, 200 offices. The new office get sorted into the right location there. I always know LastRowUsed and  LastColUsed, so I can create a dynamic range.  
About 10 selected data items (Key Performance Indicators) for each office get transferred from wbOfficesDatabase into a dynamic range in wbHomeWorkbook where reports will get created in Excel. That data feeds the dynamic LoadArray.
All those steps happen automatically when I click a button, Add Office. So I don't really need/want a separate 'Refresh' button if I can help it.

I hope that helps.
Tks!
Kyle AbrahamsSenior .Net Developer

Commented:
You wouldn't have to seperate it out.

That data feeds the dynamic LoadArray.
On your refresh just re-load the array from the dynamic range (this should be a function for readabillity by itself).

After you load the array you should put it onto the grid the same way that you have when you load the form.

Author

Commented:
I do not know where to intercept the Initialization process.

If I try to use brute force and Call ShowMainForm() from Sub Main, I get an error that TextRenderingDefault etc. can't be run because the form is already visible (or something to that effect).

    Sub ShowMainForm()  

        System.Windows.Forms.Application.EnableVisualStyles()
            System.Windows.Forms.Application. _
                 SetCompatibleTextRenderingDefault(False)
            System.Windows.Forms.Application.Run(New frmMain)

So I do not know how to skip that Sub and get into the actual huge initialize code that the system partially created. That is why I first asked if there was a way to just 'refresh/re-initialize' the DGV part.

    Public Sub New()  
        MyBase.New()
        InitializeComponent()  Here is the 1,000 lines of system code get called.
        PopulateDGV()
    End Sub

    Public Sub PopulateDGV()        
        wbHomeWorkbook = xlApp.ActiveWorkbook
        ' LastRowNum = wbHomeWorkbook.Sheets("Maint").Cells(wbHomeWorkbook.Sheets("Maint").Rows.Count, 6).End(XlDirection.xlUp).Row
        'Define range for ClientNames for grid view.
        With wbHomeWorkbook.Sheets("Maint")
            LastRowNum = .Cells(.Rows.Count, 6).End(XlDirection.xlUp).Row
            rngClientNames = .Range(.Cells(3, 6), .Cells(LastRowNum, 16))
            rngClientNames.Interior.ColorIndex = 34
        End With
blah, blah, blah

Thanks again!
Most Valuable Expert 2012
Top Expert 2014
Commented:
What version of .NET framework and Visual Studio are you using?