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.
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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO |
|
|
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. |
|
|
PeteLeHoq
Starting Member
37 Posts |
Posted - 2013-09-26 : 17:57:53
|
Thanks for the assistance, the primary key was missing from that column. |
|
|
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. |
|
|
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]GOUSE [arend]GO/****** Object: Table [dbo].[BOOKING_REPLY] Script Date: 10/01/2013 15:10:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO |
|
|
|
|
|
|
|