Create an IDENTITY column in the table, use that, a number that will be autoincremented safely, to ensure that all rows have a unique number.

first i would like to thank u

second if i use the IDENTITY column, How can I know the receipt number of the receipt that just has been added? Is there away to track the last IDENTITY added

 

 

Sure, several ways.  For instance, SELECT SCOPE_IDENTIY() will get you an identity value that's just been added within the current connection scope. 

Thank u Xandor

unfortunatly, I found out that i can not use the Identity because the company has many branches and the primary key to be composit key of serial number and branch number. for example

Ser_No        Br_No

1                  1

2                   1

3                    1

1                    2

2                     2

3                      2

......etc

so i should assign a unique number within every branches

any help pls

Here's what I would do assuming that you are inserting rows into your database via a DataSet
1) Create an autoincrement column in your table that corresponds to your SerialNumber
2) In the RowUpdated event of your DataAdapter add code to retrieve the highest SerialNumber from your table (SELECT SCOPE_IDENTITY)
3) Set the value of your AutoIncrement column to that value
4) Then perform your insert

Getting the value via the RowUpdated event will ensure that you will always get the highest serialnumber prior to performing an Insert

Good Luck

itismesa wrote:

unfortunatly, I found out that i can not use the Identity because the company has many branches and the primary key to be composit key of serial number and branch number. for example

You can (and should) still use an IDENTITY column, even given this requirement.  You can easily generate the row numbers on a per-branch basis at read time, based on the IDENTITY column.  For instance:

DECLARE @x TABLE
(
	BranchId INT,
	IdCol INT IDENTITY(1,1)
)

INSERT @x (BranchId) VALUES (1)
INSERT @x (BranchId) VALUES (1)
INSERT @x (BranchId) VALUES (2)
INSERT @x (BranchId) VALUES (1)
INSERT @x (BranchId) VALUES (3)
INSERT @x (BranchId) VALUES (2)
INSERT @x (BranchId) VALUES (3)

SELECT *
FROM @x

SELECT x1.BranchId, x1.IdCol, COUNT(*) BranchRowNum
FROM @x x1
JOIN @x x2 ON x2.BranchId = x1.BranchId
	AND x2.IdCol <= x1.IdCol
GROUP BY x1.BranchId, x1.IdCol
ORDER BY x1.BranchId, x1.IdCol