Error : Column name or number of supplied values d

问题:

I'm currently working on my little databases project, but I have already encountered problems. I ave a two tables which generated scripts are posted underneath and now I'm trying to write a procedure which will add a record first to smaller one and next to the second using to it ID from the first one. But I'm getting an error: "Column name or number of supplied values does not match table definition." in line which tries to add default values to the table Klienci. I have no idea whats going on because there is no column or arguments so there should be no problem, at least like this one.

First table :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[KlienciIndywidualni](
    [IDKlientaIndywidualnego] [int] IDENTITY(1,1) NOT NULL,
    [IDKlienta] [int] NOT NULL,
    [Imie] [nvarchar](50) NOT NULL,
    [Nazwisko] [nvarchar](50) NOT NULL,
    [NumerLegitymacji] [int] NULL,
    [Adres] [nvarchar](50) NOT NULL,
    [Miasto] [nvarchar](50) NOT NULL,
    [KodPocztowy] [nvarchar](50) NOT NULL,
    [Kraj] [nvarchar](50) NOT NULL,
    [Telefon] [int] NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_KlienciIndywidualni] PRIMARY KEY CLUSTERED 
(
    [IDKlientaIndywidualnego] 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].[KlienciIndywidualni]  WITH CHECK ADD  CONSTRAINT [FK_KlienciIndywidualni_Klienci] FOREIGN KEY([IDKlienta])
REFERENCES [dbo].[Klienci] ([IDKlienta])
GO

ALTER TABLE [dbo].[KlienciIndywidualni] CHECK CONSTRAINT [FK_KlienciIndywidualni_Klienci]
GO

ALTER TABLE [dbo].[KlienciIndywidualni]  WITH CHECK ADD  CONSTRAINT [CK_KlienciIndywidualni] CHECK  ((len([Telefon])>=(9)))
GO

ALTER TABLE [dbo].[KlienciIndywidualni] CHECK CONSTRAINT [CK_KlienciIndywidualni]
GO

ALTER TABLE [dbo].[KlienciIndywidualni]  WITH CHECK ADD  CONSTRAINT [CK_KlienciIndywidualni_mail] CHECK  (([Email] like '%@%.[a-z][a-z][a-z]'))
GO

ALTER TABLE [dbo].[KlienciIndywidualni] CHECK CONSTRAINT [CK_KlienciIndywidualni_mail]
GO

Second one:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Klienci](
    [IDKlienta] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Klienci] PRIMARY KEY CLUSTERED 
(
    [IDKlienta] 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

Procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dodaj_klienta_indywidualnego 
-- parametry
@Imie nvarchar(50),
@Nazwisko nvarchar(50),
@Adres nvarchar(50),
@KodPocztowy nvarchar(50),
@Miasto nvarchar(50) ,
@Kraj nvarchar(50),
@Telefon int,
@Email nvarchar(50),
@NumerLegitymacji nvarchar(50) = null

AS
BEGIN

SET NOCOUNT ON;
declare @IDKlienta as int;

begin try
    begin tran
    --dodaj klienta
    INSERT INTO Klienci
    DEFAULT VALUES
    set @IDKlienta = @@IDENTITY;

    --dodaj klienta indywidualnego
    INSERT INTO KlienciIndywidualni
    VALUES(@IDKlienta, @Imie, @Nazwisko, @Adres, @Miasto, 
    @KodPocztowy, @Kraj, @Telefon, @Email);
    COMMIT TRAN
end try
begin catch
    declare @error as varchar(127)
    set @error = (Select ERROR_MESSAGE())
    RAISERROR('Nie mozna dodac osoby-klienta, blad danych. %s', 16, 1, @error);
    ROLLBACK TRAN
end catch
END
GO

回答1:

Your second insertion needs ten parameters, you only provided 9, if you will skip the NumerLegitymacji field then you should specify each column by its name, cause the insertion now awaits the last NOT NULL column.

    --dodaj klienta indywidualnego
    INSERT INTO KlienciIndywidualni (
    IDKlienta,
    Imie,
    Nazwisko,
    Adres,
    Miasto,
    KodPocztowy,
    Kraj,
    Telefon,
    Email
)
    VALUES(@IDKlienta, @Imie, @Nazwisko, @Adres, @Miasto, 
    @KodPocztowy, @Kraj, @Telefon, @Email);
    COMMIT TRAN