Inserting the values with condition

Question

Using SQL Server 2005

When i insert the date it should compare the date in the table.

If it is equal with other date, it should display a error message and also it should allow only to insert the next date.

For Example

Table1

Date 

20091201
20091202

Insert into table1 values('20091202')

The above query should not allow to insert the same value

Insert into table1 values('20091204')

The above query also should not allow to insert the long gap date.

The query should allow only the next date. It should not allow same date and long gap date.

How to insert a query with this condition.

Is Possible in SQL or VB.Net

Need SQL Query or VB.Net code Help

Answer 1

You could use a where clause to ensure that the previous day is present in the table, and the current day is not:

insert into table1 ([dateColumn]) 
select '20091204'
where exists (
    select * from table1 where [dateColumn] = dateadd(d,-1,'20091204')
)
and not exists (
    select * from table1 where [dateColumn] = '20091204'
)

if @@rowcount <> 1
    raiserror ('Oops', 16, 1)

If the insert succeeds, @@rowcount will be set to 1. Otherwise, an error is returned to VB using raiserror.

Answer 2

Sounds like your date field should just be unique with auto-increment.

Answer 3

Why not just have a table of dates set up in advance, and update a row once you want to "insert" that date?

I'm not sure I understand the point of inserting a new date only once, and never allowing a gap. Could you describe your business problem in a little more detail?

Of course you could use an IDENTITY column, and then have a computed column or a view that calculates the date from the number of days since (some date). But IDENTITY columns do not guarantee contiguity, nor do they even guarantee uniqueness on their own (unless you set up suc a constraint separately).

Preventing duplicates should be done at the table level with a unique constraint, not with a query. You can check for duplicates first so that you can handle errors in your own way (rather than let the engine raise an exception for you), but that shouldn't be your only check.