用数据库生成不重复的流水号-数据库

'*************************************************
'函数名：getMaxOrder
'作  用：得到最大序列号
'参  数：fieldName   ----在序列号表中的字段名
'        tableName   ----序列号所在表名
'        fieldName   ----在表中的字段名
'返回值：字段的最大值
'调用函数：idAdd：作用见上
'*************************************************
function getMaxOrder(fieldName,tableName,tableFileName)
 dim orderNO,orderRS,testRS
 set testRS=Server.CreateObject("ADODB.recordset")
 set orderRS=Server.CreateObject("ADODB.recordset")
 firstNO=year(date)&right(("0"&month(date)),2)
 orderSQL="select * from fieldMaxValue where fieldName='"&fieldName&"'"
 orderRS.open orderSQL,conn,3,2
 if not orderRS.eof then
  orderRS("fieldMaxValue")=orderRS("fieldMaxValue")
  orderNO=orderRS("fieldMaxValue")
  if left(orderNO,6)=firstNO then
   orderNO=idAdd(orderNO)
  else
   orderNO=firstNO&"00001"
  end if
 else
  orderRS.addnew
  orderRS("fieldName")=fieldName
  orderNO=firstNO&"00001"
 end if
 testRS.open "select max("&tableFileName&") from "&tableName&" where "&tableFileName&" like '"&firstNO&"%'",conn,1,2
 if (not testRS.eof) and testRS(0).value>orderNO then
  orderNO=idAdd(testRS(0).value)
 end if
 testRS.close
 set testRS=nothing
 orderRS("fieldMaxValue")=orderNO
 orderRS.update
 orderRS.close
 set orderRS=nothing
 getMaxOrder=orderNO
end function

'*************************************************
'函数名：idAdd
'作  用：用来增加一：比如idAdd("5")="6",idAdd("L99")="M00"，idAdd("!")="!1"
'                如果是数字就到9后进位,如果是小写字母到期z后进位
'                如果是大写字母到Z后进位，其它在后面加一个1
'参  数：id   ----需来增加的数
'返回值：增加后的数
'调用函数：addOne 一个数增加一 AddOne("5")="6",Add(9)="0",AddOne("a")="b",
'                              AddOne("z")="a",AddOne("A")="B",AddOne("Z")="A"
'*************************************************
Function AddOne(first)
Dim tempfirst
AddOne = first
intfirst = Asc(first)
If (intfirst >= 48 And intfirst < 57) Or (intfirst >= 65 And intfirst < 90) Or (intfirst >= 97 And intfirst < 122) Then
    AddOne = Chr(intfirst + 1)
    Exit Function
End If
If (intfirst = 57) Then
    AddOne = "0"
    Exit Function
End If
If (intfirst = 90) Then
    AddOne = "A"
    Exit Function
End If
If (intfirst = 122) Then
    AddOne = "a"
    Exit Function
End If
End Function

Function idAdd(id)
Dim fornt, back, strFind, strBackFind, idLen, tempid
if id="" or isNull(id) then
 iddAdd=1
 exit Function
end if
tempid = id
idLen = Len(id)
For i = 1 To idLen
    fornt = Left(id, idLen - i)
    back = Right(id, i - 1)
    strFind = Mid(id, idLen + 1 - i, 1)
    strBackFind = AddOne(strFind)
    id = fornt & strBackFind & back
    If strFind < strBackFind Then
        Exit For
    End If
    If strFind > strBackFind Then
        If i = idLen Then
            id = id & "1"
        Else
            If Mid(id, idLen - i, 1) = AddOne(Mid(id, idLen - i, 1)) Then
                id = fornt & strBackFind & back & "1"
                Exit For
            End If
        End If
    End If
Next
If id = tempid Then
    id = id & "1"
End If
idAdd = id
End Function

'调用示例
serviceNO=getMaxOrder("serviceNO","service","serviceNO")

'其中保存所有字段流水号最大值的表的结构为:
表名:fieldMaxValue
字段
id    fieldName     fieldMaxValue