SQL Server - Can anyone correct this mistake on here

Asked By Mano on 26-Jul-10 09:22 AM
how can i insert values to the following table in the Event ID
this is my tble codings 

create table EVENTS
(Event_ID char (20) primary key,  
 EventName char (20)  null,
 EventTypeID char (20)  null,
 Location varchar(50)  null,
 StartDate DATETIME   null,
 EndDate DATETIME  null,
 StaffingRequired char (20),
 EventDescription varchar (50),
 EmployeeID char(20),
 AttendeeID char(20),
 NoofPeople char(20) constraint cknofple check ( NoofPeople > 0) not null
)

in this one i have created stored procedure in Event_ID column this is my stored procedure but it shows some error

create procedure prc_EVENID                
as
begin
    declare @EVEID char(15)
    set @EVEID='E00001'
    if not exists(select * from EVENTS)
      set @EVEID='E00001'
    else
      begin
          select @EVEID= right(rtrim(ltrim(Event_ID)),4) from EVENTS
          select @EVEID=
          case
              when @EVEID>=0 and @EVEID<9 then 'E0000'+convert(varchar,@EVEID+1)
              when @EVEID>=9 and @EVEID<99 then 'E000'+convert(varchar,@EVEID+1)
              when @EVEID>=99 and @EVEID<999 then 'E00'+convert(varchar,@EVEID+1)
              when @EVEID>=999 and @EVEID<9999 then 'E0'+convert(varchar,@EVEID+1)
              when @EVEID>=9999 and @EVEID<99999 then 'E'+convert(varchar,@EVEID+1)
          end
      end
    insert events values (@EVEID)
end


Error is :

Msg 213, Level 16, State 4, Procedure prc_EVENID, Line 20
Insert Error: Column name or number of supplied values does not match table definition.

i have tried many times but its not working


 
Super Man replied to Mano on 26-Jul-10 09:28 AM

you must have to pass following bolded items.

Event_ID char (20) primary key,
EventName char (20) null,
EventTypeID char (20) null,
Location varchar(50) null,
StartDate DATETIME null,
EndDate DATETIME null,
StaffingRequired char (20),
EventDescription varchar (50),
EmployeeID char(20),
AttendeeID char(20),
NoofPeople char(20) constraint cknofple check ( NoofPeople > 0) not null




in insert query you are only inserting event id , that why it gives you error.



Shunmuga Nathan replied to Mano on 27-Jul-10 07:33 AM
Try the bellow
"insert events(Event_ID) values (@EVEID)"

Complete code


create table EVENTS
(Event_ID char (20) primary key,  
 EventName char (20)  null,
 EventTypeID char (20)  null,
 Location varchar(50)  null,
 StartDate DATETIME   null,
 EndDate DATETIME  null,
 StaffingRequired char (20),
 EventDescription varchar (50),
 EmployeeID char(20),
 AttendeeID char(20),
 NoofPeople char(20) constraint cknofple check ( NoofPeople > 0) not null
)

in this one i have created stored procedure in Event_ID column this is my stored procedure but it shows some error

create procedure prc_EVENID          
as
begin
    declare @EVEID char(15)
    set @EVEID='E00001'
    if not exists(select * from EVENTS)
      set @EVEID='E00001'
    else
      begin
          select @EVEID= right(rtrim(ltrim(Event_ID)),4) from EVENTS
          select @EVEID=
          case
              when @EVEID>=0 and @EVEID<9 then 'E0000'+convert(varchar,@EVEID+1)
              when @EVEID>=9 and @EVEID<99 then 'E000'+convert(varchar,@EVEID+1)
              when @EVEID>=99 and @EVEID<999 then 'E00'+convert(varchar,@EVEID+1)
              when @EVEID>=999 and @EVEID<9999 then 'E0'+convert(varchar,@EVEID+1)
              when @EVEID>=9999 and @EVEID<99999 then 'E'+convert(varchar,@EVEID+1)
          end
      end
    insert events(Event_ID) values (@EVEID)
end


Jai replied to Shunmuga Nathan on 28-Jul-10 07:32 PM
In your table you have a column "NoofPeople char(20) constraint cknofple check ( NoofPeople > 0) not null". This is creating the issue. When you inserting a value only for Event_Id, then it inserts Null to other columns. As you defined, NoofPeople should not be null. so it declined your insertion request. Thats why you are getting the error. You can solve this in two ways:

1. Alter the column NoofPeople to allow nulls
2. Insert some values to NoofPeople column.

Thanks.