API query not working for some unknown reason

Hi all, I have a particular API Query that isnt working...

	Set SessionLookup=oConn.Execute("SELECT * FROM site_sessions WHERE session_ID="&Session.SessionID)
	If Not SessionLookup.EOF Then
		Session_ID=SessionLookup("session_ID")
	Else
		Set oXMLHTTPLocIP=CreateObject("MSXML2.ServerXMLHTTP")
		LocIP="http://api.ipify.org/"
		oXMLHTTPLocIP.Open "GET",LocIP,False
		oXMLHTTPLocIP.Send
		If oXMLHTTPLocIP.Status="200" Then
			IP=oXMLHTTPLocIP.responseText
			Set oXMLHTTPLoc=CreateObject("MSXML2.ServerXMLHTTP")
			IP=oXMLHTTPLocIP.responseText
			LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
			oXMLHTTPLoc.Open "GET",LocAPI,False
			oXMLHTTPLoc.Send
			If oXMLHTTPLoc.Status="200" Then
				GetTextFromUrlLoc=oXMLHTTPLoc.responseText
				CityLoc=Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
				CountryLoc=Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
			End If
		End If
		oConn.Execute("INSERT INTO site_sessions(session_ID,brand_ID,session_IP,session_geoCity,session_geoCountry,session_timezone,session_date,session_time) VALUES("&Session.SessionID&",'"&SubDomain("brand_ID")&"','"&IP&"','"&CityLoc&"','"&CountryLoc&"','"&Timezone()&"','"&Date()&"','"&Time()&"')")
		Set NewSession=oConn.Execute("SELECT @@IDENTITY AS New_Session_ID")
		Session_ID=Cstr(NewSession("New_Session_ID"))
	End If

Open in new window


I am currently in Malaysia and this API thinks im in Australia.. I am so confused!
Graemewebber4technologiesAsked:
Who is Participating?
 
Graemewebber4technologiesAuthor Commented:
After some playing around, I have got this code to work:

Set oXMLHTTPLoc=CreateObject("MSXML2.ServerXMLHTTP")
LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
oXMLHTTPLoc.Open "GET",LocAPI,False
oXMLHTTPLoc.Send
If oXMLHTTPLoc.Status="200" Then
	GetTextFromUrlLoc=oXMLHTTPLoc.responseText
	CityLoc=Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
	CountryLoc=Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
End If

Open in new window

0
 
ste5anSenior DeveloperCommented:
Well, it works from here:

Set httpIP=CreateObject("MSXML2.ServerXMLHTTP")
LocIP="http://api.ipify.org/"
httpIP.Open "GET",LocIP,False
httpIP.Send
WScript.Echo "httpIP status:", httpIP.Status
If httpIP.Status="200" Then
	IP=httpIP.responseText
	WScript.Echo "httpIP response:", httpIP.responseText
	
	Set httpLocation=CreateObject("MSXML2.ServerXMLHTTP")
	IP=httpIP.responseText
	LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
	httpLocation.Open "GET",LocAPI,False
	httpLocation.Send
	WScript.Echo "httpLocation status:", httpLocation.Status
	If httpLocation.Status="200" Then
		WScript.Echo "httpLocation response:", httpLocation.responseText
		GetTextFromUrlLoc=httpLocation.responseText		
		City = Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
		Country = Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
		WScript.Echo "City: ", City
		WScript.Echo "Country: ", Country
	End If
End If

Open in new window


Run it on the command line using cscript.exe:
Capture.PNG
When it works, then add some debugging output to your ASP code.
0
 
ste5anSenior DeveloperCommented:
Ah, well, then it seems to use an outdated IP to location database. I don't think there can be done anything about it.

Try a different one: ip-api

Set httpIP=CreateObject("MSXML2.ServerXMLHTTP")
LocIP="http://ip-api.com/json"
httpIP.Open "GET",LocIP,False
httpIP.Send
WScript.Echo "httpIP status:", httpIP.Status
If httpIP.Status="200" Then
	IP=httpIP.responseText
	WScript.Echo "httpIP response:", httpIP.responseText	
End If
	

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Graemewebber4technologiesAuthor Commented:
Hi Ste5an, I have integrated the code, however it still shows at that I am in AU, not elsewhere
0
 
ste5anSenior DeveloperCommented:
hmm, another interesting question is: What IP do you use? Maybe your provider is a global company who's reusing IP addresses on a global scale. In such a scenario a simple IP lookup cannot work..
0
 
Graemewebber4technologiesAuthor Commented:
I am currently in UAE (now), just in hotels so I wouldnt think so...
0
 
Graemewebber4technologiesAuthor Commented:
self figured out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.