SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Column does not allow nulls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PeteLeHoq
Starting Member

35 Posts

Posted - 09/26/2013 :  17:22:07  Show Profile  Reply with Quote
I'm inserting data from a c# webservice into a table via a stored procedure, but I get a Column does not allow nulls on the @alert_id column/field. It is set as int and allow nulls is not ticked.

Here's the sql:

USE [aren]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [aren1002].[ArenAlertInsert]
(
	@alert_id 	as int OUTPUT,
	@first_name 	as varchar(100)=NULL,
	@last_name 	as varchar(100)=NULL,
	@email	 	as varchar(100),
	@state	 	as varchar(3)=NULL,
	@birth_date 	as smalldatetime=NULL,
	@create_user	as varchar(10)=NULL,
	@gender	as char(1)=NULL,
	@referral_id	as smallint=NULL,
	@affiliate_id	as int=NULL
)
AS
	SET NOCOUNT ON


	INSERT INTO [HOLIDAY_ALERT]
	(
		first_name,
		last_name,
		email,
		state,
		birth_date,
		gender,
		referral_id,
		affiliate_id,
		create_user,
		create_date	
	)
	VALUES
	(
		@first_name,
		@last_name,
		@email,
		@state,
		@birth_date,
		@gender,
		@referral_id,
		@affiliate_id,
		@create_user,
		getdate()	
	)

	SET @alert_id = SCOPE_IDENTITY( )


and i'm using this c# to insert:


int result = 0;
var insertStr = ", @first_name = '" + (enquiry.FirstName ?? "") + "'" + ", @last_name = '" + (enquiry.LastName ?? "") + "'" +
                            ", @email               = '" + (enquiry.Email ?? "") + "'" +
                            ", @state               = '" + (enquiry.State ?? "") + "'" +
                            ", @birth_date          = '" + bDateStr + "'" +
                            ", @gender              = '" + (enquiry.Gender ?? "") + "'" +
                            ", @referral_id         = '" + (enquiry.referral_id ?? "") + "'" +
                            ", @affiliate_id        = '" + (enquiry.affiliate_id ?? "") + "'";

            var booked = _db.QueryValue("Exec aren1002.ArenAlertInsert @alert_id = @0" + insertStr, result);

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 09/26/2013 :  17:35:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
Show us the DDL for the table (the CREATE TABLE statement, script it from SSMS by right clicking on the table).

Your code is vulnerable to SQL injection by the way. You should never concatenate queries like this. Use parameterized queries instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

PeteLeHoq
Starting Member

35 Posts

Posted - 09/26/2013 :  17:40:23  Show Profile  Reply with Quote
This is the table as requested.

USE [arend]
GO

/****** Object: Table [aren1002].[HOLIDAY_ALERT] Script Date: 09/26/2013 22:38:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [aren1002].[HOLIDAY_ALERT](
[alert_id] [int] NOT NULL,
[first_name] [varchar](100) NULL,
[last_name] [varchar](100) NULL,
[email] [varchar](100) NOT NULL,
[state] [varchar](3) NULL,
[birth_date] [smalldatetime] NULL,
[create_date] [smalldatetime] NOT NULL,
[create_user] [varchar](10) NULL,
[update_date] [smalldatetime] NULL,
[update_user] [varchar](10) NULL,
[delete_date] [smalldatetime] NULL,
[delete_user] [varchar](10) NULL,
[gender] [char](1) NULL,
[referral_id] [smallint] NULL,
[affiliate_id] [int] NULL,
CONSTRAINT [PK_HOLIDAY_ALERT] PRIMARY KEY CLUSTERED
(
[alert_id] 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 09/26/2013 :  17:49:30  Show Profile  Reply with Quote
The alert_id column does not have the identity property, but you are treating it in the stored procedure as though it did. So either you need to provide an alert_id to be inserted into the table, or alter the table to apply identity property to the table.

You can do this using the designer (right-click on the table in SSMS object explorer and select design) or drop and recreate the column with identity property via T-SQL. Even if you use the designer, the column will be dropped and recreated and so the ordinal position would change.
Go to Top of Page

PeteLeHoq
Starting Member

35 Posts

Posted - 09/26/2013 :  17:57:53  Show Profile  Reply with Quote
Thanks for the assistance, the primary key was missing from that column.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 09/26/2013 :  20:28:16  Show Profile  Reply with Quote
Please consider use of SqlCommand & parameters (AddWithValue) rather than dynamic SQL which will expose you to SQL injection.
Go to Top of Page

PeteLeHoq
Starting Member

35 Posts

Posted - 10/01/2013 :  10:11:45  Show Profile  Reply with Quote
Hmm, it stil does not let me insert a new record after dropping the table and recreating it, making the reply_id column as primary key, not null, and type of uniqueidentifier.

Is there something else I should check?

Here's the drop and create script:

USE [arend]
GO

/****** Object: Table [dbo].[BOOKING_REPLY] Script Date: 10/01/2013 15:10:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BOOKING_REPLY]') AND type in (N'U'))
DROP TABLE [dbo].[BOOKING_REPLY]
GO

USE [arend]
GO

/****** Object: Table [dbo].[BOOKING_REPLY] Script Date: 10/01/2013 15:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BOOKING_REPLY](
[reply_id] [uniqueidentifier] NOT NULL,
[booking_id] [int] NOT NULL,
[reply_type] [smallint] NULL,
[subject] [varchar](500) NULL,
[text_body] [text] NULL,
[sign_off] [varchar](1000) NULL,
[pay_flag] [char](1) NULL,
[cancel_flag] [char](1) NULL,
[insurance_flag] [char](1) NULL,
[sent_date] [smalldatetime] NULL,
[sent_user] [varchar](10) NULL,
[create_date] [smalldatetime] NULL,
[create_user] [varchar](10) NULL,
[update_date] [smalldatetime] NULL,
[update_user] [varchar](10) NULL,
[delete_date] [smalldatetime] NULL,
[delete_user] [varchar](10) NULL,
[intro_text] [varchar](1000) NULL,
[general_text] [varchar](3000) NULL,
[price_text] [varchar](4500) NULL,
[booking_details_flag] [char](1) NULL,
[attachment] [varchar](1500) NULL,
[proof_age_flag] [char](1) NULL,
[visa_details_flag] [char](1) NULL,
[terms_flag] [char](1) NULL,
[attachment2] [varchar](1500) NULL,
[attachment3] [varchar](1500) NULL,
[link_flag] [char](1) NULL,
[inclusion_flag] [char](1) NULL,
CONSTRAINT [PK_BOOKING_REPLY_1] PRIMARY KEY CLUSTERED
(
[reply_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 10/01/2013 :  11:06:28  Show Profile  Reply with Quote
In my last post, I think I did not explain well what I was trying to say. So let me try again.

Let us look at your original post (Posted - 09/26/2013 : 17:22:07) and the table schema that you posted at 09/26/2013 : 17:40:23. In your stored procedure, you are passing in 10 parameters and inserting those 10 into the table. Your table has 15 columns - which means that the other 5 columns for which you are not inserting data have to be either nullable, or have a default value, or be an identity column. The first column - alert_id - is not nullable, does not have a defautl value, and is not an identity column. So that is what is causing the error.

In your stored procedure you are doing a "SET @alert_id = SCOPE_IDENTITY( )" which suggests that alert_id should be an identity column. So you should change the create table to the following (see change in red):
USE [arend]
GO

/****** Object: Table [aren1002].[HOLIDAY_ALERT] Script Date: 09/26/2013 22:38:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [aren1002].[HOLIDAY_ALERT](
[alert_id] [int] NOT NULL IDENTITY(1,1),
[first_name] [varchar](100) NULL,
[last_name] [varchar](100) NULL,
[email] [varchar](100) NOT NULL,
[state] [varchar](3) NULL,
[birth_date] [smalldatetime] NULL,
[create_date] [smalldatetime] NOT NULL,
[create_user] [varchar](10) NULL,
[update_date] [smalldatetime] NULL,
[update_user] [varchar](10) NULL,
[delete_date] [smalldatetime] NULL,
[delete_user] [varchar](10) NULL,
[gender] [char](1) NULL,
[referral_id] [smallint] NULL,
[affiliate_id] [int] NULL,
CONSTRAINT [PK_HOLIDAY_ALERT] PRIMARY KEY CLUSTERED 
(
[alert_id] 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000