AMD_GAMER (am********@gmail.com) writes:
I currently have a website which is using ASP.NET 2.0, C#, and SQL
Server 2005. The website will be used to enter grants for a
university. When a new grant is entered, I need to generate a primary
key. The primary key will need to follow the format: Two digit for
fiscal year, then number of the grant for that year. Example:
Year 08 and 14th grant of the year would be: 0814
How can I implement this. Right now, I have a "New Grant.aspx" page
with a Submit button. I am guessing the date is going to be formatted
in C#. How can I check what the last primary key in the database is?
Also, it seems to me that SQL Server insists that the primary key be
32 bits long, however my primary key will only be 4. How can I
override this? Thanks.
SQL Server accept any length of the PK up to 900 bytes.
0814 appears a tad short to me. What if there are more than 99 grants
in one year?
It would be better to make the PK a two-column key:
Year char(4) NOT NULL,
grantno int NOT NULL,
You find the new grantno with:
BEGIN TRANSACTION
SELECT @nextgrantno = coalesce(MAX(granttno), 0) + 1
FROM tbl WITH (UPDLOCK)
WHERE Year = @year
INSERT ...
For display you add a computed column:
displaykey as substring(Year, 3, 2) +
right(ltrim(str(grantno + 100))), 2) PERSISTED UNIQUE
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx