自动生成发票代码的ID [英] autogenerating id for invoice code

问题描述

我在vb.net中使用Windows应用程序窗体.我的项目是使用3tier arcitecture.开发的.在我的项目中，我的销售发票格式是我希望发票代码是自动递增的.我想增加代码值，但其数据类型为varchar.该ID包含一些前缀作为字母，例如inv0000001.我正在使用sql server.数据类型varchar(10).pls告诉我如何自动生成ID.

i am using windows application form in vb.netmy project is developed using 3tier arcitecture.in my project in sales invoice form i want the invoice code to be auto increment. I want to increment code value but its datatype is varchar.The id contain some prefix as alphabet e.g inv0000001.i am using sql server.the datatype varchar(10).pls some one hele me how to auto generate id.

推荐答案

由于这是在VB.NET部分中，所以我将使用代码和序列表为您提供答案(您可以在数据库中使用触发器和/或计算列并不需要序列表.

创建一个表(或如果有设置表)，在此表中您将阅读并更新下一个发票顺序(1,2,3 ... 9999999)

保存新发票时，获取序列并递增序列表(如果在保存之前获取序列，则如果有多个用户创建发票，则会遇到并发问题).您可能要在保存序列唯一性之前进行测试，如果不是，请获取另一个序列(直到您具有唯一序列).

Since this is in the VB.NET section, I''ll give you my answer using code and a sequence table (you could do this within the database with triggers and/or computed columns and remove the need for a sequence table).

Create a table (or if you have a setting table) where you will read and update your next invoice sequence (1,2,3...9999999)

When you save a new invoice, fetch the sequence and increment your sequence table (if you fetch the sequence before you save you can run into concurrency issues if you have multiple users creating invoices). You may want to test before you save that your sequence is unique, if it is not, fetch another (until you have a unique sequence).

'Fetch the sequence from your table
value = GetSequence()

'Test if unique invoice
While Not IsUniqueInvoice(value)
    value = GetSequence()
End While

GetSequence()将返回序列表中的下一个发票编号:
用户A:保存发票...选择顺序= 1，更新顺序表= 2
用户B:保存发票...选择顺序= 2，更新顺序表= 3

假设用户C和用户D同时保存，并且两个用户都被分配了相同的顺序:
用户C:保存发票...选择顺序= 3，更新顺序表= 4
用户D:保存发票...选择顺序= 3，更新顺序表= 4

用户C的条目将保存(因为3是唯一的)；但是，用户D将失败(将创建重复的发票编号).这就是IsUniqueInvoice 防止重复的地方.

因此，您希望用户D获取另一个序列(循环直到您有未使用的序列为止):
用户D:选择顺序= 4，更新顺序表= 5

GetSequence()将获取并更新序列表.您可以创建一个存储过程，以在一次调用中选择并更新序列.

GetSequence() would return the next invoice number from your sequence table:
User A: save invoice ... select sequence = 1, update sequence table = 2
User B: save invoice ... select sequence = 2, update sequence table = 3

Let say that User C and User D save at the same time where both users are assigned the same sequence:
User C: save invoice ... select sequence = 3, update sequence table = 4
User D: save invoice ... select sequence = 3, update sequence table = 4

User C''s entry would save (since 3 is unique); however User D would fail (would create a duplicate invoice number). That is where the IsUniqueInvoice would prevent the duplication.

So you would want User D to fetch another sequence (loop until you have an unused sequence):
User D: select sequence = 4, update sequence table = 5

GetSequence() would fetch and update the sequence table. You could create a stored procedure that selects and updates the sequence in one call.

请参阅此处的代码项目文章:灵活编号T-SQL中的序列]
其中包含针对此类问题的解决方案.
由于在大型应用程序上有许多客户端(或Web服务器)，因此我使用数据库来生成下一个序列，仅需对数据库进行一次调用.

迈克

Please see Code Project article here:
[Flexible numbering sequences in T-SQL]
which contains a solution for such a problem.
Since on large-use applications there are many clients (or web servers), I use the database to generate the next sequence, with only 1 single call to the database.

Mike

这篇关于自动生成发票代码的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！