How to give unique number in each Receipt of payme

Question

I have a database, 3 or more tables and a one of them with 3 or more columns.

In this table i have ID column, Name Column, Date Column etc.

I like to select one record for printing and give to the person one or more Receipt of payment (bill).

But every time i like the number of Receipt to be unique. For all persons in my table and all payment.

The start number Format will be 00000000 or 00.000.000 or A 000000 . Next number +1 etc.

Ask for further explanation.

See Form example:

http://imageshack.us/photo/my-images/89/prinreceipt.png/

Answer 1

If you are using MS SQL-Server, i would use a Stored Procedure in database to insert new records and a CLR(scalar-valued function)to get the next bill-number. On this way you could ensure that the number is unique.

The SP could look like:

CREATE PROCEDURE [dbo].[InsertBill]
    @idBill int OUTPUT,
    @CreationDate datetime OUTPUT,
    @Name varchar(50) OUTPUT,
    @BillNumber char(10) OUTPUT
 with execute as Owner
AS
INSERT INTO tBill(CreationDate, Name, BillNumber)
VALUES (GetDate(), @Name, dbo.GetNextBillNumber())
;SELECT  @idBill=idBill,@CreationDate=CreationDate, @Name=Name, @BillNumber=BillNumber
FROM tBill WHERE (@idBill = SCOPE_IDENTITY())

The next number will be MAX(BillNumber)+1, the format is 10 chars(A+9 digits), the CLR to generate the next bill-number:

CREATE FUNCTION [dbo].[GetNextBillNumber]()
RETURNS CHAR(10)
AS
BEGIN
    DECLARE @BillNumber CHAR(10);
    DECLARE @nextBillNumber int;
    SET @nextBillNumber = CONVERT(int,SUBSTRING((SELECT MAX(BillNumber) FROM tBill),2,9)) + 1;
    SET @BillNumber = 'A' + RIGHT('000000000'+ CONVERT(VARCHAR,@nextBillNumber),9)
    return @BillNumber 
END

Note: not 100% tested but you should understand what i mean.

Edit: You also should add a unique constraint to guarantee the uniqueness. Then it's techniqually impossible to insert two records with the same Bill-Number. In this example i've presumed that the table's primary-key is not the Bill-Number itself but an int-column which Is Identity(Auto-Increment, here with SSMS).

Here are informations on how to call a stored-procedure from ADO.NET.