Select top random records!?

[ManicCW]

Hi,
I'm trying to select 3 random records from access database usign asp.net oledbcommand. I select records from query.
This is the command:

[SIZE=2]"SELECT TOP 3 ID, Name, Title, CreationTime " & _
"FROM RandomImages"

[/SIZE]
This is the RandomImages query in access database:

SELECT Images.*
FROM Images
ORDER BY Rnd(IsNull(ID)*0+1);

The problem with this is that it selects the same 3 records every time. It works fine in database but not on page!?

I found somewhere that i can put function in access database module that randomizes but that only works in database, on page it throws an error that function does not exists.
Here is that function

Function Randomizer () As Integer    
Static AlreadyDone As Integer
    	If AlreadyDone = False Then Randomize : AlreadyDone = True    
	Randomizer = 0
End Function

Is there a sloution. I dont want to load all records and then filter and randomize it in code!!!

 

[ManicCW]

anyone???
i'm in kind of a rush!

 

[ManicCW]

Can someone at least say that it's impossible!

 

[kulrom]

to be honest i've been never in position to need random records althrough the first that came to my mind was fetching all records into an array/arraylist and then pick up the random records from the array. maybe it si not perfect solution but anyway as there is not any SQL statement neither function for this i would go for this above or at least i would retrive the data populating say ListView and later select random items from it. Good luck

 

[ManicCW]

i dont like idea of fetching like 3000 records just to get 3!
i need it to display random images from image gallery on main page.

 

[kulrom]

true but that's only way i could recall at the moment (Btw, notice that fetching of 3000 records with reader class will last not more than couple seconds that is not much). However, let's wait for a while, maybe someone else will know more about this.

Regards

 

[TechGnome]

Shame this isn't MSSQL, I'd have an answer in a heartbeat. Access though.... *scratches head*.... let me look something up.

-tg

 

[ManicCW]

i know how to do it with mssql but this god damn access (i hate its queries!).

 

[kulrom]

how much you pay as much you got

 

[TechGnome]

i know how to do it with mssql but this god damn access (i hate its queries!).

Sorry man, I tried.... I was hoping that Access had something similar to SQL's CreateGUI() function... the closest thing I could find was an AutoNumber field type that can be set to ReplicationGUID type. Doesn't do much good in this case.

Sorry.

-tg