如何在sql表中插入更新多条记录？ [英] How to insert update multiple records in sql table?

问题描述

这是我的程序..

This is My Procedure..

alter PROCEDURE Sp_AssignProjectToUser
(

 @Ref_UserID int =  null
, @Ref_ProjectID int  = null
, @ActiveFlag bit  =null
)
AS
  BEGIN
    INSERT INTO UserMappingProject (Ref_UserID
    , Ref_ProjectID
    , ActiveFlag)
      VALUES (@Ref_UserID, @Ref_ProjectID, @ActiveFlag)
  END

这个是C＃代码

This Is C# code

public int AssignProjectToUser(DataTable dtUserProjectMap, Int32 batchSize)
       {
int Result = 0;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Sp_AssignProjectToUser", con);

            cmd.Parameters.AddWithValue("@Ref_UserID", Ref_UserID);
            cmd.Parameters.AddWithValue("@Ref_ProjectID", ProjectID);
            cmd.Parameters.AddWithValue("@ActiveFlag ", flag);
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            Result = cmd.ExecuteNonQuery();
            con.Close();
            return Result;
            SqlDataAdapter adapter = new SqlDataAdapter();

            // Set the UPDATE command and parameters.
            adapter.UpdateCommand = new SqlCommand("Sp_AssignProjectToUser", con);
            adapter.UpdateCommand.Parameters.Add("@Ref_UserID", SqlDbType.Int, 5, "Ref_UserID");
            adapter.UpdateCommand.Parameters.Add("@Ref_ProjectID", SqlDbType.Int, 4, "Ref_ProjectID");
            adapter.UpdateCommand.Parameters.Add("@ActiveFlag", SqlDbType.Bit, 1, "ActiveFlag");
            adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
            adapter.UpdateBatchSize = 4;


             Set the INSERT command and parameter.
            adapter.InsertCommand = new SqlCommand("Sp_AssignProjectToUser", con);
            adapter.InsertCommand.Parameters.AddWithValue("@Ref_UserID","Ref_UserID");
            adapter.InsertCommand.Parameters.Add("@Ref_UserID", SqlDbType.Int, 5, "Ref_UserID");
            adapter.InsertCommand.Parameters.Add("@Ref_ProjectID", SqlDbType.Int, 4, "Ref_ProjectID");
            adapter.InsertCommand.Parameters.Add("@ActiveFlag", SqlDbType.Bit, 1, "ActiveFlag");

            adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

     
            // Set the batch size.
            adapter.UpdateBatchSize = batchSize;

           adapter.Update(dtUserProjectMap);
            return Result;

插入时显示错误记录。

Showing That Error When I Insert Records.

Cannot insert the value NULL into column 'Ref_UserID', table 'ZP505_VersionControlTracker.dbo.UserMappingProject'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'Ref_UserID', table 'ZP505_VersionControlTracker.dbo.UserMappingProject'; column does not allow nulls. INSERT fails.

推荐答案

我们无法帮助您：错误消息在两种情况下都非常明确：

We can't help you on that: the error message is pretty explicit in both cases:

Cannot insert the value NULL into ... column does not allow nulls. INSERT fails.

您的列被配置为无空值列 - 并且假设它具有后缀ID，它可能是主键列，它永远不会具有空值。



这意味着问题出在代码的其他地方：用于设置列的值为null：

Your column is configured as a "no null values" column - and given that it has the suffix "ID" it's probably a primary key column which can never have null values.

Which means the problem is elsewhere in your code: the value you are using to set your column is null:

cmd.Parameters.AddWithValue("@Ref_UserID", Ref_UserID);

所以在该行上设置一个断点，然后查看Ref_UserID并确认它。

然后看看你的代码是如何得到的那里，为什么它没有价值。

但是我们不能为你做那个。

So put a breakpoint on that line, and look at Ref_UserID and confirm it.
Then look at how your code got there, and why it contains no value.
But we can't do that for you.

这篇关于如何在sql表中插入更新多条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！