Randomly Displaying a Van Gogh Painting

As I was poking around the Web the other day I came across a really neat site, The Vincent van Gogh Gallery. (For those unfamiliar with Vincent van Gogh, he was a European painter who lived during the latter part of the 19th century - be sure to check out this biography if you are interested.)

The Vincent van Gogh Gallery has over 800 paintings of Van Gogh's online for viewing. I'm not much of an art student, but I enjoy Van Gogh's works and had fun poking around the site. After spending some time there I came across an on-line database section which provided an Access database of the paintings on the site! The database contains a single table, tblPaintings, which has the following structure:

tblPaintings
Title	Text(255)
Origin	Text(255)
Date	Text(255)
Medium	Text(255)
Height	Number
Width	Number
F	Text(50)
JH	Text(50)
City	Text(255)
[Museum or Collection]	Text(255)
Country	Text(255)

The F and JH columns are catalogue numbers. A little investigating on the site revealed that the actual images of the paintings contained the following naming format:

http://www.vangoghgallery.com/catalog/images/ValueOfColumnF.jpg

Therefore, the Apricot Trees in Blossom painting which has the value 0399 for its F column in the tblPaintings database table could be viewed at: http://www.vangoghgallery.com/catalog/images/0399.jpg . Now that I had the database and knew how to snarf an image from the Gallery Web site, I decided to make a page that would display a random Van Gogh painting. There are a number of articles on 4Guys that illustrate how to select a random record from a database (for more information be sure to view the FAQ, "How can I pick random records from a table?"). Since I did not have a unique ID field in the database table, my choices were somewhat limited. I could grab the entire table, use the .RecordCount property, and then Move to that record... but that would require snarfing all 800-some-odd rows into a Recordset object each time I wanted to just display one painting!

Since I won't be adding or deleting any rows to this table, it occurred to me that if I could get a series of sequential unique identifiers for each row then I could have my ASP page pick a random number between 1 and the max record number from the table - then, a single SQL statement could be called to grab the information on the record that corresponds to the randomly selected ID I picked. Great, but how do I get a my table to have a sequential ID for each row?

Fortunately this happens automatically when you insert rows into a table that contains a column that is an IDENTITY column (or an AutoNumber column, as it's referred to in Access). So all I needed to do was create a new table that had the same structure as the tblPaintings table (along with an additional column, an IDENTITY column) and then dump all of the rows from tblPaintings to this new table. Before I did this, however, I took a moment to export the tblPaintings table to SQL Server (in Access you can right-click on the table name and choose Export and opt to export to an ODBC datasource (you will then need to choose the DSN of the ODBC datasource you wish to export the table)).

Once I had the tblPaintings in SQL Server I created a new table, tblVangoghPaintings, which had identical column values as tblPaintings along with one additional column, VangoghPaintingsID, an integer IDENTITY column (I also changed the [Museum or Collection] column name to Museum). Once this table was created all I needed to do was dump over the contents from tblPaintings to tblVangoghPaintings. This was accomplished with a single INSERT statement:

INSERT INTO tblVangoghPaintings (Title, Origin, Date, Medium, Height, Width, F, JH, City, Museum, Country) SELECT Title, Origin, Date, Medium, Height, Width, F, JH, City, [Museum or Collection], Country FROM tblPaintings

That INSERT statement will copy all of the rows from tblPaintings to tblVangoghPaintings and give each row in tblVangoghPaintings a unique, sequentially increasing ID stored in the VangoghPaintingsID. At this point all that was left was to write an ASP page that would select a random record from the tblVangoghPaintings table and display the image. The code for this is relatively simple and can be seen below:

<% 'Display a random painting Const maxID = 873 'Choose a value between 1 and 873, inclusive Randomize Timer Dim iValue iValue = Int(Rnd() * maxID) + 1 Dim objRS, objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=<i>MyDSN</i>" Set objRS = objConn.Execute("SELECT * FROM tblVanGoghPaintings " & _ "WHERE VanGoghPaintingsID = " & iValue) %> <center> <h1><%=objRS("Title")%></h1> <img src="http://www.vangoghgallery.com/catalog/images/<%=objRS("F")%>.jpg" border="1"> <p> This <%=objRS("Medium")%> was painted <%=objRS("Date")%> in <%=objRS("Origin")%>. <% 'Clean up... objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %>

[View the live demo!]

Note the hard-coded value of 873 for maxID. Since there are, exactly, 873 records in the tblVangoghPaintings table I could use VBScript to pick a random number between 1 and 873 and then craft my SQL statement to grab that one particular record. Next I display information about the painting as well as the HTML IMG tag to properly display the painting. Recall that the paintings are stored on The Vincent van Gogh Gallery in the format: http://www.vangoghgallery.com/catalog/images/ValueOfColumnF.jpg .

Well, that about wraps it up! Since you have the complete listing of the Van Gogh Gallery paintings in an Access database (or SQL Server database, if you chose to export the table like I did), you can provide all sorts of nifty functionality, such as providing a complete list of links to the paintings, or creating a painting slideshow.

Happy Programming!