尝试在表中插入或更新多行时发生异常
本文关键字:异常 更新 插入 | 更新日期: 2023-09-27 18:27:21
我正在尝试将Datatable中的多行插入并更新到数据库表中。以下是将Datatable传递到存储过程的代码:
DataTable dtUserRights = new DataTable();
PropertyInfo[] piT = typeof(clsRightsAL).GetProperties(); //Gets all column values with there datatype
foreach (PropertyInfo pi in piT)
{
//create a datacolumn for each property
DataColumn dc = new DataColumn(pi.Name, pi.PropertyType);
dtUserRights.Columns.Add(dc);
}
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
for (int item = 0; item < objUserRightsALLst.Count; item++)
{
DataRow dr = dtUserRights.NewRow();
for (int property = 0; property < dtUserRights.Columns.Count; property++)
{
dr[property] = piT[property].GetValue(objUserRightsALLst[item], null);
}
dtUserRights.Rows.Add(dr);
}
objCmd = new SqlCommand();
objCmd.CommandText = "sp_AD_InsertUpdateGroupRights";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = con;
objCmd.Parameters.Clear();
SqlParameter tableParameter = objCmd.Parameters.AddWithValue("@GroupRightslist", dtUserRights);
tableParameter.SqlDbType = SqlDbType.Structured;
intCount = objCmd.ExecuteNonQuery();
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
catch (Exception e)
{
throw e;
}
下面是我呼叫的SP:
CREATE PROCEDURE [dbo].[sp_AD_InsertUpdateGroupRights]
@GroupRightslist as [dbo].[GroupRightsList] ReadOnly
AS
BEGIN
if Not Exists(Select intID from tblADGroupRights where intID = (Select l.intID from @GroupRightslist l))
Begin
insert into tblADGroupRights(IntGroupID,intMenuID,blAdd,blEdit,blView,blGrant,blStatus,strUserCreated
) Select l.IntGroupID,l.intMenuID,l.blAdd,l.blEdit,l.blView,l.blGrant,l.blStatus,
l.strBy from GroupRightslist l
End
else
begin
update A set blAdd = l.blAdd,blEdit = l.blEdit,blView = l.blView,blGrant = l.blGrant
FROM tblADGroupRights A INNER JOIN @GroupRightslist l on A.intID = l.intID
where A.intID = l.intID
end
END
当我将单行传递给存储过程时,数据会被插入,但当数据表有多条记录时,我会得到异常:
子查询返回了多个值。当子查询跟在=、!=、<lt;=,>,>=或者当子查询用作表达式时。
我的更新Query根本不起作用。
我的用户定义表类型:
CREATE TYPE [dbo].[GroupRightsList] AS TABLE(
[intID] [int] NULL,
[intProfileID] [int] NULL,
[IntGroupID] [int] NULL,
[intMenuID] [int] NULL,
[blAdd] [bit] NULL,
[blEdit] [bit] NULL,
[blView] [bit] NULL,
[blGrant] [bit] NULL,
[blStatus] [bit] NULL,
[strBy] [nvarchar](max) NULL
)
我知道,当Select查询返回的值超过1,而我们没有正确处理它时,通常会出现这种异常,但在我使用用户类型表的情况下,我看到许多网站都以与我相同的方式显示了它。
子查询返回了多个值。当子查询跟在=、!=、<lt;=,>,>=或者当子查询用作表达式时。
这纯粹是一个sp错误。这意味着在您的sp中,有一个子查询返回了多个不允许的记录。下面很可能是线
Select intID from tblADGroupRights where intID = (Select l.intID from @GroupRightslist l)
要么将其更改为
Select intID from tblADGroupRights where intID = (Select Top 1 l.intID from @GroupRightslist l)
或
Select intID from tblADGroupRights where intID in (Select l.intID from @GroupRightslist l)