CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008stored-procedures
dcolumbus
dcolumbus

Reputation: 9714

SQL Server: Stored Procedure trying to insert NULL?

Msg 515, Level 16, State 2, Procedure CreateParty, Line 16
Cannot insert the value NULL into column 'Id', table 'DEVIN.dbo.Party';

column does not allow nulls. INSERT fails. The statement has been terminated.

(1 row(s) affected)

What the hell? My table consists of

Id (Primary Key)
FirstName
LastName

Here's my stored procedure:

USE [DEVIN]
GO
/****** Object:  StoredProcedure [dbo].[CreateParty]    Script Date: 11/19/2010 16:59:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[CreateParty]
    @firstname varchar(50),
    @lastname varchar(50)
    --@emailaddress varchar(100),
    --@zipcode char(5)
AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO Party (FirstName, LastName)
    Values (@firstname,@lastname)

END

Can someone find out why the heck I would be getting that error? It's so annoying...

Also, for some reason, SQL Management is only showing the "Id" column in intellisense. I have no idea why...

SCREENSHOT: alt text

Does it not already have the identity "PK_Party"? It does this automatically.. I must be missing something...

Upvotes: 0

Views: 4901

Answers (5)

Mayank Jain
Mayank Jain

Reputation: 235

For this you have to create primary key value identical I.e. auto increment. As primary key is heaving cluster index and it cannot be null. You have to create identity increment by 1 or any value.

As for now you have to create table again and through design you have to increment value.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96658

Drop and recreate your table with the identity specified. If you have data in it already, it is more complicated than that.

Example of scritp to create a table with an identity:

USE [MyDatabase]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MyTable') AND type in (N'U'))
DROP TABLE MyTable
GO


CREATE TABLE MyTable(
    [ID] [int] IDENTITY (1,1) NOT NULL,
    [LASTNAME] [varchar](500) NULL,
    [FIRSTNAME] [varchar](500) NULL


) ON [PRIMARY]

GO

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294487

INSERT INTO Party (FirstName, LastName)
    Values (@firstname,@lastname)

You do no specify a value for the column Id, therefore the INSERT will attempt to insert the default value:

  • for an IDENTITY() column, the default value will be the next identity
  • for a column with a DEFAULT constraint, the default value will be the value of the constraint
  • everything else will be NULL

Post the Exact definition of your table so we can tell you what is your column default value.

Upvotes: 8

arena-ru
arena-ru

Reputation: 1020

Try to use code 

INSERT INTO Party
Values (@firstname,@lastname)

instead of INSERT INTO Party (FirstName, LastName) Values (@firstname,@lastname)

Upvotes: -2

Joe
Joe

Reputation: 42666

Is ID an identity as well as a primary key? A PK by itself doesn't fill in any data for the column on insert.

Upvotes: 4

Related Questions