无法将值NULL插入表'SegY_Analyzer_DB.dbo.linkRulesRuleSets'的列'RuleID'中；列不允许为空.插入失败 [英] Cannot insert the value NULL into column 'RuleID', table 'SegY_Analyzer_DB.dbo.linkRulesRuleSets'; column does not allow nulls. INSERT fails

问题描述

 con = 新 SqlConnection(ConfigurationManager.ConnectionStrings ["  sqlCon"].ToString());

            尝试
            {
                如果(con.State == ConnectionState.已关闭)
                    con.Open();
            }
            捕获 {}
            cmd = 新 SqlCommand("  ，con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter p1 = cmd.Parameters.Add(" ，SqlDbType.Int);
            p1.Direction = ParameterDirection.Output;

            SqlParameter p2 = cmd.Parameters.Add(" ，SqlDbType.Int);
            p2.Direction = ParameterDirection.Output;

            SqlParameter p3 = cmd.Parameters.Add(" ，SqlDbType.Int);
            p3.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
如果(p1.Value == 空)
{
    con.Close();
    返回 假；
} 

解决方案

RuleId列需要一个值.在这里，您没有将其传递给存储过程.
方向是一个OUPUT参数.

SqlParameter p3 = cmd.Parameters.Add("@ RuleID"，SqlDbType.Int);
p3.Direction = ParameterDirection.Output;

检查表定义是否可以包含RuleId是否允许为空值

ALTER过程[dbo].[uspInsertRuleset] @RuleSetID int out
，@ RuleTypeID int out，
@RuleID int out，
@minRange nvarchar(20)，
@maxRange nvarchar(20)，
@invalidChars nvarchar(50)，
@isMandatory位，
@allowZeroAsValue位，
@RuleSetName nvarchar(20)，
@UserID int，
@RuleTypeName nvarchar(20)，
@RuleName nvarchar(50)
如
开始


从dbo.pltblRuleTypes中选择@ RuleTypeID = rtRuleTypeID，其中rtRuleTypeName = @ RuleTypeName;

从dbo.tblRules中选择@ RuleID = rulesRuleID，其中ruleAttribute = @ RuleName和rulesRuleTypeID = @ RuleTypeID;

从dbo.tblUserRuleSet中选择@ RuleSetID = Max(rsetRuleSetID)+1

如果@RuleSetID为null
设置@ RuleSetID = 1;
ELSE
从dbo.tblUserRuleSet中选择@ RuleSetID = Max(rsetRuleSetID)+1


更改表[dbo].[linkRulesRuleSets] nocheck约束全部


插入[dbo].[linkRulesRuleSets]
([[RuleSetID]
，[RuleTypeID]
，[RuleID]
，[minRange]
，[maxRange]
，[invalidChars])
值
(@RuleSetID，
@RuleTypeID，
@RuleID，
@minRange，
@maxRange，
@invalidChars)



更改表[dbo].[linkRulesRuleSets]检查约束全部


结束
这是我的存储过程，规则ID不为空

con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCon"].ToString());

            try
            {
                if (con.State == ConnectionState.Closed)
                    con.Open();
            }
            catch { }
            cmd = new SqlCommand("uspInsertRuleset", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter p1 = cmd.Parameters.Add("@RuleSetID", SqlDbType.Int);
            p1.Direction = ParameterDirection.Output;

            SqlParameter p2 = cmd.Parameters.Add("@RuleTypeID", SqlDbType.Int);
            p2.Direction = ParameterDirection.Output;

            SqlParameter p3 = cmd.Parameters.Add("@RuleID", SqlDbType.Int);
            p3.Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
if (p1.Value == null)
{
    con.Close();
    return false;
}

解决方案

RuleId column requires a value. Here you are not passing it into the stored procedure.
The direction is an OUPUT parameter.

SqlParameter p3 = cmd.Parameters.Add("@RuleID", SqlDbType.Int);
p3.Direction = ParameterDirection.Output;

Check table defination Constaint can RuleId Allow Null Value Or Not

ALTER procedure [dbo].[uspInsertRuleset] @RuleSetID int out
,@RuleTypeID int out,
@RuleID int out,
@minRange nvarchar(20),
@maxRange nvarchar(20),
@invalidChars nvarchar(50),
@isMandatory bit,
@allowZeroAsValue bit,
@RuleSetName nvarchar(20),
@UserID int,
@RuleTypeName nvarchar(20),
@RuleName nvarchar(50)
as
begin


select @RuleTypeID=rtRuleTypeID from dbo.pltblRuleTypes where rtRuleTypeName=@RuleTypeName;

select @RuleID=rulesRuleID from dbo.tblRules where ruleAttribute=@RuleName and rulesRuleTypeID=@RuleTypeID;

select @RuleSetID=Max(rsetRuleSetID)+1 from dbo.tblUserRuleSet

IF @RuleSetID is null
set @RuleSetID=1;
ELSE
select @RuleSetID=Max(rsetRuleSetID)+1 from dbo.tblUserRuleSet


alter table [dbo].[linkRulesRuleSets] nocheck constraint all


INSERT INTO [dbo].[linkRulesRuleSets]
([RuleSetID]
,[RuleTypeID]
,[RuleID]
,[minRange]
,[maxRange]
,[invalidChars])
VALUES
(@RuleSetID,
@RuleTypeID,
@RuleID,
@minRange,
@maxRange,
@invalidChars)



alter table [dbo].[linkRulesRuleSets] check constraint all


end
this is my stored procedure and rule id is not null

这篇关于无法将值NULL插入表'SegY_Analyzer_DB.dbo.linkRulesRuleSets'的列'RuleID'中；列不允许为空.插入失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！