Eduard Gamonal

Last month I wrote some code for a Joomla website about foundations. One of my tasks was to create a sidebar widget that displayed some random names. This widget is a wrapper that retrieves information from ASP files, located in another server. MS-Access database. Microsoft one. Terrible one.

When I was 16 or 17 I played a bit with sockets, databases and Visual Basic 6. It was such a pain in the neck, but I never thought it would became that exotic: Microsoft Access doesn’t know what the word «random» means. The expected behaviour for a code like:

SELECT * FROM TABLE WHERE RAND() LIMIT 5

is getting 5 random rows. With MS-Access, with ADODB, it may have a different syntax, but the behaviour should be the same. With just some basic searches I found that something like the following may work:

SELECT TOP 1 * FROM Quotes ORDER BY RND(qID*now())

Well, it didn’t. I tried a few more lines, with no success. In CarlJ.ca I got an explanation:

The conclusion is… set random = ’static’; but I was being paid to make the website show random results, so I did Just use imagination. ASP fans shall excuse me, I don’t really speak that language. I built this code thanks to w3schools’ ASP reference and some examples that had already been written by other coders working on the project. I prefer PHP and C++.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

a.Source = "SELECT* FROM DIRECTORI WHERE Ns = " + Replace(associades__MMColParam, "'", "''") + " ORDER BY IDF DESC"
a.CursorType = 0
a.CursorLocation = 2
a.LockType = 3
a.Open()
associades_total = a.RecordCount
 
 
If (associades_total = -1) Then
  ' count the total records by iterating through the recordset
  'wtf? mysql or postgres next time, please...
  associades_total=0
  While (Not associades.EOF)
    associades_total = associades_total + 1
    a.MoveNext
  Wend
End If
 
dim i
dim max 
'Response.Write("assoc total: " & associades_total & "<br>")
max = int(associades_total / 6) 'I want 6 random results
 
Randomize 
i = int(rnd*max) + 1
 
Response.Write("<ul>")
 
a.MoveFirst
dim c
 'I want 6 random results
for c = 1 to 6 step 1
    a.Move i
    Response.Write("<li>" & associades.Fields.Item("IDF").Value & "</li>")
  next
Response.Write("</ul>")

Or rather use another language, another server, and another database system.

Tags: access, asp, microsoft, sql

This entry was posted on dimarts, març 10th, 2009 at 20:26 and is filed under Anotacions informàtiques. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.