-
Oct 25th, 2021, 03:31 AM
#1
Thread Starter
Member
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
-
Oct 25th, 2021, 04:42 AM
#2
Re: Values in Array
The most easy way should be to retrieve only the unique values (States) from the database.
-
Oct 25th, 2021, 04:56 AM
#3
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.
-
Oct 25th, 2021, 05:15 AM
#4
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
-
Oct 25th, 2021, 06:24 AM
#5
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.
-
Oct 25th, 2021, 06:52 AM
#6
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).
-
Oct 26th, 2021, 05:11 AM
#7
Re: Values in Array
You never mentioned what Database engine you're using.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|