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!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting access to retrieve different random records on each request

Status
Not open for further replies.
shauns1

shauns1

Programmer
Oct 21, 2005
53
AU
<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" "<html xmlns="<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-weight: bold}
-->
</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:DataGrid runat="server" ID="dgDef" EnableViewState="false" />
</tr>
</table>
<p>&nbsp;</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

I believe that I need to force access to use a different seed each time it uses the rnd() function.

Could anyone help with doing this using ASP.Net. In ASP there was RANDOMIZE. I think the alternative in .Net is Random().

There is some history to this story here: (sorry if anyone classes this as a double post but this is more about using .Net to force a new seed for each query)

Any help would be greatly appreciated.

Many thanks

Shaun
 
Sort by date Sort by votes
You may try something like this:
Public objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
Public myLong As Long
Sub GetTeam(s as Object, E as EventArgs)
myLong = myLong + 1
Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY Rnd(Val(PlayerID)+" & myLong & ");", objConn)
Try
...
End Try
Set objCmd = Nothing

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

Doesn't myLong + 1 just = 1? Or am I mixing up Long with integer?

Thanks - just trying to understand whats going on.
 
Upvote 0 Downvote
Doesn't myLong + 1 just = 1
GetTeam is called only once ?

You may try this instead:
myLong = Second(Now)*100 + Minute(Now)

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

Both of these options still result in the same set of records being returned every time!

Unless my datagrid is telling me porkies as that's the only thing I've got displaying the records.

But as it stands, everytime I click that button, there is no change in the records. I have tested the following query in Access which once again, will always return the same set of records the first time you run it but subsequent runs will return different records.

SELECT TOP 4 PlayerID
FROM tblPlayers
WHERE PositionID='defender'
ORDER BY rnd(isnull(PlayerID)*0+1);

This is where I got to in a different thread (before I knew what the problem was)

Woja wrote:
"You need to use the Randomize 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."

So, I guess I'm asking how to acheive what Woja writes above.

Failing that, should I be using an alternative method to retrieve a random set of records?!

Many thanks for your help thus far.

Shaun
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
533
dhookom
dhookom
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
703
TheresAlwaysAWay
TheresAlwaysAWay
TrekBiker
  • Locked
  • Question
Records in one table not in another 1
Replies
3
Views
245
Andrzejek
Andrzejek
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
2K
lameid
lameid
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top