Filter and RecordCount 2

[vasah20]

Here's my problem. After applying the filter on a recordset, I cannot get a random record from that result set. Like my code outlines, the RecordCount changes.
I cannot specify search criteria in the SQL query, because I need to manipulate the recordset about 4 times. I figured that it would be a lot better to have one object holding about 100 records than 4 objects holding around 25 records each. This recordset is disconnected, so the performance hit isn't as bad as you would think it would be.

RS.RecordCount             'this gives a value of 5
RS.Delete                  
RS.RecordCount             'this gives a value of 4
RS.Filter = &quot;field1 <= 2&quot;  'limits the recordset to 2 records
RS.RecordCount             'this gives a value > 2
Randomize timer
t = int(rnd * RS.RecordCount) ' t can be > 2

RS.Move t                  'Error! Past EOF!!

BTW - I know that I can loop while not RS.EOF to determine the count, but there has to be a much less expensive solution...

thanks in advance for your help.
leo

 

[link9]

You say that the last RS.recordcount gives a value > 2 ... what exactly does it give? Is it the same every time?

If it is, then are you absolutely sure that the filter statement should make it two records?

What result do you come up with for the while not rs.eof method of counting? I know that's expensive, but I would try it just to see what I came up with... might point to the problem.

I use the .recordCount property on filtered recordsets all the time, and never have a problem, so I'm at a loss at what else it could be besides a recordset that was, actually, bigger than the 2 that you are going after (which would point to a data issue)...

 

[ToddWW]

I have code in my office that will allow you to set a range on the randomizer. For instance, you can set a range such as 1 to RS.RecordCount. That way the return won't be any greater than your Record Set size. If that would help, let me know and when I get into the office in about 30 min, I'll send it to you.

 

[link9]

Oh, I think I see it now --

You left off the () on the rnd function --

My docs show it as:

int(rnd() * RS.RecordCount)

Does that help?

And this is, in fact, the same statement that specifies upper and lower bound, only that lowerbound is 0 in this case, correct?

 

[vasah20]

The recordcount after the filter is always changing, but it's always greater than the actual count of records. I guess it must be a data issue, but I wouldn't know how to pinpoint that. I mean - the original recordset comes out perfectly, then I display the first record, then delete that one.

It's when I start trying to display random records that I get an error. This is a freelance project of mine at home, so unfortunately I can't do a lot of testing while I'm here at work...

Todd - can you post the code for limiting the value of the randomizer? But this brings in a question -- how truly 'random' will that result be?

Here is actually one thing that I left out -
I sort the recordset first, then I apply the filter. I think that might be my problem... I might need to remove the sort.
Has anyone encountered problems with using a combination sort/filter on a recordset?

thanks.
leo

 

[link9]

The code Todd is talking about is:

upperlimit = 10
lowerlimit = 0
t = Int((upperlimit - lowerlimit)*Rnd() + lowerlimit)

But it's the same thing that you have up there, only your lowerlimit is 0, which makes the formula reduce to what you already have. Using the randomizer statement up there just like you have will make the results truly random.

Here is an algorithm that I have to do pretty much what you are trying to do. Only difference is that it populates an array with five unique random numbers ranging between 1 and 10. Then, in the recordset, I have id's that are associated with the records that I want to retrieve, so rather than the .move method to actually move so many records, I use the .find record to just pluck out the one I want. (Maybe you can try that method??)

upperLimit = 10
lowerLimit = 1 
j = 1

for i = 1 to 5
	randArray(i) = 0
next

for i = 1 to 5
	good = false
	while not good
		randomize
		number = Int((upperLimit - lowerLimit) * Rnd() + lowerLimit)
		j = 1
		do while j <= 5
			if randArray(j) = number then
				good = false
				exit do
			else
				good = true
			end if
			j = j + 1
		loop
	wend
	randArray(i) = number
next

 

[ToddWW]

There shouldn't be an issue with sort / filter. However, whenever I am going to be sorting / filtering / moving back and forth through a disconnected recordset, I always make sure to issue the RS.CursorType = 3 command before populating the recordset and placing filters / sorts / etc... This is a static, disconnected recordset but is fully scrollable.

Here's the randomizer code that I use and as far as I know, it's random enough to use in gaming software.

dim fname
dim ufname

Randomize
ufname = Int((Rnd * 999) + 0)

fname = "c:\folder1\myexcelfile_" & ufname & ".xls"

I've used that code to make sure that duplicate file names cannot be uploaded into my server. The 999 is the upper_bound and the 0 is the lower_bound. I use the Int function to ensure that the return is an integer. Your code could look like this.

dim recno

Randomize
recno = Int((Rnd * RS.RecordCount) + 1)

RS.AbsolutePosition = recno

This should generate a random number between 1 and the size of the record set. Also, I've never seen the property "timer" used after the Randomize command as I have seen in your code above so I'm not sure how that differs from just the Randomize command by itself. One more thing. Using the Move method could still possibly give you an EOF error unless you move the pointer to the top of the record set before issuing the RS.Move. I would suggest using the AbsolutePosition method. This moves the pointer to a specific record in the recordset instead of moving the pointer forward or backward a specific number of records. This method moves the pointer to the record position in the disconnected recordset, not in the original table. It can also be used to retrieve the current position.

dim recno
recno = RS.AbsolutePosition
Response.Write recno        ' returns current record position

I'm not sure how AbsolutePosition works after issuing filters and sorts. Try it out, if you have problems, then just change the code to the following.

dim recno

Randomize
recno = Int((Rnd * RS.RecordCount) + 0)

RS.MoveFirst
RS.Move recno - 1

Note that I changed the lower_bound to 0 and issued a -1 in the Move statement. This way, the first and last records could be accessed with the Randomizer. For example, if your recordset had 10 records...

RS.Move 0   ' lower_bound of randomizer keeps pointer at 1
RS.Move 10 - 1    ' Moves forward 9 places to record 10.

Have fun !!

TWW

 

[vasah20]

I used timer in my randomize statement because from what I can tell, the random seed is taken from system time. Since I end up calling randomize multiple times, I use timer as the seed instead of the system time. After testing, I noticed that it gave a more random result than just regular Randomize.

Todd - I think that you solved my EOF problem, since I was using RS.Move without using .MoveFirst. (I'm not very experienced with the Move functions... I usually just call .GetRows and get on with it ) Anyhow, thanks guys from your help... I'll try to get this to work once I get on my home machine.

Link - that's a great idea for pulling out a random record... but since there are gaps in my primary keys, I won't be able to use that method. Too bad I'm not using SQL Server, but then again, if I was using SQL Server I don't think I would be having this problem.

Question with RS.find:
I've never used the method, could you possibly post the syntax?

thanks again...
leo

 

[ToddWW]

RS.Find uses SQL equivalent of the WHERE clause only you don't put the word "WHERE" in there and you don't make table references since your recordset is disconnected. Some examples.

RS.Find "textfield1 = '444A9287' AND numfield1 > 2"

RS.Find "textfield1 = '" & memvar1 & "' AND " _
  & "numfield1 > " & memvar2

Returns EOF if record is not found. Use ..

IF NOT RS.EOF THEN
    Statements if record was found
ELSE
    Statements if record was not found
END IF

To test for EOF and go from there.

Thanks. I only noticed after writing all of my original reply that you were using the Move method. Don't forget, if you stay with the Move method, that the top of the record (Record #1) is not BOF so if you want a truly random placement of the record pointer, make sure your randomizer can return a zero. Otherwise, record #1 will never be accessed. If you use the AbsolutePosition method, make sure the randomizer will not return 0 because there is no such record number.

Have Fun..

ToddWW

 

[link9]

RS.find "fieldName = value, fieldName = value" --etc...

There are a few optional arguments:

rs.find Criteria, [SkipRows], [SearchDirection], [Start]

Syntax for criteria is listed above

Skip Rows is a boolean stating whether to use the current row as a part of the search. default is 0

SearchDirection indicates which direction to search -- default is 1 (forward), and you can specify -1 (backwards)

Start -- you can pass in a bookmark as the starting position if you want.

good luck
paul