Loop Until Number Doesn't Exist in Database

[DebbieC]

I'm trying to do a loop but it's different than others I've done. I need to loop through a recordset of Case ID's that are sequential starting with #1 but some of them have been deleted so I need it to get to the next available number.

In other words I might have these numbers in the database:

CaseID
1
2
3
4
5
8
9
10
.....

I need it to return the #6 since it is the next available number to be used.

I hope this makes sense.

 

[Hameedullah]

do until recordset.eof
  response.write(recordset("CaseID") & "<br />")
  this_caseid = cint(recordset("CaseID"))
  recordset.movenext
  if this_caseid > cint(recordset("CaseID")) + 1 then
    for i = this_caseid+1 to cint(recordset("CaseID")) - 1
      response.write(i & "<br />")
    next
  if 
loop

Note: You can choose to have a different inmplementation but this is the idea....
Hint: Make sure your sql query should return the data in order of CaseID

 

[DebbieC]

Thank you so much. I will give it a try.

 

[DebbieC]

I got the message "error '80020009' Exception occurred" on the line that says:

if this_caseid > cint(recordset("CaseID")) + 1 then

 

[Sheco]

Since that code checks the field value both before and after the call to .MoveNext there should be another check in there for .EOF.

So after the line recordset.movenext you want something like:
IF recordset.EOF THEN Exit Do

 

[DebbieC]

I don't get an error message this time but it doesn't return a value either.

This is the code I have:

<%			
Do Until objRs.EOF
  Response.Write(objRs("CaseID") & "<br />")
  sCaseID = cint(objRs("CaseID"))
  objRs.movenext
  If objRs.EOF Then Exit Do
  If sCaseID > cint(objRs("CaseID")) + 1 Then
     For i = sCaseID + 1 to cint(objRs("CaseID")) - 1
	Response.Write(i & "<br />")
     Next
  Else
  End If            
Loop
%>

 

[gpzcrasher]

Try testing for

cint(objRs("CaseID")) > (sCaseID + 1)

If true then (sCaseID + 1) is your next available number and then exit do.



--------------------------------
Rome did not create a great empire by having meetings, they did it by killing all those who opposed them.

 

[DebbieC]

I tried all of the suggestions and nothing worked. I tried going through and simplifying it and I came up with the following code that I think should work (and it does when I do a response.write to see the values the first loop through) but it still doesn't return a value.

sCaseID = cint(objRs("CaseID"))	
Do Until objRs.EOF
	sCaseID = cint(sCaseID) + 1	
	objRs.movenext										
	If cint(objRs("CaseID")) > sCaseID Then 
	        Response.Write cint(sCaseID)  
		Exit Do					
	Else
	End If
Loop

Can someone help me?

 

[Hameedullah]

Try the following. This way, it will also show number 1 even if it is deleted.

<%
sCaseID = 1       
Do Until objRs.EOF
  If sCaseID < cint(objRs("CaseID")) Then
     For i = sCaseID to cint(objRs("CaseID"))
         Response.Write(i & "<br />")
     Next
  End If 
  Response.Write(objRs("CaseID") & "<br />")
  sCaseID = cint(objRs("CaseID")) + 1
  objRs.movenext          
Loop
%>

If you don't want to start from 1 if it is deleted then instead of

sCaseID = 1

set sCaseID it to a higher value than the max in the table