Category Archives: ASP Code

How to create XML from an Access (mdb) database with ASP

OK, so I recently created this code with VBS, but it would work with ASP as well. Basically, you need to place the script in a folder where it can get write permissions to create the XML file. Then you'll connect to the Access database like you would normally. Although to be perfectly honest, you could use this script to create an XML from data in just about any type of database that you can connect to!

'=== declare variables
Dim objConn, strConnect, strSQL, rs, tb, mdbFile, objFSO, xmlFile, objWrite

'=== filename variables
xmlFile = Server.MapPath(""inventory.xml")
mdbFile	= Server.MapPath("database.mdb")

'=== tab character for xml file
tb = chr(9)

'=== instantiate objects
set objFSO  = Server.CreateObject( "Scripting.FileSystemObject" )
Set objConn = Server.CreateObject( "ADODB.Connection" )

'=== connect to database
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile

'=== open/create xml file
If Not objFSO.FileExists( xmlFile ) Then objFSO.CreateTextFile( xmlFile )
set objWrite = objFSO.OpenTextFile( xmlFile, 2 )

'=== open the xml file
objWrite.WriteLine("<?xml version=""1.0"" encoding=""ISO-8859-1""?>")

strSQL = "SELECT * FROM table WHERE 1=1"
Set rs = objConn.Execute(StrSQL)

'=== loop through results
Do While not rs.EOF
    objWrite.WriteLine(tb & "<item>")
    objWrite.WriteLine(tb & tb & "<id>" & rs("id") & "</id>")
    objWrite.WriteLine(tb & tb & "<product>" & replace(rs("product"),"&","&amp;") & "</product>")
    objWrite.WriteLine(tb & tb & "<color>" & rs("color") & "</color>")
    objWrite.WriteLine(tb & tb & "<size>" & rs("size") & "</size>")
    objWrite.WriteLine(tb & "</item>")

'=== finish xml file

Determine a visitor's location by their IP address!

Tracking a visitor's geographical location (ie. country, region, city, latitude, longitude, ZIP code, ISP and domain name) used to be a cool trick. Now it is an integral part of many websites, enabling them to identify the locations from where they're getting the maximum traffic; and tune your webpages accordingly. It also enables you to modify the site according to location, or send users to specific sections (ie. show prices in CAD for Canadian visitors).

It is relatively easy to use this technology on your website with IP2Location's proprietary IP address lookup database. This database is free for the IP-Country version. Of you need more details like region, city, latitude, longitude, ZIP code, ISP and domain name for the IP address, you need to purchase the full database.


Convert an IP address to IP number with PHP, ASP, C# and VB.Net

So you've retrieved the visitor's IP address. If you're just logging or tracing it, that's all you need to do. However, if you want to match it to an IP location database, you can't search by the IP in the IPV4 format. It needs to be converted to its IP number equivalent (it is just more efficient to store and search between a range of numbers in database). Here are the functions in PHP, ASP, C# and VB.Net to convert and IP address from dot format to IP number format.

Each function is based on the fact that IP addresses (IPV4) are divided into 4 sub-blocks. Each sub-block has a different weight number, each powered by 256. Here's the math:

IP Number = (16777216*a) + (65536*b) + (256*c) + (d (1))
– where the IP Address = a.b.c.d

And here are the functions that implement that math:


How to retrieve a visitor's IP address

Every visitor to your site or web application has an IP address. It is quite handy to be able to get that address. It can be used for security logging, or perhaps tracing. It can also be used to determine where they are in the world, or at least where their ISP is.

The difficulty is when they're behind a proxy of some sort, you only see the IP address of the proxy server. So, here are the code snippets in PHP, ASP and .Net that first check for an IP addresses that's forwarded from behind a proxy, and if there's none then just get the IP address.


Create Excel .xls files with ASP

I used to work on a web application that tracked hardware inventory. I was requested to build a feature into the report page to be able to export to Excel (.xls) format.

Initially I looked into the code to instantiate a new spreadsheet object, and build the rows and cells one by one programatically. But I found a much simpler solution.

1. Create your report in HTML table format the same as you would your report page.
2. Strip out any header/footer info from the page, everything except the table.
3. Insert this line of code in the ASP header:

<% response.ContentType ="application/x-excel" %>

The table will be produced in HTML by the ASP server the same as it would on the report page. However, the returned content type is marked as .xls, so it will open in Excel as a spreadsheet!