Problem getting random records

shauns1 · Apr 18, 2006

Hi, I am trying to fill a DataGrid with random records from my database. It thought I had found a way to randomly select records using SQL. So I built a stored procedure in Access and sure enough, it returns a random set of records and works exactly how I need it to. But...

Whe I tried to call this from my ASP.Net page, it kept returning the same set of records. So I copied and pasted the SQL into the actual page(code below) but it made no difference. Could anyone tell me why this wouldn't work?

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>

<script runat="server">
Public objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Sub GetTeam(s as Object, E as EventArgs)

Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY rnd(isnull(PlayerID)*0+1);", objConn)

Try
objConn.Open()
dgDef.DataSource = objCmd.ExecuteReader
dgDef.DataBind()
objConn.Close()
Catch ex as OleDbException
throw ex
End try

End Sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">

</style>
</head>
<body>
<form runat="server">
<p>
<asp:Button ID="btn33" runat="server" OnClick="GetTeam" Text="Get Defenders" />
</p>
<table width="100%" border="0" cellspacing="10" cellpadding="0">
<tr>
<td></td>
Defenders
<asp

ataGrid runat="server" ID="dgDef" EnableViewState="false" />
</tr>
</table>
<p> </p>
</form>
</body>
</html>

I have also tried:

SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY newid()

But this doesn't work in Access or from my ASP.Net page and returns the error:

Undefined function 'newid' in expression

Any help would be greatly appreciated. I thought I had the first method working a few days ago. Are there any other factors that I could be overlooking? Like I said, the first option works fine when called within Access.

Many thanks

Shaun

woja · Apr 19, 2006

From MS Access documentation for [tt]Rnd()[/tt]:

For any given initial seed, the same number sequence is generated because each successive call to the [tt]Rnd[/tt] function uses the previous number as a seed for the next number in the sequence.

You need to use the [tt]Randomize[/tt] statement to get different numbers each time.
You'll find that withing Access you get the same sequence everytime you open the DB but the list will be different each time you call the procedure while the DB is open. From the ASP code, you'll be opening a connection to the DB eacj time, which initialises the pseudo-random number generator to the same starting value, which produces the same sequence of "random" numbers.
Hope this is clear.

[tt]_______________________________________
Roger [pc2]

The Eileens are out there[/tt]

PHV · Apr 19, 2006

You may try something like this:
Public objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
[!]Public lngCounter As Long[/!]
Sub GetTeam(s as Object, E as EventArgs)
[!]lngCounter = 1 + lngCounter[/!]
Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY rnd(isnull(PlayerID)*0+[!]" & lngCounter & "[/!]);", objConn)
Try
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

shauns1 · Apr 19, 2006

PHV, this didn't seem to make any difference. It still returned the same set of records.

Wouldn't the above code always result in IngCounter being '1'?

Woja - Your post does make sense but I'm struggling to implement your solution.

I have tried to do a combination of both solutions...

Public objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
Public r as Random()
Sub GetTeam(s as Object, E as EventArgs)
Dim intRan
intRan = r.Next(3)

Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY rnd(isnull(PlayerID)*0+" & intRan & ");", objConn)

Try
...

But I get the error: 'Next' is not a member of 'System.Array'

My thinking behind the above was that every time the SQL is called, it would provoke a new starting point for the RND() function???

I feel I've gone off-track.

Any further help on implementing the Random Class in .Net so as to achieve Woja's solution above would be greatly appreciated.

Many thanks

shauns1 · Apr 20, 2006

OK, error above was due to incorrectly declaring Random(). I code the code to work but it still returns the same set of data. How do I change the seed that Access uses to create the Random from with my .Net page?!

Many thanks

Shaun

shauns1 · Apr 20, 2006

I think I've worked it out using Random()

Dim r as New Random()
Dim intRan
intRan = r.Next(1000)

Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID, Rnd(" & -1 * (intRan) & "*PlayerID) FROM tblPlayers WHERE PositionID='defender' ORDER BY Rnd(" & -1 * (intRan) & "*PlayerID)", objConn)

Previously, we were ordering by the Random clause but were not including the random clause in the Select.

This seems to have done the trick?!

Im happy. Vary happy. That a 6.30am resolution and a half!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem getting random records

shauns1

Programmer

woja

Programmer

PHV

MIS

shauns1

Programmer

shauns1

Programmer

shauns1

Programmer

Similar threads

Part and Inventory Search

Sponsor