Preventing overlapping data entry

Hello Everyone,

I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.

The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.

Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.

After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.

Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.

Thanks in advance,

Brian

This query should give you the timeIDs that have not been scheduled:

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )

Replace the date with the date the user has selected.|||

This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )

This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||

Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:

SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))

Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.

Brian

preventing duplicate entry for a given foreign key in db table

Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..

hi nb123

try this

Alter Table YOURTABLENAME
Add Constraint NAMEOFCONSTRAINT UNIQUE (COLUMN NAME)

hope it helps

|||

Are you trying to achieve something like this ?

create table checktest(col1int ,col2int ,col3int ,col4int)GOalter table checktestadd constraint [unq_checktest_col123]unique ( col1 , col2 , col3 )insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 1insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 1

Here the second and the fourth insert query will fail as they violate the unique value constraint.

Hope this will help.

|||

Thanks guys,,yes this is what i am trying to achieve...how do i add the constraints in existing table?

col1 col2 col2 col4
1 12 val1 50
2 12 val1 60 >>this shud not be allowed
3 12 val2 30
4 13 val1 233 >>this is okay,as col2 is changed
5 13 val1 1111 >>this shud not be allowed

|||

I've also written the alter table query in my earlier post.

alter table <tablename>
add constraint [<name of the constraint>]unique ( col1 , col2 , col3 )

After you execute this query, the duplicate values for the col1, col2, col3 combination won't be allowed. 

|||

Thanks dhimant,i tried doing it using sql server management studio,i wasn't able to select 'alter table'..it was coming grayed..i thought of drop and recreate it with the above changes,i was getting the syntax error.I basically scripted the createTable from menu and tried adding 'alter table clause stuff manually to it and it was giving me syntex error.i am sure i wasn't doing something right...can u please help me with this...

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/24/2007 10:00:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/**** manually added ***/
ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE] add CONSTRAINT [roleConstraint]
unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.
GO
SET ANSI_PADDING OFF

|||

nb123:

unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.

use ( ) instead of [ ]. Both of them have different meaning and applied for different purposes. So, your modified alter statement would be

ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE]add CONSTRAINT [roleConstraint]unique ( SupportRoleID,SeasonID,Role )

Hope this will help.

|||

okay,it saved fine after i made the above change but its allowing the duplicate entries in the given column,i am not getting any unique key violation sort of error.Also when i script the table as create,to see the syntax,this is what i see..what iam doing wrong,i was expecing error while entering the duplicate value in role for a given foreign key..

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/25/2007 00:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [roleConstraint] UNIQUE NONCLUSTERED .>> this 'NOTCLUSTERED stuff got added after i saved
(
[SupportRoleID] ASC,
[SeasonID] ASC,
[Role] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

|||

I got your problem mate. First you run the following queries, I've explained the solution below the queries:

alter table SEASON_SUPPORT_ROLEdrop constraint roleConstraint GOalter table SEASON_SUPPORT_ROLEadd constraint roleConstraintunique ( [SeasonID] , [Role])GO

Now, what is happening is that you've mentioned the SupportRoleId column in your unique key constraint. That column is an identity one and will be auto-incremented, that means the value of this column will always be different than the values already present in your table.

If you've provided more than one columns to be a part of your unique key constraint, then the combination of all the columns should not be repeated and if that happens then only the constraint is said to be violated.

Actually, you don't need the SupportRoleId column in your constraint as it is always going to be unique, hence will make the entire collection of all 3 columns to be unique and that won't violate the constraint and SQL Server will allow these kind of records.

What I've done in the query is to remove the constraint and then recreated it with excluding the SupportRoleId column.

Hope this will help.

|||

ah..that makes sense...i changed the query and now its working as expected..thanks Dhimant for beings so patient with me and helping me with this..

|||

Well, it was a good problem to solve for me too. You should mark as answer the post which solved your problem, that way the member gets credit for solving the problem and other visitors also come to know which post solved your problem.