quote:

---

Originally posted by bjlt

will you use a country table? If so, how will you use it?

---

quote:

---

Originally posted by bozden

No. I don't see any reason for loosing some performance in joins...

---

<%
Function GetMemberCountry(fMemberID)

	Dim strSql, rsChk, iCountryID
	
	'todo: new field M_COUNTRY_ID in MEMBERS, which is default as 0
	strSql = "SELECT M_COUNTRY_ID "
	strSql = " FROM " & strMemberTablePrefix & "MEMBERS me"
	strSql = " WHERE me.MEMBER_ID = " & SQLVal(fMemberID)

	set rsChk = Server.CreateObject("ADODB.Recordset")
	rsChk.open strSql, my_Conn
	
	if rsChk.bof or rsChk.eof then		
		GetMemberCountry = ""
	else
		iCountryID = FVal(rsChk("M_COUNTRY_ID"))
		GetMemberCountry = GetCountryName(iCountryID)	
	end if 	
	rsChk.close	
	set rsChk = nothing

End Function


Function GetCountryName(fCountryID)
	'this doesn't handle more than one set of country names yet.
	Dim txtCountryListID, txtCountryListName, CountryListID, CountryListName
	Dim strSql, iCountryListCount, allCountryListData
	Dim fld_CountryID, fld_CountryName, i
	Dim iCountryPosition, aryCountryListID, aryCountryListName

	if trim(Application(strApplicationPrefix & "STRCOUNTRYLISTID")) = "" or trim(Application(strApplicationPrefix & "STRCOUNTRYLISTNAME")) = "" then
		txtCountryListID = ""
		txtCountryListName = ""
		'## Forum_SQL - Get Country List from DB
		strSql = "SELECT COUNTRY_ID, COUNTRY_NAME " 
		strSql = strSql & " FROM COUNTRY_LIST "

		set rsCountryList = Server.CreateObject("ADODB.Recordset")
		rsCountryList.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

		if rsCountryList.EOF then
			iCountryListCount = 0
		else
			allCountryListData = rsCountryList.GetRows(adGetRowsRest)
			iCountryListCount = UBound(allCountryListData,2)
		end if

		rsCountryList.close
		set rsCountryList = nothing

		if iCountryListCount <> 0 then
			fld_CountryID = 0
			fld_CountryName = 1

			for i = 0 to iCountryListCount
				CountryListID = allCountryListData(fld_CountryID,i)
				CountryListName = allCountryListData(fld_CountryName,i)
				if txtCountryListName = "" then
					txtCountryListID = CountryListID
					txtCountryListName = CountryListName
				else
					txtCountryListID = txtCountryListID & "," & CountryListID
					txtCountryListName = txtCountryListName & "," & CountryListName
				end if
			next
		end if
		Application.Lock
		Application(strApplicationPrefix & "STRCOUNTRYLISTID") = txtCountryListID
		Application(strApplicationPrefix & "STRCOUNTRYLISTNAME") = txtCountryListName
		Application.UnLock
	end if
	txtCountryListID = Application(strApplicationPrefix & "STRCOUNTRYLISTID")
	txtCountryListName = Application(strApplicationPrefix & "STRCOUNTRYLISTNAME")

	aryCountryListID = split(txtCountryListID, ",")
	aryCountryListName = split(txtCountryListName, ",")

	iCountryPosition = PositionInArray(aryCountryListID, Fval(fCountryID), vbTextCompare)

	GetCountryName = aryCountryListName(iCountryPosition)

End Function


Function PositionInArray(aMyArray, strLookingFor, compare)
'Return the position strLookingFor is found in aMyArray... if
'strLookingFor is not found, -1 is returned.
	
	Dim iUpper, iLoop
	iUpper = UBound(aMyArray)

	For iLoop = LBound(aMyArray) to iUpper
		If compare = vbTextCompare then
			If CStr(UCase(aMyArray(iLoop))) = CStr(UCase(strLookingFor)) then
				PositionInArray = iLoop
				Exit Function
			End If
		Else
			If CStr(aMyArray(iLoop)) = CStr(strLookingFor) then
				PositionInArray = iLoop
				Exit Function
			End If
		End If
	Next

	'Didn't find the element in the array...
	PositionInArray = -1

End Function
 
%>

quote:

---

Originally posted by reinsnitz

The answer to this all is realy quite simple. We only use the ISO list. If it's not in there we most likely won't include it. It is the International standard for country lists.

---