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
 Column does not allow nulls

Author  Topic 

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-09-26 : 17:22:07
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

38200 Posts

Posted - 2013-09-26 : 17:35:26
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

37 Posts

Posted - 2013-09-26 : 17:40:23
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-26 : 17:49:30
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

37 Posts

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-26 : 20:28:16
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

37 Posts

Posted - 2013-10-01 : 10:11:45
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 11:06:28
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
   

- Advertisement -