Results 1 to 18 of 18

Thread: [RESOLVED] Help with best practice for loading data

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Resolved [RESOLVED] Help with best practice for loading data

    Hello!

    I am new to VB.NET... trying to convert all my VB6 programs and am trying to update them in the process.

    I have a program that is mostly used as an interface to our main accounting system. It pulls data to view and create reports. I have a couple combo boxes that need to populate from the database for selection in the program. My thought is to somehow pull the information in at the start of the program and keep it in a table somewhere so I can just load the comboboxes from the stored data. It doesn't need to be real time, as the lists will not change much.

    Currently, I am accessing the DB directly when the form loads to populate the combo box. The database is "in the cloud"... just trying to make it run faster.... is a little slow when accessing the database.

    Attached is an image of what I tried to create with the data that I need for two of the comboboxes... I am not certain how to use them or if this is even the correct route.

    If you could steer me in the right direction, it would be greatly appreciated.

    Name:  DataTables.JPG
Views: 254
Size:  25.7 KB

    Thank you in advance!
    Chrissy

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    Since they are essentially static lookup tables, what I would do is create a strongly typed object to represent the table and then store a collection of those objects in the My namespace during your application start.

    For example, the classes would look like this (you'll likely need to update the data types):
    Code:
    Public Class Owner
        Public Property ucode As String
        Public Property ulastname As String
    End Class
    
    Public Class PtrCode
        Public Property HMY As String
        Public Property SCODE As String
        Public Property SDESC As String
        Public Property RPT_COLUMN As String
        Public Property IMPACT As String
    End Class
    Then in your ApplicationEvents.vb file, you could read the data and store them in a collection:
    Code:
    Namespace My
        Partial Friend Class MyApplication
            Public Property Owners As List(Of Owner)
            Public Property PtrCodes As List(Of PtrCode)
    
            Private Sub MyApplication_Startup(sender As Object, e As StartupEventArgs) Handles Me.Startup
                Owners = GetOwners()
                PtrCodes = GetPtrCodes()
            End Sub
    
            Private Function GetOwners() As List(Of Owner)
                Dim owners = New List(Of Owner)
    
                Const commandString = "SELECT OWNER.* FROM OWNER;"
                Try
                    ' create a new connection
                    Using con = New OleDbConnection("My Connection String Here")
    
                        ' create a new command using the connection and command string
                        Using cmd = New OleDbCommand(commandString, con)
                            con.Open()
    
                            Dim reader = cmd.ExecuteReader()
                            While reader.Read()
                                owners.Add(New Owner() With {
                                              .ucode = reader("ucode"),
                                              .ulastname = reader("ulastname")})
                            End While
                            reader.Close()
    
                            con.Close()
                        End Using
                    End Using
                Catch ex As Exception
                    ' uh oh
                    Console.WriteLine(ex.Message)
                End Try
    
                Return owners
            End Function
    
            Private Function GetPtrCodes() As List(Of PtrCode)
                Dim ptrCodes = New List(Of PtrCode)
    
                Const commandString = "SELECT PTRCODE.* FROM PTRCODE;"
                Try
                    ' create a new connection
                    Using con = New OleDbConnection("My Connection String Here")
    
                        ' create a new command using the connection and command string
                        Using cmd = New OleDbCommand(commandString, con)
                            con.Open()
    
                            Dim reader = cmd.ExecuteReader()
                            While reader.Read()
                                ptrCodes.Add(New PtrCode() With {
                                                .HMY = reader("HMY"),
                                                .SCODE = reader("SCODE"),
                                                .SDESC = reader("SDESC"),
                                                .RPT_COLUMN = reader("RPT_COLUMN"),
                                                .IMPACT = reader("IMPACT")})
                            End While
                            reader.Close()
    
                            con.Close()
                        End Using
                    End Using
                Catch ex As Exception
                    ' uh oh
                    Console.WriteLine(ex.Message)
                End Try
    
                Return ptrCodes
            End Function
    
        End Class
    End Namespace
    Now you can bind your ComboBox controls like this:
    Code:
    With ComboBox1
        .DisplayMember = "ulastname",
        .ValueMember = "ucode",
        .DataSource = My.Application.Owners
    End With
    Quick side note - If you can fix your naming convention in the database, I would highly encourage you to do so now.
    Last edited by dday9; Jan 19th, 2021 at 12:39 PM.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    That makes sense...Thank you so much!

    FYI-My company is not the owner of the database... we only have read access to it, so I cannot change the field names.

    Quote Originally Posted by dday9 View Post

    Quick side note - If you can fix your naming convention in the database, I would highly encourage you to do so now.

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    Quote Originally Posted by Chrissy View Post
    FYI-My company is not the owner of the database... we only have read access to it, so I cannot change the field names.
    I feel your pain on this. I am currently on a project with similarly bad naming that we only have read access to.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,819

    Re: Help with best practice for loading data

    If it is a bit slow, you might consider WHEN you load the data, as well. You said that you do it on form load, which may be the earliest opportunity to load the data. If that's the case, then so be it. If that's the first form, and you get to loading the data at the first possible opportunity, then there's not much to be done there. You might make it appear a bit nicer by putting up something like a splash screen while you wait, but that's only a cosmetic improvement. You'll still take the same amount of time, you'd just be giving the user something to look at while they wait.

    If the form that does the loading isn't the very first thing that you do in the application, then there could be some opportunities for loading the data earlier, or loading in a background process.

    If the data is really static, there is also the possibility of caching the data locally and using that while the real data loads in the background. This would get far more complicated pretty quickly, but it's something to consider.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    Quote Originally Posted by Shaggy Hiker View Post
    If it is a bit slow, you might consider WHEN you load the data, as well. You said that you do it on form load, which may be the earliest opportunity to load the data. If that's the case, then so be it. If that's the first form, and you get to loading the data at the first possible opportunity, then there's not much to be done there. You might make it appear a bit nicer by putting up something like a splash screen while you wait, but that's only a cosmetic improvement. You'll still take the same amount of time, you'd just be giving the user something to look at while they wait.

    If the form that does the loading isn't the very first thing that you do in the application, then there could be some opportunities for loading the data earlier, or loading in a background process.

    If the data is really static, there is also the possibility of caching the data locally and using that while the real data loads in the background. This would get far more complicated pretty quickly, but it's something to consider.
    I have a splash screen and plan to load the data into the lists when the program starts. This way the data will be ready when needed.

    Thanks for your input!

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,228

    Re: Help with best practice for loading data

    Quote Originally Posted by dday9 View Post
    I feel your pain on this. I am currently on a project with similarly bad naming that we only have read access to.
    I was recently tangential to a project that not only used Hungarian notation for field names but also, in a penny pinching move, took out all vowels... so the Check For Service Required Flag became bchkfrsvcrqrd ... oh... yeah... and there is no Little Orphan Annie Secret Decoder Ring to go with it either. Either you know to drink your Ovaltine, or you found someone who did.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Wink Re: Help with best practice for loading data

    Quote Originally Posted by techgnome View Post
    I was recently tangential to a project that not only used Hungarian notation for field names but also, in a penny pinching move, took out all vowels... so the Check For Service Required Flag became bchkfrsvcrqrd ... oh... yeah... and there is no Little Orphan Annie Secret Decoder Ring to go with it either. Either you know to drink your Ovaltine, or you found someone who did.


    -tg

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    I added the code as stated above and it works great for loading my combo boxes. However, I am now having an issue with my old code for looping through the combo box. For example, I have a Statement form where the user can select an owner from the combobox or leave blank to run for all owners.

    How do I loop through all the owners in the combobox using this code? I'm sure it is something simple, but for some reason my brain is having trouble making the transition from VB6 to VB.NET!!


    ComboLoad Code:
    1. Public Sub LoadComboBoxWithPartners(ByRef CmbX As ComboBox)
    2.  
    3.         CmbX.Font = myFont
    4.  
    5.         With CmbX
    6.             .Font = myFont
    7.             .DisplayMember = "fullname"
    8.             .ValueMember = "ucode"
    9.             .DataSource = My.Application.Owners
    10.         End With
    11.         CmbX.SelectedIndex = -1
    12.  
    13.     End Sub

    Any guidance is appreciated.
    Thank you!
    Chrissy

  10. #10
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,819

    Re: Help with best practice for loading data

    You probably shouldn't be looking through the combobox at all. I'm feeling that I'm misunderstanding something about the design. It sounds like you want to do something for either ALL owners, or for just the selected owner. There are two ways this could be interpreted, so I see two possible designs that don't have all that much in common.

    The one is where you are doing something anyways, and you may or may not want to restrict by owner. Kind of like whether or not you add a WHERE clause onto a SELECT query. In that case, you'd use the selected item from the combobox if you wanted to deal with just that one owner, and do nothing otherwise.

    The other design would be where you want to do something for each owner in some set of owners, but there always has to be an owner for the action to make sense. In that case, if no owner was selected in the combobox, you'd iterate through all the owners in My.Application.Owners, and if there was an owner selected, then you'd just do the action using the selecteditem from the combobox.

    I'm not sure that I described the difference between those two with any clarity.
    My usual boring signature: Nothing

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    Quote Originally Posted by Shaggy Hiker View Post

    The other design would be where you want to do something for each owner in some set of owners, but there always has to be an owner for the action to make sense. In that case, if no owner was selected in the combobox, you'd iterate through all the owners in My.Application.Owners, and if there was an owner selected, then you'd just do the action using the selecteditem from the combobox.
    Yes... this is what I want.

    This is what I am going for... basically I am creating a statement for the owner. The same code would be applied either to only one owner. Or, if left blank, it loops through the owners in My.Application.Owners and does the same code for each Owner. Basically creating individual reports for all owners versus just one report for one owner.

    Ugh... not sure I explained that well either, but I think you know what i am going for based on the explanation above!

    Chrissy

  12. #12
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    Honestly, that sounds like bad UI design.

    If I were you, I would have a CheckBox above the ComboBox. The CheckBox's Text would be "All Owners". The Enabled property of the ComboBox would be based on the Checked property of the ComboBox.

    Then when the user would go to generate the report(s), it would look something like:
    Code:
    Dim ucodes = New List(Of String)
    If (CheckBoxAllOwners.Checked) Then
        ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
    Else
        ucodes.Add(CmbX.SelectedValue)
    End If
    
    For Each ucode In ucodes
        ' do something
    Next

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    Quote Originally Posted by dday9 View Post
    Honestly, that sounds like bad UI design.

    If I were you, I would have a CheckBox above the ComboBox. The CheckBox's Text would be "All Owners". The Enabled property of the ComboBox would be based on the Checked property of the ComboBox.

    Then when the user would go to generate the report(s), it would look something like:
    Code:
    Dim ucodes = New List(Of String)
    If (CheckBoxAllOwners.Checked) Then
        ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
    Else
        ucodes.Add(CmbX.SelectedValue)
    End If
    
    For Each ucode In ucodes
        ' do something
    Next
    Thank you for the reply, this helps a lot! The design is mimicked after our main accounting system. There are many fields for the user to filter the data on. Checking a box rather than leaving blank seems like an unnecessary step that I would have to add for all filter items. Basically they select an option if they want to filter the results otherwise leave blank to include all.

    Just curious why you think it is a bad design?

  14. #14
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    There is room ambiguity/confusion.

    With the current design, the user has to know ahead of time that an item selected in the ComboBox (the unselected item) behaves differently than others.

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    Quote Originally Posted by dday9 View Post
    There is room ambiguity/confusion.

    With the current design, the user has to know ahead of time that an item selected in the ComboBox (the unselected item) behaves differently than others.
    There is no ambiguity... the instructions literally say... leave blank to run for all partners. This is how all of our reports are. There could be 10 filters on a form... mandatory fields are designated with a blue background. Pretty straightforward from a reporting standpoint... if they include data in the filter, it will filter on that data, otherwise it will pull all.

    This is the best way to have one report with multiple options on how the data is to be presented.

  16. #16
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    There is a level of subjectivity when it comes to UI. Personally, I do not find your design to be intuitive but if it works for you then that is fine.

    You can still leverage the "meat" of my code example only getting rid of the CheckBox:
    Code:
    Dim ucodes = New List(Of String)
    If (CmbX.SelectedIndex = 0) Then ' assumes top-most is "empty" option
        ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
    Else
        ucodes.Add(CmbX.SelectedValue)
    End If
    
    For Each ucode In ucodes
        ' do something
    Next

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    275

    Re: Help with best practice for loading data

    Yes, I was able to tweak your code and it worked great.

    I regards to the UI... I like to keep an open mind and try to see where others are coming from in design. Just trying to understand... you would put a checkbox next to each one of these filter items that says "Run for All" or something to that affect and require the user to check each box every time?

    Attachment 179980

  18. #18
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    10,012

    Re: Help with best practice for loading data

    No, not in that situation.

    I was under the assumption that there was just a single filter (the Owner dropdown). In a situation like in your screenshot, that looks (and presumably behaves) just fine.

Tags for this Thread

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