Results 1 to 7 of 7

Thread: Values in Array

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    46

    Values in Array

    Hi

    I want to save list of States from Database like below in an array . If it already exists then it should not be added to array.
    Which the best & fastest way

    Delhi
    Lucknow
    Mumbai
    Gia
    Chennai
    Haryana
    Leh
    Ladakh

    Thanks

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Values in Array

    The most easy way should be to retrieve only the unique values (States) from the database.

  3. #3
    The Idiot
    Join Date
    Dec 2014
    Posts
    2,721

    Re: Values in Array

    one easy way to work with arrays:

    Code:
    Dim myArray() As String
    Dim myArrayLen As Long
    
    Function AddToArray(Name As String) As Long
        Dim i&
    
        For i = 1 To myArrayLen
            If Name = myArray(i) Then AddToArray = i: Exit Function
        Next i
        myArrayLen = myArrayLen + 1
        ReDim Preserve myArray(1 To myArrayLen)
        myArray(myArrayLen) = Name
        AddToArray = myArrayLen
    End Function
    
    Private Sub Form_Load()
        MsgBox AddToArray("Delhi")
        MsgBox AddToArray("Mumbai")
        MsgBox AddToArray("Delhi")
    End Sub
    this "version" tells the position as well. but you could also have a boolean return telling the add was successful or not.
    so, just loop the array you want to copy and add to the new array using a simple function.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: Values in Array

    As Arnout said: Use a SELECT DISTINCT
    If not possible (for whatever reason) then the easiest way is a collection-object with activated Error-Trap (IMO one of the rare situations an OERN is valid) where you add the Value as Item AND key

    Aircode
    Code:
    Private/Public MyStates As Collection
    
    Public Sub AddState(ByVal AState As string)
       On Error Resume Next
       MyStates.Add AState, AState
    End Sub
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Values in Array

    Like mentioned above a select distinct query to retrieve the states and then a ado .Getrows to place that data directly into an array.

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Values in Array

    Example, Ramco:

    Code:
    Option Explicit
    'Add Reference to Microsoft ActiveX Data Objects 2.8 Library
    'Add two commandbuttons to form1 (named cmdLoadArray and cmdShowArrayResults
    'Add listbox (list1) to form 1
    'example database used: (in app.path)
    'Name: StateDB.accdb
    'TableName: States
    'FieldName: StateName (Type Text)
    '(Added your list of states to the database table)
    Dim stateArray() As String
    
    
    Private Sub cmdLoadArray_Click()
        Dim rs As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim cnn As ADODB.Connection
        Dim i As Integer
        Set cnn = New ADODB.Connection
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.12.0"  'THIS is for ACCESS 2007
            .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\stateDB.accdb;"
            .CursorLocation = adUseClient
            .Open
        End With
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = cnn
        cmd.CommandText = "select distinct stateName as dState from states"
        Set rs = cmd.Execute
        ReDim stateArray(rs.RecordCount) As String
        Do While Not rs.EOF
            stateArray(i) = rs!dState
            i = i + 1
            rs.MoveNext
        Loop
    End Sub
    
    
    Private Sub cmdShowArrayResults_Click()
        Dim i As Integer
        For i = 0 To UBound(stateArray) - 1
            List1.AddItem (stateArray(i))
        Next i
    End Sub
    Sam I am (as well as Confused at times).

  7. #7
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Values in Array

    You never mentioned what Database engine you're using.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

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