Private Sub prcTransactionsAutoGen(strTable As String)
'此存储过程产生一个某张表的最大ID号
Set prmOfPrc = New Parameter
Set comPrc = New Command
comPrc.ActiveConnection = con
Set prmOfPrc = comPrc.CreateParameter("TransactionID", adInteger, adParamOutput)
comPrc.Parameters.Append prmOfPrc
comPrc.Execute
If comPrc(0) < 10 Then
strTransactionID = "000000000" + CStr(comPrc(0))
ElseIf comPrc(0) < 100 Then
strTransactionID = "00000000" + CStr(comPrc(0))
ElseIf comPrc(0) < 1000 Then
strTransactionID = "0000000" + CStr(comPrc(0))
ElseIf comPrc(0) < 10000 Then
strTransactionID = "000000" + CStr(comPrc(0))
ElseIf comPrc(0) < 100000 Then
strTransactionID = "00000" + CStr(comPrc(0))
ElseIf comPrc(0) < 1000000 Then
strTransactionID = "0000" + CStr(comPrc(0))
ElseIf comPrc(0) < 10000000 Then
strTransactionID = "000" + CStr(comPrc(0))
ElseIf comPrc(0) < 100000000 Then
strTransactionID = "00" + CStr(comPrc(0))
ElseIf comPrc(0) < 1000000000 Then
strTransactionID = "0" + CStr(comPrc(0))
End If
Set comPrc = Nothing
Set prmOfPrc = Nothing
End Sub
在SQL SERVER有类似存储过程
CREATE procedure prcTransactionsOfAccountAutoGen @iIncrement int output
as
declare @cTransactionID char(10)
select @cTransactionID=isnull(max(cTransactionID),'0000000000') from TransactionsOfAccount
select @iIncrement=convert(int,substring(@cTransactionID,1,10))+1
GO