Persistant Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Persistant Error

Author

Topic

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-20 : 23:33:00

I am a programming student, having trouble with an assignment, which my classmates have not been able to help me with. Thought I'd ask the pros.

I setup the database, created some stored procedures and setup all the relationships and settings. When I try to run a stored proc to add a row to one of the tables, it tells me that my insert cannot insert a null value into my primary key feild, and terminates the statement.

I changed the is_identity setting in the design properties of the table, which is the only other solution I have found to this problem, why isn't the insert giving it the auto-number it should?

my insert looks like this:

INSERT INTO Accounts (CustNo, Type)--The accountID primary key is the VALUES (<inpt prm1>, <inpt prm2>) --first column
SELECT <out prm> = SCOPE_IDENTITY()

Any help would be appreciated.
VALUES

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 23:37:57

Sounds like you've got an application bug in that you are passing a NULL value. What programming language are you using? Can you post the relevant code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-20 : 23:42:04

Oh im using SQL in MS SQL Server
and this is the stored procedure that I am having trouble with.
(actually it seems to happen whenever I try an insert, but this is the first stored proc to die)

USE [Assign1Banking]
GO
/****** Object: StoredProcedure [dbo].[pAddAccount] Script Date: 10/20/2009 22:55:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pAddAccount] (@CustNo INT, @Type AccountType, @AccountID INT = 0 OUTPUT)
AS

INSERT INTO Accounts (CustNo, Type)
VALUES (@CustNo, @Type)
SELECT @AccountID = SCOPE_IDENTITY()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 23:53:31

Could you post the CREATE TABLE statement for Accounts? Use Management Studio to generate the script for you. Post it here.

Oh and how are you executing it?

Here's how it should look on the execute:

DECLARE @AccountID int

EXEC pAddAccount @CustNo = 999, @Type = '', @AccountID = @AccountID OUTPUT

I wasn't sure what the data type of @Type is, since it looks like you are using a user-defined data type.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-21 : 00:01:22

Yup thats exactly how I execute it, finding the script now...

here it is (i think)

USE [Assign1Banking]
GO

/****** Object: Table [dbo].[Accounts] Script Date: 10/21/2009 00:00:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Accounts](
[AccountID] [int] IDENTITY(1,1) NOT NULL,
[CustNo] [int] NULL,
[Type] [dbo].[AccountType1] NULL,
[Balance] [money] NULL,
[AccruedInterest] [money] NULL,
[WithdrawalCount] [smallint] NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY NONCLUSTERED
(
[AccountID] 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].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Accounts_Custs] FOREIGN KEY([CustNo])
REFERENCES [dbo].[Custs] ([CustNo])
GO

ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [FK_Accounts_Custs]
GO

ALTER TABLE [dbo].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Accounts_Rates] FOREIGN KEY([Type])
REFERENCES [dbo].[Rates] ([Type])
GO

ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [FK_Accounts_Rates]
GO

ALTER TABLE [dbo].[Accounts] WITH CHECK ADD CONSTRAINT [CK_Accounts] CHECK (([WithdrawalCount]>=(0)))
GO

ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [CK_Accounts]
GO

ALTER TABLE [dbo].[Accounts] WITH CHECK ADD CONSTRAINT [CK_Accrued] CHECK (([AccruedInterest]>=(0)))
GO

ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [CK_Accrued]
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 00:34:05

I think the problem is with your user-defined data type: AccountType1. I have a feeling it is set to NOT NULL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-21 : 00:43:32

Yes it is, but the errors im getting are all coming from the primary key column

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 00:47:36

Could you post the error?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-21 : 00:50:38

Msg 515, Level 16, State 2, Procedure pAddAccount, Line 3
Cannot insert the value NULL into column 'AccountID', table 'Banking.dbo.Accounts'; column does not allow nulls. INSERT fails.
The statement has been terminated.

When I run this code :

DECLARE @AccountId INT
EXEC pAddAccount 1,'Savings',@AccountID OUTPUT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 00:56:38

When I run your code minus the user-defined data type, it works great.


USE master
GO

CREATE TABLE [dbo].[Accounts](
[AccountID] [int] IDENTITY(1,1) NOT NULL,
[CustNo] [int] NULL,
[Type] int NULL,
[Balance] [money] NULL,
[AccruedInterest] [money] NULL,
[WithdrawalCount] [smallint] NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY NONCLUSTERED 
(
[AccountID] 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
/****** Object: StoredProcedure [dbo].[pAddAccount] Script Date: 10/20/2009 22:55:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pAddAccount] (@CustNo INT, @Type int, @AccountID INT = 0 OUTPUT)
AS

INSERT INTO Accounts (CustNo, Type) 
VALUES (@CustNo, @Type)
SELECT @AccountID = SCOPE_IDENTITY()

GO

DECLARE @AccountID int

EXEC pAddAccount @CustNo = 999, @Type = 1, @AccountID = @AccountID OUTPUT

DROP PROC pAddAccount
DROP TABLE Accounts

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

hungrycookpot
Starting Member

6 Posts

Posted - 2009-10-21 : 01:00:22

Ok i will try it without the user defined type, but I think i need that for the project requirements. (also the base type of that user def type is char(8), if it matters)

Nope no effect, exact same error as before when I change all references to AccountType to CHAR(8)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-21 : 01:02:45

I'm not sure what to tell you as it works fine on my side. Can you drop/create the table just in case?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources