looping if statement

[penguinspeaks]

I am trying to loop through records to see if a value exists. I can make it happen once, but how do I check while looping through the entire table?

con2 = (Int((max-min+1)*Rnd+min))

	set info5 = conn.execute("select confirm_ from orders where confirm_ = "&con2&"")
	if not info5.eof then
		
		max=9999999999
		min=1
		Randomize
		con2 = (Int((max-min+1)*Rnd+min))
		else
		con2 = con2
		end if

Thanks

 

[guitarzan]

Sorry, I don't follow your question, and your code seems incomplete. And you have no loop...

If you want a loop, use a Do While loop

http://ss64.com/vb/do.html

 

[penguinspeaks]

I didn't catch the loop and movenext while copying, my bad. This script checks the DB once for a duplicate. What I want it to do is if there is a duplicate, create another random number, and then check that one against the records. I need this to loop until there is not a duplicate.

Hope that makes sense.

 

[guitarzan]

>I didn't catch the loop and movenext while copying, my bad.
OK. So post all of the loop this time, including the missing part, and we can see what needs to be corrected

 

[guitarzan]

Well, something like this is what you are after

max=9999999999
min=1
Randomize

Dim bFound
bFound = False
Do While Not bFound
   con2 = (Int((max-min+1)*Rnd+min))
   set info5 = conn.execute("select confirm_ from orders where confirm_ = "&con2&"")
   if info5.eof then
      bFound = True
   end If
Loop

 

[penguinspeaks]

Hello and thanks for your reply.

This continues to give me the exact number in the DB field. Here is what I have, as you have writen it:
[code}
Dim max,min
max=9999999999
min=1
Randomize

Dim bFound
bFound = False
Do While Not bFound
con2 = (Int((max-min+1)*Rnd+min))
set info5 = conn.execute("select confirm_ from orders where confirm_ = "&con2&"")
if info5.eof then
bFound = True
end If
Loop
[/code]

I do not see why this is not working properly.

 

[guitarzan]

This continues to give me the exact number in the DB field
Sorry, I don't understand this sentence. What exact number is it giving you?

When the loop is done, con2 will be equal a value that is not already in the database, which is what you asked for. If it is NOT doing this, maybe you have "On Error Resume Next" which is suppressing error messages.

 

[penguinspeaks]

It gives the value of 2147483647 each time it runs.

 

[guitarzan]

That is the expected behavior if you are not executing the "Randomize" command. Are you sure it's there in your code?

 

[penguinspeaks]

I posted the exact code.
The problem was the field setting in the DB.
It was set to INT and I do not believe that it can store a 9 digit number with that setting?

 

[guitarzan]

Ahh, yes. 2147483647 is the maximum for that type. Use a different data type or use a smaller "max

 

[penguinspeaks]

Thanks for the help. Now working great!