1: create function getDH returns char(10) As begin declare @dh1 char(10),@dh2 char(10) select @dh1 = max(dh) from tableName set @dh1 = IsNull(@dh1,'') set @dh2 = 'cc' +right(convert(varchar(6),getdate(),112),4) + '0001' if @dh1 > @dh2 begin set @dh2 = left(@dh1,6) + right('0000'+ cast(cast(right(@dh1,4)as int)+1 as varchar),4) end return(@dh2) end/******** Usage: select dbo.getdh() *******/ 2:在字段默认值中填入 dbo.getdh()
cc03100001 假设由cc+表中的已有的最大编号为例: --> select 'cc'+right('00000000'+max(编号),8) from table
把系统时间转换成字符串,然后和其它字符串连起来就可以了. 转换函数可参考MSDN.
'取得表的主键 Public Function GetID(ls_Table) As String On Error GoTo Errhandle Dim ls_ID As String, rs As New ADODB.Recordset
ls_ID = Format$(ls_CurDate, "YYYYMMDD") If rs.State = adStateOpen Then rs.Close rs.Open "select max(ID) from " & ls_Table & " where ID like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly If IsNull(rs.Fields(0).Value) Then ls_ID = ls_ID & "0001" Else ls_ID = rs.Fields(0).Value + 1 End If GetID = ls_ID rs.Close Set rs = Nothing
Exit Function Errhandle: If rs.State = adStateOpen Then rs.Close Set rs = Nothing ErrView Err.Description End Function
可以啊,你首先要从数据库里面现在最大的编号,然后把它格式化成四位的形式,再加上前面的那些资料就行了,我以前做的是用一个标保存最后一个保存的那个ID,每次添加的时候都读出来,获取编号,然后把这个保存进去。给你一段代码 Private Sub GetSN() Dim IntCyc As Integer Dim QueryString As String Dim MyDB As New ADODB.Connection Dim MyRS As New ADODB.Recordset QueryString = "Exec LoadSerialNum" MyDB.ConnectionString = ConnString MyDB.Open Set MyRS = MyDB.Execute(QueryString) With MyRS Do While Not .EOF Select Case Trim(!SN_Type) Case "Batch" BatchSN = Trim(!pre_ID) Case "Lot" If Trim(!Product_name) = Left(CProduct.Text, Len(CProduct.Text) - 3) Then LotSN = Trim(!pre_ID) End If Case "Bad" BadSN = Trim(!pre_ID) End Select
.MoveNext Loop End With If BatchSN = "" Then BatchSN = "0001" Else BatchSN = Mid(BatchSN, 11, 4) If BatchSN = "9999" Then BatchSN = "0001" Else BatchSN = BatchSN + 1 For IntCyc = 1 To 4 - Len(BatchSN) BatchSN = "0" & BatchSN Next End If End IfEnd Sub 你加上保存的代码就行了
我这样不行,你帮看看 在text1.text=getid(ccba) 'ccba是表名 Public Function GetID(ls_Table) As String On Error GoTo Errhandle Dim ls_ID As String, rs As New ADODB.Recordset
ls_ID = Format$(Data, "YYYYMMDD") If rs.State = adStateOpen Then rs.Close rs.Open "select max(报案登记编号) from " & ls_Table & " where 报案登记编号 like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly If IsNull(rs.Fields(0).Value) Then ls_ID = ls_ID & "0001" Else ls_ID = rs.Fields(0).Value + 1 End If GetID = ls_ID rs.Close Set rs = Nothing
Exit Function Errhandle: If rs.State = adStateOpen Then rs.Close Set rs = Nothing ErrView Err.Description End Function
select 'cc'+datepart(mm,getdate())+datepart(dd,getdate())+right('0000'+max(编号),4) from table
应该是: select 'cc' +cast(datepart(mm,getdate()) as char(2)) +cast(datepart(dd,getdate()) as char(2)) +right('0000'+cast(max(编号) as varchar(20)),4) from table测试如下: create table #t(bh varchar(10),a int) insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
我昨晚在搞同类code 1000条记录的表,字段序号分散跳跃,要实现增加记录时,序号自动生成并且优先使用表中被跳跃的数字 实现: 1、查询表中的序号,赋值给动态数组a() 2、for j=1 to 5000 for i=1 to a数组下标 if i=a(i) then hh=true next if hh=false then 目标序号=j next 要code mailto; [email protected]
将ls_ID = rs.Fields(0).Value + 1改为ls_id=ls_id & formcode(4,rs.fields(0).value+1) 其中formcode为一自定义函数 public sub formcode(byval bitNo as integer,byval numbervalue as long) as string dim i as long dim j as integer dim temp as long temp=numbervalue formcode="" for i =bitNO-1 to 0 step -1 j=temp\10^i formcode=formcode & str(j) temp=temp-j*10^i next i end sub
难道这个不行么?到底你试了没有?? select 'cc' +cast(datepart(mm,getdate()) as char(2)) +cast(datepart(dd,getdate()) as char(2)) +right('0000'+cast(max(编号) as varchar(20)),4) from table测试如下: create table #t(bh varchar(10),a int) insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
将下列程序拷贝到查询分析其中执行create table #t(bh varchar(10),a int) insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
将我的函数放到模块中,用的时候:Dim id as Stringid = GetID("表名")
http://expert.csdn.net/Expert/topic/2341/2341073.xml?temp=.7738001 老大:不行Dim id as String id = GetID("ccna") ‘ccna为表名 rs.Open "select max(登记号) from " & ls_Table & " where 登记号 like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly
这行就出错,登记号为自动号, 我已经将您的程序放到模块了,那里还有错
SQL中的.--自已做标识列的例子:--创建得到最大id的函数 create function f_getid() returns int as begin declare @id int select @id=max(id) from tb set @id=isnull(@id,0)+1 return(@id) end go--创建表 create table tb(id int default dbo.f_getid(),name varchar(10)) go--创建触发器,在删除表中的记录时,自动更新记录的id create trigger t_delete on tb AFTER delete as declare @id int,@mid int select @mid=min(id),@id=@mid-1 from deleted update tb set id=@id,@id=@id+1 where id>@mid go--插入记录测试 insert into tb(name) values('张三') insert into tb(name) values('张四') insert into tb(name) values('张五') insert into tb(name) values('张六') insert into tb(name) values('张七') insert into tb(name) values('张八') insert into tb(name) values('张九') insert into tb(name) values('张十')--显示插入的结果 select * from tb--删除部分记录 delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果 select * from tb--删除环境 drop table tb drop function f_getid
create function getDH
returns char(10)
As
begin
declare @dh1 char(10),@dh2 char(10)
select @dh1 = max(dh) from tableName
set @dh1 = IsNull(@dh1,'')
set @dh2 = 'cc' +right(convert(varchar(6),getdate(),112),4) + '0001'
if @dh1 > @dh2
begin
set @dh2 = left(@dh1,6) + right('0000'+ cast(cast(right(@dh1,4)as int)+1 as varchar),4)
end
return(@dh2)
end/********
Usage: select dbo.getdh()
*******/
2:在字段默认值中填入 dbo.getdh()
假设由cc+表中的已有的最大编号为例:
-->
select 'cc'+right('00000000'+max(编号),8) from table
转换函数可参考MSDN.
Public Function GetID(ls_Table) As String
On Error GoTo Errhandle
Dim ls_ID As String, rs As New ADODB.Recordset
ls_ID = Format$(ls_CurDate, "YYYYMMDD")
If rs.State = adStateOpen Then rs.Close
rs.Open "select max(ID) from " & ls_Table & " where ID like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly If IsNull(rs.Fields(0).Value) Then
ls_ID = ls_ID & "0001"
Else
ls_ID = rs.Fields(0).Value + 1
End If GetID = ls_ID
rs.Close
Set rs = Nothing
Exit Function
Errhandle:
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
ErrView Err.Description
End Function
Private Sub GetSN()
Dim IntCyc As Integer
Dim QueryString As String
Dim MyDB As New ADODB.Connection
Dim MyRS As New ADODB.Recordset
QueryString = "Exec LoadSerialNum"
MyDB.ConnectionString = ConnString
MyDB.Open
Set MyRS = MyDB.Execute(QueryString)
With MyRS
Do While Not .EOF
Select Case Trim(!SN_Type)
Case "Batch"
BatchSN = Trim(!pre_ID)
Case "Lot"
If Trim(!Product_name) = Left(CProduct.Text, Len(CProduct.Text) - 3) Then
LotSN = Trim(!pre_ID)
End If
Case "Bad"
BadSN = Trim(!pre_ID)
End Select
.MoveNext
Loop
End With
If BatchSN = "" Then
BatchSN = "0001"
Else
BatchSN = Mid(BatchSN, 11, 4)
If BatchSN = "9999" Then
BatchSN = "0001"
Else
BatchSN = BatchSN + 1
For IntCyc = 1 To 4 - Len(BatchSN)
BatchSN = "0" & BatchSN
Next
End If
End IfEnd Sub
你加上保存的代码就行了
在text1.text=getid(ccba) 'ccba是表名
Public Function GetID(ls_Table) As String
On Error GoTo Errhandle
Dim ls_ID As String, rs As New ADODB.Recordset
ls_ID = Format$(Data, "YYYYMMDD")
If rs.State = adStateOpen Then rs.Close
rs.Open "select max(报案登记编号) from " & ls_Table & " where 报案登记编号 like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly If IsNull(rs.Fields(0).Value) Then
ls_ID = ls_ID & "0001"
Else
ls_ID = rs.Fields(0).Value + 1
End If GetID = ls_ID
rs.Close
Set rs = Nothing
Exit Function
Errhandle:
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
ErrView Err.Description
End Function
select
'cc'
+cast(datepart(mm,getdate()) as char(2))
+cast(datepart(dd,getdate()) as char(2))
+right('0000'+cast(max(编号) as varchar(20)),4)
from table测试如下:
create table #t(bh varchar(10),a int)
insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
1000条记录的表,字段序号分散跳跃,要实现增加记录时,序号自动生成并且优先使用表中被跳跃的数字 实现:
1、查询表中的序号,赋值给动态数组a()
2、for j=1 to 5000
for i=1 to a数组下标
if i=a(i) then hh=true
next
if hh=false then 目标序号=j
next
要code mailto; [email protected]
其中formcode为一自定义函数
public sub formcode(byval bitNo as integer,byval numbervalue as long) as string
dim i as long
dim j as integer
dim temp as long
temp=numbervalue
formcode=""
for i =bitNO-1 to 0 step -1
j=temp\10^i
formcode=formcode & str(j)
temp=temp-j*10^i
next i
end sub
select
'cc'
+cast(datepart(mm,getdate()) as char(2))
+cast(datepart(dd,getdate()) as char(2))
+right('0000'+cast(max(编号) as varchar(20)),4)
from table测试如下:
create table #t(bh varchar(10),a int)
insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
insert into #t select '0001',1select 'cc'+cast(datepart(mm,getdate()) as char(2))+cast(datepart(dd,getdate()) as char(2))+right('0000'+cast(max(cast(bh as int)) as varchar(20)),4) from #t
老大:不行Dim id as String id = GetID("ccna") ‘ccna为表名
rs.Open "select max(登记号) from " & ls_Table & " where 登记号 like '" & ls_ID & "%'", cn, adOpenForwardOnly, adLockReadOnly
这行就出错,登记号为自动号,
我已经将您的程序放到模块了,那里还有错
create function f_getid()
returns int
as
begin
declare @id int
select @id=max(id) from tb
set @id=isnull(@id,0)+1
return(@id)
end
go--创建表
create table tb(id int default dbo.f_getid(),name varchar(10))
go--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tb--删除环境
drop table tb
drop function f_getid