Null + Running Order Problem

Question

Post reply

Null + Running Order Problem

Indigo Violet

Newbie

Points: 2
More actions
August 30, 2005 at 1:54 am

#94715

I am using VB.NET to store id(primary key) in a Rxxyyyy in SQL Server 2000. xx starts from 01 in running order and yyyy stands for the year.So each year's first record has an xx which begins with 01.
For the id, i use char and nchar, 7 but when i try to store them the system will the id don't allow nulls. Rxxyyyy is seven chars right so where is the null?
Another thing is I don't know how to get the xx to run in running order.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Indigo - a primary key does not allow null values which's why you cannot insert nulls in your column.

I am going to make a few assumptions on your ID column based on your post:

1) Your first character is always going to be 'R'

2) For each year, you are not going to exceed 99 in the 'xx' part

If this is correct, you can write a stored procedure to pass the 'year' - figure out the next ID to insert - then insert into your table...

the t-sql to get the newID will be something like this:

CREATE PROCEDURE InsertNewID
@Year Char(4)
AS

SELECT NewID = 
CASE 
WHEN LEN(MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1) = 1 THEN 'a0' + CONVERT(CHAR(1), (MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1)) + @Year
WHEN LEN(MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1) = 2 THEN 'a' + CONVERT(CHAR(2), (MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1)) + @Year
WHEN LEN(MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1) = 3 THEN 'No More'
END
FROM myTable
WHERE RIGHT(colName, 4) = @Year
....and then you can insert this NewID into your table...

Hope this is what you were looking for!

**ASCII stupid question, get a stupid ANSI !!!**

Indigo Violet Newbie Points: 2 More actions · Answer 2

Thanks for replying. But I don't understand where and how to use the t-sql, can you explain. Plus which part makes the xx increase by one each time a record is added. Your assumptions are correct. Maybe you can refer me to a useful website.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Indigo - you won't find a better source of information than BOL (books online) -

about the part that makes the xx increase by 1...

1) (SUBSTRING(colName, 2, 2) - gives you the xx part - it says take the value colName (let's say it is 'R012005') & give me 2 characters starting from the second character - so this will give you '01'

2)CAST(SUBSTRING(colName, 2, 2) AS INT) + 1 - the cast just converts the string characters to integer so 1 can be added to it - this should give you 2

3)(MAX(CAST(SUBSTRING(colName, 2, 2) AS INT)) + 1)) - when you do a MAX...it first takes the maximum xx value for a given year before adding the 1 to it - so if you have 23 in your xx part, this will return 24.

As for how to use the T-Sql - you would create a stored procedure in your database (as in the post above) & "call" it from your vb.net application.

Hopefully, things are a little clearer now ?!?!

**ASCII stupid question, get a stupid ANSI !!!**