Hi,I'm having a problem auto incrementing my reference number. I have created a user request form, every time a user clicks save, the data entered by the user will be stored in the database and the reference number will auto increment.
The problem here is that my reference number is char(9), for example, 2006-0001, 2006-0002, etc until it reaches 2006-9999. I have created a stored procedure.How am I going to auto increment my reference number.Please help, i'm useing VS 2005 VB.

 I do I make use of this code,
intTemp = cint(right(strMyString,4))
intTemp=intTemp + 1

strMyNewString = "2006-" & cstr(intTemp)


Below is the stored procedure.
CREATE PROCEDURE [dbo].[My_SSRF_Insert]
    @RequestNo char(9),
    @System char(30),
    @Module char(9),
    @Description char(255),
    @Priority char(6),
    @Approver char(30),
    @Status char(6)

AS

---//DECLARE @RequestNo char(9)
SELECT MAX(RequestNo) FROM My_SSRF


-----///SET NOCOUNT ON

INSERT INTO [My_SSRF]
(
            [RequestNo],
            [System],
            [Module],
            [Description],
            [Priority],
            [Approver],
                            [Status]

)
VALUES
(
            @RequestNo,
            @System,
            @Module,
            @Description,
            @Priority,
            @Approver,
            @Status

)
GO

-- prepare test data
declare @test table (t varchar(50))

insert @test
select '2006-0001' union all
select '2006-0049'

-- show data before inserting new values
select * from @test

declare @number varchar(50)

select @number = '2006-' + right('0000' + convert(varchar, t.n + 1), 4)
from (
        select max(convert(int, substring(t, 6, 50))) n
        from @test
    ) t

-- show new number
select @number

Or more generic, use this code!

-- prepare test data
declare @test table (t varchar(50))

insert @test
select '2006-0001' union all
select '2007-0012' union all
select '2005-4004' union all
select '2005-0123' union all
select '2007-0002' union all
select '2006-0049'

-- show data before inserting new values
select * from @test

declare @number varchar(50),
    @Year smallint

select @year = 2005

select @number = cast(@year as char(4)) + '-' + right('0000' + convert(varchar, t.n + 1), 4)
from (
        select max(convert(int, substring(t, 6, 50))) n
        from @test
    ) t

-- show new number
select @year 'Year', @number 'Next serial'