Need help to find next available interger 1

[MrRocky]

Can anyone give me an idea on why this is not working? I have a "Record ID" textbox in a add form that I want it to find the next available number that is not used for the user. I can't seem to get this to work because it keeps showing "001" as the next number when there is record with that number. Here the code I am using:


<%@ Language=VBScript %>
<%
'Create DSN Less connection to Access Database
'Create DBConn Object

Set DBConn = Server.CreateObject(&quot;adodb.connection&quot
DBopenString = &quot;DRIVER={Microsoft Access Driver (*.mdb)}; &quot;
DBopenString = DBopenString & &quot;DBQ=&quot; & Server.Mappath(&quot;ErrorProofing.mdb&quot

DBConn.Open DBopenString

Action = Request.QueryString(&quot;action&quot

select case Action
case &quot;add&quot;
DeptNum = Request.QueryString(&quot;DeptNum&quot
DeptName = Request.QueryString(&quot;DeptName&quot

SQLstmt = &quot;SELECT SequenceNum FROM tblSequencer WHERE Dept = &quot; & DeptNum & &quot; AND YearVal = &quot; & Year(date) & &quot;;&quot;
Set RS = DBconn.Execute(SQLstmt)

if not RS.eof then
SeqVal = RS(&quot;SequenceNum&quot + 1
else
SeqVal = 1
end if

if SeqVal < 10 then
seq = &quot;00&quot; & SeqVal
else
if SeqVal < 100 then
seq = &quot;0&quot; & SeqVal
else
seq = SeqVal
end if
end if

EntryDate = date

EPID = right(DeptNum, 2) & &quot;-&quot; & right(year(date),2) & &quot;-&quot; & seq

 

[BKQc]

Try to do a RS.movelast() before adding your 1. But a better way would be to call your recordset with

SQLstmt = "SELECT max(SequenceNum) FROM tblSequencer WHERE Dept = " & DeptNum & " AND YearVal = " & Year(date) & ";"

 

[Targol]

Does the orror comes from your DB or from your page ? I mean, if you type this :

    if not RS.eof then
        SeqVal = RS("SequenceNum") + 1
    else
        SeqVal = 1
    end if
    response.write("SeqVal = [" & SeqVal & "]")
    response.end

is your SeqVal value good ? Water is not bad as soon as it stays out human body ;-)

 

[MrRocky]

Thanks for the help I can't seem to get either one to work. Here what I now have tried. And I tried to debug to see if a nummber is coming in. I do get 1 for the SeqVal.
'SQLstmt = &quot;SELECT max(SequenceNum) FROM tblSequencer WHERE Dept = &quot; & DeptNum & &quot; AND YearValue = &quot; & Year(date) & &quot;;&quot;
'Set RS = conn.Execute(SQLstmt)

lPrevNumber = 0
SQLstmt = &quot;Select max(SequenceNum) from tblSequencer where Dept = &quot; & DeptNum & &quot; AND YearValue = &quot; & Year(date) & &quot;;&quot;
Set rsNum = conn.Execute(SQLstmt)

If rsNum.RecordCount > 0 Then
rsNum.MoveLast
rsNum.MoveFirst
Do While Not rsNum.EOF
If rsNum - 1 > lPrevNumber Then
Exit Do
rsNum.MoveLast
rsNum.MoveNext
Else
lPrevNumber = rsNum
rsNum.MoveNext
End If
Loop
End If
rsNum = lPrevNumber + 1



if rsNum < 10 then
seq = &quot;00&quot; & rsNum
else
if rsNum < 100 then
seq = &quot;0&quot; & rsNum
else
seq = rsNum
end if
end if


'if not RS.eof then
' RS.MoveLast
' RS.MoveNext
' SeqVal = RS(&quot;SequenceNum&quot + 1
'else
' SeqVal = 1
'end if

'if SeqVal < 10 then
' seq = &quot;00&quot; & SeqVal
'else
' if SeqVal < 100 then
' seq = &quot;0&quot; & SeqVal
' else
' seq = SeqVal
' end if
'end if
' response.write(&quot;SeqVal = [&quot; & rsNum & &quot;]&quot
' response.end

EPID = right(DeptNum, 2) & &quot;-&quot; & right(year(Date),2) & &quot;-&quot; & rsNum

 

[Tarwn]

I don't see why you started to use the recordcount method, it is not reliable, I would stop using it. Try this:

<%
'SQLstmt = &quot;SELECT max(SequenceNum) FROM tblSequencer WHERE Dept = &quot; & DeptNum & &quot; AND YearValue = &quot; & Year(date) & &quot;;&quot;
    'Set RS = conn.Execute(SQLstmt)

lPrevNumber = 0
SQLstmt = &quot;Select max(SequenceNum) as maxSN from tblSequencer where Dept = &quot; & DeptNum & &quot; AND YearValue = &quot; & Year(date) & &quot;;&quot;
Set rsNum = conn.Execute(SQLstmt)
    
'No need to move last as a select max stmt will only return one record from a single table, single attribute query
If rsNum.EOF Then
	seqNum = 1
Else
	seqNum = cInt(rsNum(&quot;maxSN&quot;)) + 1
End If        
    

if rsNum < 10 then
	seq = &quot;00&quot; & seqNum
elseif rsNum < 100 then
	seq = &quot;0&quot; & seqNum
else
	seq = seqNum
end if
 
	Response.Write &quot;The New sequence number is: &quot;&seqNum

EPID = right(DeptNum, 2) & &quot;-&quot; & right(year(Date),2) & &quot;-&quot; & seqNum
%>

One addition, you really should use an Option Explicit at the top of your ASP c ode and declare all of your variables, in the previous post you used the variable rsNum as both a recordset and a string variable without (probably) any complaint from the computer. This is not only a bad habit for the prettiness of your code, it can also cause some pretty bad problems if you accidentally mis-spell a variable because the machine has been told that every variable must be explicitly declared.

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri