Results 1 to 4 of 4

Thread: Best way to create class to return data as List object

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Minnesota
    Posts
    830

    Best way to create class to return data as List object

    I have a Google spreadsheet that I am reading data from and I want to load the data into a list object so I can use but I am having some issues.

    Google Spreadsheet
    -Worksheet name: Charts
    -Tab: Color_Summarized
    -Column 1: ColorSummary
    -Column 2: ChildID
    -Column 3: ParentID

    sample rows:
    Aqua|29|28
    Blue|49|23
    Yellow|55|28

    My results are
    Yellow Color_Summarized FH 55 28

    QUESTION:
    How do I need to set it up so it keeps all the data loaded instead of just the last instance? Is there a better way then putting in class?

    My code:
    Code:
    Imports Microsoft.VisualBasic
    Imports System.Data
    Imports System.Collections.Generic
    Imports Google.GData.Spreadsheets
    Imports Google.Spreadsheets
    Imports Google.GData.Client
    Imports System.Configuration
    
    Partial Class GoogTest
        Inherits System.Web.UI.Page
    
        Dim service As SpreadsheetsService
    
        Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
            Try 
                For Each gl As GoogleCategoryList In GetGoogleCategoryList()
                    litMsg.Text = gl.Attribute & " " & gl.AttributeType & " " & gl.CategoryVol & " " & gl.ChildID & " " & gl.ParentID & "<br />"
                Next
            Catch ex As Exception
                litMsg.Text = ex.ToString
            End Try
        End Sub
    
    #Region " GetGoogleCategoryList "
        Public Function GetGoogleCategoryList() As List(Of GoogleCategoryList)
    
            'Create an GoogleCategoryList Object
            Dim MyGoogleCategoryList As New GoogleCategoryList()
    
            'Create a List of objects of type GoogleCategoryList
            Dim MyGoogleCategoryListLst As New List(Of GoogleCategoryList)
    
    
            Dim sWorksheetURL As String = "https://spreadsheets.google.com/feeds/worksheets/<yourowngoogleid>/private/full"
            Dim sAppName As String = "Project_App_Name-v1"
            Try
                If service Is Nothing Then
                    service = New SpreadsheetsService(sAppName)
                    'if using 2-step authentication then need to use security created password
                    service.setUserCredentials("..@gmail.com", "abcdefghijklmnopqrstuvwxyz")
                End If
    
                'Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
                Dim sheetQuery As SpreadsheetQuery = New SpreadsheetQuery
    
                'Make a request to the API and get all spreadsheets.
                Dim ssfSpreadsheetFeed As SpreadsheetFeed = service.Query(sheetQuery)
    
                If ssfSpreadsheetFeed.Entries.Count > 0 Then
                    'Choose a spreadsheet more intelligently based on your app's needs
                    Dim sseSpreadSheet As SpreadsheetEntry = ssfSpreadsheetFeed.Entries(0)
    
                    Dim worksheetQuery As WorksheetQuery = New WorksheetQuery(sWorksheetURL)
                    Dim feed As WorksheetFeed = service.Query(worksheetQuery)
                    Dim wfEntries As AtomEntryCollection = feed.Entries
                    For Each wsEntry As WorksheetEntry In wfEntries
                        ' Get the list feed URI
                        Dim listLink As AtomLink = wsEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, AtomLink.ATOM_TYPE)
                        Dim lqQuery As ListQuery = New ListQuery(listLink.HRef.ToString)
                        'lqQuery.setSpreadsheetQuery("name = 'person x' and age > 25")
                        Dim lfResult As ListFeed = service.Query(lqQuery)
    
                        'create new instance of object
                        MyGoogleCategoryList = New GoogleCategoryList()
    
                        Select Case wsEntry.Title.Text
                            
                            Case "Color_Summarized"
                                'Iterate through each row, add column A1 to hashtable
                                For Each leRow As ListEntry In lfResult.Entries
                                    MyGoogleCategoryList.Attribute = leRow.Elements(0).Value
                                    MyGoogleCategoryList.AttributeType = "Color_Summarized"
                                    MyGoogleCategoryList.CategoryVol = "FH"
    'wasn't sure if this is the best way of handling if the value was empty
                                    Try
                                        MyGoogleCategoryList.ChildID = leRow.Elements(1).Value
                                    Catch ex As Exception
                                        MyGoogleCategoryList.ChildID = ""
                                    End Try
    
                                    Try
                                        MyGoogleCategoryList.ParentID = leRow.Elements(2).Value
                                    Catch ex As Exception
                                        MyGoogleCategoryList.ParentID = ""
                                    End Try
                                    MyGoogleCategoryListLst.Add(MyGoogleCategoryList)
                                Next
                        End Select
                    Next
                End If
            Catch ex As Exception
                MyGoogleCategoryList = New GoogleCategoryList()
                MyGoogleCategoryList.Attribute = ex.ToString
                MyGoogleCategoryList.AttributeType = "Error"
                MyGoogleCategoryList.CategoryVol = "ER"
                MyGoogleCategoryList.ChildID = 0
                MyGoogleCategoryList.ParentID = 0
                MyGoogleCategoryListLst.Add(MyGoogleCategoryList)
            End Try
    
            Return MyGoogleCategoryListLst
        End Function
    
    #End Region
    End Class
    
    Public Class GoogleCategoryList
    #Region " Instance variables "
        Dim _Attribute As String = String.Empty
        Dim _ChildID As String = String.Empty
        Dim _ParentID As String = String.Empty
        Dim _AttributeType As String = String.Empty
        Dim _CategoryVol As String = String.Empty
    #End Region
    
    #Region " New "
        Public Sub New()
            'Default class Constructor
        End Sub
    #End Region
    
    #Region " Properties "
        Public Property Attribute As String
            Get
                Return _Attribute
            End Get
            Set(value As String)
                _Attribute = value
            End Set
        End Property
    
        Public Property AttributeType As String
            Get
                Return _AttributeType
            End Get
    
            Set(value As String)
                _AttributeType = value
            End Set
        End Property
    
        Public Property ChildID As String
            Get
                Return _ChildID
            End Get
            Set(value As String)
                _ChildID = value
            End Set
        End Property
    
        Public Property ParentID As String
            Get
                Return _ParentID
            End Get
            Set(value As String)
                _ParentID = value
            End Set
        End Property
    
        Public Property CategoryVol As String
            Get
                Return _CategoryVol
            End Get
            Set(value As String)
                _CategoryVol = value
            End Set
        End Property
    
    #End Region
    End Class

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Best way to create class to return data as List object

    well for starters, you can stop overwritting it each time you're in the loop
    Code:
                For Each gl As GoogleCategoryList In GetGoogleCategoryList()
                    litMsg.Text = gl.Attribute & " " & gl.AttributeType & " " & gl.CategoryVol & " " & gl.ChildID & " " & gl.ParentID & "<br />"
                Next
    Each time you go through the loop, you overwriting what you did in the last loop... I suspect that isn't what you want... but rather you should be ADDING to litMsg instead.

    -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??? *

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Minnesota
    Posts
    830

    Re: Best way to create class to return data as List object

    Embarrassing :-) That did help but now my result count is right but the output for each is the last row.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2000
    Location
    Minnesota
    Posts
    830

    Re: Best way to create class to return data as List object

    Figured it out.

    Needed to do it like so:

    Code:
    For Each leRow As ListEntry In lfResult.Entries
              'create new instance of object
              MyGoogleCategoryList = New GoogleCategoryList()

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