Re: Resticting No of entries

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Paul in Harrow (PaulinHarrow_at_discussions.microsoft.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 07:55:03 -0700

Harry, Hugo & Steve(wow!) thanks,
I've come up with this:
CREATE TABLE dbo.tblFutureClassesSub (
LDUserName varchar (35) NOT NULL ,
CourseName varchar (75) NOT NULL ,
BigClassCode varchar (25) NOT NULL ,
ClassDate smalldatetime NOT NULL ,
Attended varchar (35) NOT NULL ,
Num4Count smallint DEFAULT 1 ,
Primary Key (LDUserName, BigClassCode, ClassDate) ,
CONSTRAINT ck_NoInClass CHECK ( COUNT (BigClassCode, ClassDate, Num4Count) >
10) ) --I'm trying to count a concat of the three fields
I get "The COUNT function requires 1 arguments."
I'm so close

Paul.

"Steve Kass" wrote:

> Paul,
>
> You might model this situation by considering an "available spot" in a
> class as a real entity that should be included in your database as a
> table row. You could then insert all course seats into your database
> when a course is inserted, and update seat attributes as students fill
> them by registering. You might want to hide some of the details from
> the user, and here's an start on one possible solution.
>
> The table N8000 has structure create table N8000(N int not null
> primary key) and should be filled with integers from 1 to 8000 and kept
> handy as a permanent table.
>
>
> create table Course (
> courseID int not null primary key,
> title varchar(50) not null,
> capacity int not null
> )
> go
>
> create table Seat (
> courseID int
> references Course(courseID)
> on update cascade
> on delete cascade,
> priority int not null,
> studentID int null,
> primary key (courseID, priority)
> )
> go
>
> create trigger Course_trig on Course
> for insert as
> insert into Seat
> select courseID, N8000.N, null
> from inserted join N8000
> on N8000.N between 1 and inserted.capacity
> go
>
> create view Enrollment as
> select courseID, studentID
> from Seat
> where studentID is not null
> go
>
> create trigger Enrollment_t on Enrollment
> instead of insert as
> if @@rowcount > 1 begin
> print 'Multirow inserts prohibitied'
> -- don't allow this; handle error
> end
> update Seat set
> studentID = i.studentID
> from inserted i
> where Seat.priority = (
> select min(priority) from Seat S
> where S.CourseID = Seat.CourseID
> )
> go
>
> drop view Enrollment
> drop table Seat
> drop table Course
>
> -- Steve Kass
> -- Drew University
> -- Ref: 157239A7-CD64-4C6B-837A-AF528DDF47B4

Relevant Pages

  • Re: Resticting No of entries
    ... You could then insert all course seats into your database ... and update seat attributes as students fill ... The table N8000 has structure create table N8000(N int not null ... select courseID, studentID ...
    (microsoft.public.sqlserver.programming)
  • Re: Visual C++.NET - Help!
    ... Maybe you could use a pair of bool arrays instead of one int array? ... First Class) evaluated true, ... if they wanted a seat in another section. ...
    (comp.lang.cpp)
  • Re: cracked seat base
    ... velo base here int the us i think you can only get a whole seat ... Get a carbon fibre one. ...
    (rec.sport.unicycling)