I was trying to load a Database table with a list of Countries and their abbreviations. I was first looking at the traditional way of doing it using arrays:
Dim arrCountries(1,246)
arrCountries(0,0) = "US"
arrCountries(0,1) = "United States"
arrCountries(1,0) = "CA"
arrCountries(1,1) = "Canada"
.
.
.
But with 247 Countries and their abbreviations I was looking at declaring almost 500 variables in the array...something I wasn't looking forward to and I thought there had to be a better way.
I started googling the problem and everything I found was explained like the example above. I had at first thought I could do it the way it is done in the Java and C languages:
Dim arrCountries(1,246)
arrCountries(("US,United States"),("CA,Canada"),...,("ZM,Zambia"),("ZR,Zaire"))
That didn't work so I tried some other different ways
Dim arrCountries(1,246)
arrCountries("US","United States","CA","Canada",...,"ZM","Zambia","ZR","Zaire")
arrCountries(("US","United States"),("CA","Canada"),...,("ZM","Zambia"),("ZR","Zaire"))
I even tried:
Dim arrCountries
arrCountries = Array(("US,United States"),("CA,Canada"),...,("ZM,Zambia"),("ZR,Zaire"))
arrCountries = Array("US","United States","CA","Canada",...,"ZM","Zambia","ZR","Zaire")
arrCountries = Array(("US","United States"),("CA","Canada"),...,("ZM","Zambia"),("ZR","Zaire"))
All this did was throw a bunch of errors and after much trial and error what I found was that ASP cannot recognize a multi dimensional array in this manner, it can only see a multi dimensional array like it is shown in the first example.
When I tried to iterate through the Arrays in the tradinional manner:
For ix = 0 to UBound(arrCountries,2)
Response.Write "Abbr: "&arrCountries(0,ix)
Response.Write "Country: "&arrCountries(1,ix)
Next
Only the first example worked, all the others kept giving me the 'Subscript out of range' or 'Missing ")"' or 'Type mismatch [string]' errors
So I had to look at it in 1 dimension...that is a 1 dimensional array of strings.
What I found is that if I used this example
Dim arrCountries
arrCountries = Array("US,United States","CA,Canada",...,"ZM,Zambia","ZR,Zaire")
and treated each element as a string I could work with it using the "split" function in this manner
For ix = 0 to UBound(arrCountries)
x = split(arrCountries(ix),",")
Response.Write "Abbr: "&x(0)
Response.Write "Country: "&x(1)
Next
What is strange here is that the "split" function is an array function Arrays
And if I check it to see if its an array it comes up false:
For ix = 0 to UBound(arrCountries)
Response.Write IsArray(arrCountries(ix))
Next
Summary:
If you have a large amount of data to work with and it is multi dimensional the best (possibly the only) way to do it is:
Dim arrCountries
arrCountries = Array("US,United States","CA,Canada",...,"ZM,Zambia","ZR,Zaire")
For ix = 0 to UBound(arrCountries)
x = split(arrCountries(ix),",")
Response.Write "Abbr: "&x(0)
Response.Write "Country: "&x(1)
Next
BTW...Here is the script I used to get the countries and their abbreviations, just add your connection object and swap out the "Response.Write" with your querystring and you're good to go.
Well that's the nitty gritty of it. I hope you find this article useful!
