Results 1 to 14 of 14

Thread: [RESOLVED] Loading DataGrdiView from module

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    15

    Resolved [RESOLVED] Loading DataGrdiView from module

    Sorry if this has been asked somewhere before ... I wasn't quite sure how to phrase what I am looking for and I've never seen an answer to this. I have been fighting with this problem on numerous programs for years and decided it's time to find a solution if there is one.

    My program has 2 forms. Each form has at least 1 datagridview - there are many more but let's use 1 each to keep it simple. To keep things more easily organized all routines to load the datagridviews are located on a module and the database to load the global variable TABLE is a separate class. SQL is also a global variable. dbPath is a global variable pointing to the location of the access database.

    Code:
    (from clsDatabase class)
    Imports System.Data.OleDb
    Public Function GetData() As Boolean
    
     Dim DBConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
     Dim cnn As OleDbConnection = New OleDbConnection(DBConnString)
     
           Try
                cnn.Open()
                OLECmd = New OleDbCommand(SQL, cnn)
                OLEDA = New OleDbDataAdapter(OLECmd)
                TABLE = New DataTable
    
                OLEDA.Fill(TABLE)
    
                cnn.Close()
                Return True
    
            Catch ex As Exception
                If cnn.State = ConnectionState.Open Then cnn.Close()
                Return False
            End Try
    End Function
    If I call the sub LoadFuelGrid (located in the module modGridRoutines) from the frmMain (primary form) and everything works fine - including the NoData sub (also in the module modGridRoutines). However, when I call LoadVendorGrid (also located in the module modGridRoutines) from frmVendorList (a secondary form) the grid on that form is left blank. I've stepped through the code on execution and no errors are flagged but the grid is left blank. If I move the subs LoadVendorGrid and NoData to frmVendorList then everything works fine. While this is a possible solution, it isn't very organized, sloppy and to me, amateurish. However, that's the solution I've had to use for years but I really would like to know what it is I'm doing wrong here. What is so different about frmMain and any secondary form that the module code would work on the main form and not a secondary form even though I clearly send it the location of the grid I want to fill? Error checking is not robust here yet ... just enough to track major errors. There is obviously a lot more code as I've edited nearly all of this so there may be some minor typos .. the code in the program, however does work properly outside of my problem.

    Code:
    Module GridRoutines
    
    ReadOnly db As New ClsDatabase
    
    Public Sub LoadFueldGrid (Grid is located on frmMain - the primary form in the 
    program also where the call to this sub originates)
    
    SQL ="SELECT * FROM FUEL"
    
    If db.GetData() = False Then
        MsgBox("Fuel Data Load Failed", vbCritical, "Database Failure")
        Exit Sub
    End If
    
    If TABLE.Rows.Count = 0 Then
         NoData("Fuel Purchases", frmMain.grdFuel)
         Exit Sub
    End If
    
     With frmMain.grdFuel
    
        .DataSource = TABLE
    
    (continue with formatting instructions ...)
    
    End Sub
    
    Public Sub LoadVendorGrid (Grid is located on frmVendorList - a secondary form 
    and also where this call originates)
    
    SQL="SELECT * FROM VENDORS"
    
    If db.GetData() = False Then
       MsgBox("Cannot Load Vendors - There is a problem with the Database.", vbOKOnly,)
       Exit Sub
    End If
    
    If TABLE.Rows.Count = 0 Then
         NoData("Vendors", frmVendorList.grdVendors)
         Exit Sub
    End If
    
    With frmVendorList.grdVendors
    
                .DataSource = TABLE
    
    (Continued with formatting instructions ... )
    
    End Sub
    
    Private Sub NoData(Col As String, ctrl As DataGridView)
            Dim table As DataTable = New DataTable("TABLE")
            ' Declare variables for DataColumn and DataRow objects. 
            Dim column As DataColumn
    
            ' Create new DataColumn, set DataType, ColumnName  
            column = New DataColumn()
            column.DataType = Type.GetType("System.Int32")
            column.ColumnName = Col + "ID"
            ' Add the column to the table.
            table.Columns.Add(column)
    
            column = New DataColumn()
            column.DataType = Type.GetType("System.String")
            column.ColumnName = Col
            ' Add the column to the table.
            table.Columns.Add(column)
    
            table.Rows.Add(0, "No " + Col + " On File")
    
    With ctrl
    
        .DataSource = table
    
    (continue with formatting instructions ...)
    
    End Sub
    I hope I gave enough information to explain my problem. I'd be surprised if it weren't possible to do what I want through a single module so I suspect it's something I just don't grasp properly. Thank you all in advance!

    Ken
    Last edited by KenB; Feb 3rd, 2018 at 02:51 PM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Loading DataGrdiView from module

    How do you Load/Show frmVendorList? Are you using the default instance or are you creating a new instance. Post the relevant code where you display the form and call LoadVendorGrid.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Loading DataGrdiView from module

    The most likely reason that the second form isn't working as expected is due to issues around Default Instances of forms... that is a long discussion which I wont go into right now, but I would recommend the following changes whether it is actually the problem or not.

    Instead of having the routines in the module "know" the forms/controls they are meant to work with, pass those objects in as parameters. This greatly reduces the chances of the kind of issue you have at the moment, and makes the routines much more reusable.

    Also avoid global variables, and use parameters for them. This reduces clutter, and reduces the chances of getting hard-to-find bugs.


    In terms of clsDatabase, the changes would be like this:
    Code:
    Public Function GetData(theTable as DataTable) As Boolean
      ...
                OLEDA = New OleDbDataAdapter(OLECmd)
                theTable = New DataTable
    
                OLEDA.Fill(theTable)
      ...
    LoadFuelGrid would be like this:
    Code:
    Public Sub LoadFueldGrid (grdFuel as DataGridView)
    
    SQL ="SELECT * FROM FUEL"
    Dim theTable as DataTable
    If db.GetData(theTable) = False Then
        MsgBox("Fuel Data Load Failed", vbCritical, "Database Failure")
        Exit Sub
    End If
    
    If theTable.Rows.Count = 0 Then
         NoData("Fuel Purchases", grdFuel)
         Exit Sub
    End If
    
     With grdFuel
    
        .DataSource = theTable
    
    (continue with formatting instructions ...)
    
    End Sub
    ..and where you call it, add the grid parameter:
    Code:
    LoadFueldGrid (Me.grdFuel)
    (this assumes you call it from the form it is supposed to act on)

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    15

    Re: Loading DataGrdiView from module

    Quote Originally Posted by wes4dbt View Post
    How do you Load/Show frmVendorList? Are you using the default instance or are you creating a new instance. Post the relevant code where you display the form and call LoadVendorGrid.
    From frmMain ...
    Code:
     Private Sub VendorListToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles VendorListToolStripMenuItem.Click
            Dim frmVendorList As New frmVendorList
            frmVendorList.ShowDialog()
            LoadVendorCombos()
        End Sub
    Then on frmVendorList ...
    Code:
    Private Sub frmVendorsList_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            SetColors()
            VendorID = 0
            grpVendor.Enabled = False
            btnActiveVendors.Checked = True
            LoadVendorGrid()
        End Sub

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Loading DataGrdiView from module

    That would be an issue with default instances, and it might work if you simply remove the "Dim frmVendorList" line... but I would still recommend the changes I mentioned in my previous post (while you were typing!).

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Loading DataGrdiView from module

    Ah, I thought so. Your code in the Module loads the data into the Default Instance of frmVendorList. You create a new instance, just because you call it frmVendorList it doesn't take the place of the Default Instance. Think about it, what the scope of this
    Code:
    Dim frmVendorList As New frmVendorList
    your Gridroutine Module can't see the form you created, it's out of scope.

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Loading DataGrdiView from module

    This is only my opinion but if LoadVendorGrid is only called from frmVendorList then it should be in the frmVendorList class. It's the natural place for it to be, you should keep code that only services frmVendorList in frmVendorList. Moving it to a Module serves no purpose and as you have seen, it complicates the code without adding any benefit. If LooadVendorGrid is called from other places in the program then keeping it in a module makes sense.

    As I said, just an opinion.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    15

    Re: Loading DataGrdiView from module

    Well that explains it! I didn't think of that when I was initially writing the code but when I removed the "new" form everything works fine. THANK YOU!

    In general I only use 2 global variables and those are the only ones that are all caps so I can keep them all straight in my mind (TABLE, SQL). At one point I was passing them to the routine that needed them but it seemed like I was working with too many variables (define it in the calling routine, reference it in the call itself then the reference to it in the routine I called not to mention the return) so I went to global for the ones I was using constantly. I've never had confusion with them ... but is that bad form?

    I'll move the parameter of the grid desired to the calling routine - that's more clear.

    If I were to keep the LoadVendorGrid routine on the frmVendorList then I would have to duplicate the NoData sub (or incorporate it into the LoadVendorGrid routine which makes that sub more confusing). I started programming back in the late 60s in Fortran IV and was always aware of duplicating code which took up space that wasn't always available. I also had to be very organized with my code because I might only get 5 minutes at the console daily. (life before monitors with an IBM 360). I also spent a lot of time later in Assembly Language with the Commodore 64 and early Apples where I had to find spots to stash code amid the built-in Basic that was 64K so space was a huge issue. I used to shift code using the floppy disk as I needed it overwriting memory that contained code which was no longer necessary. Those lessons have always stuck with me so that I try to be as frugal with using space as possible and in so doing, I never have leftover and unused code floating around in any of my projects. But that's my thinking on why I combined all grid loading routines into the module.

    Thank you for all your help here. Always something new to learn ...

    Ken

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Loading DataGrdiView from module

    Quote Originally Posted by KenB View Post
    In general I only use 2 global variables and those are the only ones that are all caps so I can keep them all straight in my mind (TABLE, SQL). At one point I was passing them to the routine that needed them but it seemed like I was working with too many variables (define it in the calling routine, reference it in the call itself then the reference to it in the routine I called not to mention the return) so I went to global for the ones I was using constantly. I've never had confusion with them ... but is that bad form?
    It sounds like you are dealing with most of the issues of global variables, but you may still be leaving open the possibility of accidental changes.

    It is possible that you call one routine that modifies the variable, but before using it also call another routine (possibly indirectly) that modifies it. You don't seem like the kind of person who would do that, but it can take some effort to avoid it.

    It's a matter of opinion whether it is worth worrying about for a single-developer system that is "linear" (where only one thing can happen at a time, so no threading etc), but avoiding global variables is considered good practice due to the problems it can cause in other circumstances.

    If I were to keep the LoadVendorGrid routine on the frmVendorList then I would have to duplicate the NoData sub (or incorporate it into the LoadVendorGrid routine which makes that sub more confusing).
    You don't need to duplicate NoData, just change it from Private to Public.

    The code in it only deals with the parameters it is passed, so it wouldn't be a problem... but you may want to alter the name a little to indicate it is for DataGridViews.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: [RESOLVED] Loading DataGrdiView from module

    If I were to keep the LoadVendorGrid routine on the frmVendorList then I would have to duplicate the NoData sub (or incorporate it into the LoadVendorGrid routine which makes that sub more confusing).
    No you wouldn't, you could call NoData from any form. You wouldn't have any extra code. But if you feel more comfortable using this type of design that's fine with me. Good luck.

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Loading DataGrdiView from module

    si,

    Sorry, I keep posting over you. Maybe I shouldn't take so much time between starting a post and finishing it. But at my age I don't do anything fast.

  12. #12

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    15

    Re: [RESOLVED] Loading DataGrdiView from module

    Since I'm only a hobbyist and always alone I should be safe in my limited use of global variables. The SQL statement is always built in a series of lines prior to calling the table loading class and never modified or changed after that so I think I'm pretty safe there. I will, however, do more considering before setting any variable scope to global.

    I'll tinker with things, but I suspect I will keep all the grid loading routines in the module as that makes the most sense to me and I don't have to worry about anyone else. I always leave a good deal of documentation in comments throughout my code because I know I will be going over it again sometime in the future and trying to streamline it or make it more efficient and I don't want to always have to reinvent an algorithm over and over. I suppose at my age that wouldn't be such a bad thing, but there are always new algorithms to develop or challenges to address. Or ways to streamline an existing algorithm or cut out some inconsequential code. But since I only dabble with programming and will never need to worry about someone else understanding what or why I'm doing things, I might as well do what makes the most sense to me - or is most comfortable to me.

    I appreciate you taking the time to look at my problem and offer your insight.

  13. #13

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    15

    Re: Loading DataGrdiView from module

    Quote Originally Posted by wes4dbt View Post
    si,

    Sorry, I keep posting over you. Maybe I shouldn't take so much time between starting a post and finishing it. But at my age I don't do anything fast.
    Hahaha .. tell me about it! I'm a moderator at a pretty large drawing forum elsewhere on the net so I run into cross-posting all the time. Thanks for your help - you've given me lots to think about.

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Loading DataGrdiView from module

    Your use of global variables etc should be fine, and in your circumstances the comfort factor is important

    Quote Originally Posted by wes4dbt View Post
    si,

    Sorry, I keep posting over you. Maybe I shouldn't take so much time between starting a post and finishing it. But at my age I don't do anything fast.
    No worries, I tend to do similar from time to time... and when there is crossover, there does also tend to be extra bits of information in the extra posts, so it's still useful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width