locked
Need to verify value in Column of table1 before and after inserting ino table 2 RRS feed

  • Question

  • User1572083949 posted

    I am designing a small application to register for an event.  I have a location table, an event table, a registrant table and a bridge table for locations and sessions.

    My event table stores room capacity as an int and stores the number registered with an int.  I use the location table and event table only to populate two cascading dropdownlists on the form page.  The dropdowns work fine using stored procedures and a dataset.

    On clicking the submit button, I want to gather information from the registrant and insert it into the registrant table.  Before I do the insert, I want to check the number already registered for the event in the event table.  If the number registered already equals the event capacity, I want to close the event by updating a bit column from 1 to 0 and fail the attempted insert.  If not, I want to insert the gathered data into the registrant table and update the event table by incrementing the number registered by 1.  I want to do this in a stored procedure.  I will be passing the location id and the event id to the server.

    Here is my database structure.  I'm having problems figuring out how to do inserts to the registrants table and limiting them to the capacity of the events (sessions).  Been working on this all day and it is due tomorrow for testing.  Any ideas would help and I cannot change the underlying structure of the web app to use newer technology such as LINQ or the Entity Framework, I'm stuck using vanilla ADO.NET and Datasets.

    USE [myDatabase]
    GO
    
    /****** Object:  Table [dbo].[Locations]    Script Date: 12/22/2014 3:30:52 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Locations](
    	[LocationID] [int] IDENTITY(1,1) NOT NULL,
    	[LocationName] [varchar](50) NOT NULL,
    	[LocationRoom] [varchar](50) NOT NULL,
    	[LocationActive] [bit] NOT NULL CONSTRAINT [DF_Locations_LocationActive]  DEFAULT ((1)),
     CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 
    (
    	[LocationID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    USE [myDatabase]
    GO
    
    /****** Object:  Table [dbo].[Sessions]    Script Date: 12/22/2014 3:31:25 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Sessions](
    	[SessionID] [int] IDENTITY(1,1) NOT NULL,
    	[SessionName] [varchar](50) NOT NULL,
    	[SessionDate] [varchar](50) NOT NULL,
    	[SessionTime] [varchar](50) NOT NULL,
    	[SessionCapacity] [int] NOT NULL CONSTRAINT [DF_Sessions_SessionCapacity]  DEFAULT ((25)),
    	[NumberRegistered] [int] NOT NULL CONSTRAINT [DF_Sessions_NumberRegistered]  DEFAULT ((0)),
    	[SessionOpen] [bit] NOT NULL CONSTRAINT [DF_Sessions_SessionOpen]  DEFAULT ((1)),
     CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED 
    (
    	[SessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    USE [myDatabase]
    GO
    
    /****** Object:  Table [dbo].[UniqueSessions]    Script Date: 12/22/2014 3:32:37 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[UniqueSessions](
    	[UniqueSessionID] [int] IDENTITY(1,1) NOT NULL,
    	[rLocationID] [int] NOT NULL,
    	[rSessionID] [int] NOT NULL,
     CONSTRAINT [PK_UniqueSessions] PRIMARY KEY CLUSTERED 
    (
    	[UniqueSessionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[UniqueSessions]  WITH CHECK ADD  CONSTRAINT [FK_UniqueSessions_Locations] FOREIGN KEY([rLocationID])
    REFERENCES [dbo].[Locations] ([LocationID])
    GO
    
    ALTER TABLE [dbo].[UniqueSessions] CHECK CONSTRAINT [FK_UniqueSessions_Locations]
    GO
    
    ALTER TABLE [dbo].[UniqueSessions]  WITH CHECK ADD  CONSTRAINT [FK_UniqueSessions_Sessions] FOREIGN KEY([rSessionID])
    REFERENCES [dbo].[Sessions] ([SessionID])
    GO
    
    ALTER TABLE [dbo].[UniqueSessions] CHECK CONSTRAINT [FK_UniqueSessions_Sessions]
    GO
    
    USE [myDatabase]
    GO
    
    /****** Object:  Table [dbo].[Registrants]    Script Date: 12/22/2014 3:33:17 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[Registrants](
    	[RegistrantID] [int] IDENTITY(1,1) NOT NULL,
    	[RegistrantFirstName] [varchar](50) NOT NULL,
    	[RegistrantLastName] [varchar](50) NOT NULL,
    	[RegistrantDOB] [varchar](50) NOT NULL,
    	[RegistrantEmail] [varchar](50) NOT NULL,
    	[uSessionID] [int] NOT NULL,
     CONSTRAINT [PK_Registrants] PRIMARY KEY CLUSTERED 
    (
    	[RegistrantID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Registrants]  WITH CHECK ADD  CONSTRAINT [FK_Registrants_UniqueSessions] FOREIGN KEY([uSessionID])
    REFERENCES [dbo].[UniqueSessions] ([UniqueSessionID])
    GO
    
    ALTER TABLE [dbo].[Registrants] CHECK CONSTRAINT [FK_Registrants_UniqueSessions]
    GO

    Monday, December 22, 2014 3:40 PM

Answers

  • User-851967432 posted

    Here's some pseudo code to give you the idea:

    declare @vacancy as int

    SET @vacancy= select SessionCapacity - NumberRegistered from sessions where sessionid = @sessionid

    If @vacancy > 0 then
    begin
        --Insert registrant

        --update NumberRegistered.


    end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 22, 2014 5:14 PM