这是我的代码,每次生成的代码是WMR+YYMMDD+四位流水号 procedure Twmrnofrm.ToolButton1Click(Sender: TObject); var maxno,currno,maxseq,wmrno:string; begin wmrnewfrm:=Twmrnewfrm.Create(self); wmrnewfrm.Show; currno:=FormatDatetime('YYMMDD',now()); with adoquery2 do begin close; sql.Clear; sql.Add('Select max(wmr_no) as maxno from TBLWOMATERIALREQ'); open; end; maxno:=copy(adoquery2.FieldValues['maxno'],4,6); maxseq:=RightStr('0000000000'+floattostr(strtofloat(copy(adoquery2.FieldValues['maxno'],4,10))+1),10); if currno>maxno then begin wmrno:='WMR'+currno+'0001'; end else begin wmrno:='WMR'+maxseq; end; wmrnew.wmrnewfrm.Edit1.Text:=wmrno; end;
对query1 写sql语句: select max(主键的字段名) from 表名 最大值是 Adoquery1.Fields[0].Value 再加1就是下一单号
楼上的代码在多人同时操作时,都可能出现重号。要避免必须使用transaction+数据库锁
处理数据集的OnNewRecord事件,按自己的规则生成一个新流水号
function TForm1.hq_bh():string; var strnowtime:string; tmpString:string; strnowdate:string; i:integer; begin strnowdate:=datetostr(now()); for i:=0 to length(strnowdate) do begin if strnowdate[i]<>'-' then tmpString:=tmpString+strnowdate[i]; end; strnowdate:= trim(tmpString); tmpString:=''; strnowtime:= FormatDateTime('HH:MM:SS:ZZZ',now()); for i:=0 to length(strnowtime) do begin if strnowtime[i]<>':' then tmpString:=tmpString+strnowtime[i]; end; strnowtime:= Trim(tmpString); tmpString:=''; strnowdate:=strnowdate+ strnowtime; Sleep(50); result:= strnowdate; end; 这个会重复的概率应该很小很小吧? 也可以自己加个流水号
create table (id int identity(1,1))
给你个存储过程CREATE PROCEDURE proc_GetSerialNumber @TableName nvarchar(32), --------单号对应的表名称 @FieldName nvarchar(20), --------单号对应的字段名称 @Prefix nvarchar(10), --------单号前缀 @DocumentNumber nvarchar(16) output --------生成的单号,返回值。 AS DECLARE @GetNumber nvarchar(16), @MaxNumber nvarchar(6), @TempSQL nvarchar(2048) SET @TempSQL = N' SELECT @MaxNumber =RIGHT(MAX(' + @FieldName+') , 6) + 1 FROM ' + @TableName + N' WHERE SUBSTRING(RIGHT(RTRIM(' + @FieldName+'), 12), 1, 6) = CONVERT(varchar(6), getdate(),112)' + N' SET @GetNumber = ' --取年月 + N'CONVERT(varchar(12), getdate(),112) + ' --取年月 + N'CONVERT(varchar(6), getdate(),112) + ' + N' (SELECT CASE WHEN @MaxNumber IS NULL THEN '+''''+'100001'+'''' + N' ELSE @MaxNumber END) SET @DocumentNumber= @GetNumber' EXECUTE sp_executesql @TempSQL, N'@MaxNumber varchar(6) output, @GetNumber nvarchar(16) output, @DocumentNumber nvarchar(16) output', @MaxNumber=@MaxNumber output, @GetNumber=@GetNumber output, @DocumentNumber=@DocumentNumber output SET @DocumentNumber=@Prefix + @GetNumber SELECT @DocumentNumber GO
这是我的代码,每次生成的代码是WMR+YYMMDD+四位流水号
procedure Twmrnofrm.ToolButton1Click(Sender: TObject);
var maxno,currno,maxseq,wmrno:string;
begin
wmrnewfrm:=Twmrnewfrm.Create(self);
wmrnewfrm.Show;
currno:=FormatDatetime('YYMMDD',now());
with adoquery2 do
begin
close;
sql.Clear;
sql.Add('Select max(wmr_no) as maxno from TBLWOMATERIALREQ');
open;
end;
maxno:=copy(adoquery2.FieldValues['maxno'],4,6);
maxseq:=RightStr('0000000000'+floattostr(strtofloat(copy(adoquery2.FieldValues['maxno'],4,10))+1),10);
if currno>maxno then
begin
wmrno:='WMR'+currno+'0001';
end
else
begin
wmrno:='WMR'+maxseq;
end;
wmrnew.wmrnewfrm.Edit1.Text:=wmrno;
end;
最大值是
Adoquery1.Fields[0].Value
再加1就是下一单号
function TForm1.hq_bh():string;
var
strnowtime:string;
tmpString:string;
strnowdate:string;
i:integer;
begin
strnowdate:=datetostr(now());
for i:=0 to length(strnowdate) do
begin
if strnowdate[i]<>'-' then
tmpString:=tmpString+strnowdate[i];
end;
strnowdate:= trim(tmpString);
tmpString:='';
strnowtime:= FormatDateTime('HH:MM:SS:ZZZ',now());
for i:=0 to length(strnowtime) do
begin
if strnowtime[i]<>':' then
tmpString:=tmpString+strnowtime[i];
end;
strnowtime:= Trim(tmpString);
tmpString:='';
strnowdate:=strnowdate+ strnowtime;
Sleep(50);
result:= strnowdate;
end;
这个会重复的概率应该很小很小吧?
也可以自己加个流水号
@TableName nvarchar(32), --------单号对应的表名称
@FieldName nvarchar(20), --------单号对应的字段名称
@Prefix nvarchar(10), --------单号前缀
@DocumentNumber nvarchar(16) output --------生成的单号,返回值。
AS
DECLARE
@GetNumber nvarchar(16),
@MaxNumber nvarchar(6),
@TempSQL nvarchar(2048)
SET @TempSQL = N' SELECT @MaxNumber =RIGHT(MAX('
+ @FieldName+') , 6) + 1 FROM '
+ @TableName
+ N' WHERE SUBSTRING(RIGHT(RTRIM('
+ @FieldName+'), 12), 1, 6) = CONVERT(varchar(6), getdate(),112)'
+ N' SET @GetNumber = '
--取年月 + N'CONVERT(varchar(12), getdate(),112) + '
--取年月
+ N'CONVERT(varchar(6), getdate(),112) + '
+ N' (SELECT CASE WHEN @MaxNumber IS NULL THEN '+''''+'100001'+''''
+ N' ELSE @MaxNumber END) SET @DocumentNumber= @GetNumber'
EXECUTE sp_executesql @TempSQL,
N'@MaxNumber varchar(6) output, @GetNumber nvarchar(16) output,
@DocumentNumber nvarchar(16) output',
@MaxNumber=@MaxNumber output, @GetNumber=@GetNumber output,
@DocumentNumber=@DocumentNumber output
SET @DocumentNumber=@Prefix + @GetNumber
SELECT @DocumentNumber
GO