操作数类型冲突:nvarchar与用户定义的表类型不兼容

I'm new here and I'm facing a trouble currently, my scenario is that I wanted to insert and update data from Excel into a SQL Server table.

我是新来的，我现在遇到了麻烦，我的场景是我想把Excel中的数据插入到一个SQL Server表中。

For the insert part it works perfectly but when it comes to update I have no idea how should I do that. I have search for few methods and I found this is the most comfortable for me by using stored procedure.

对于插入部分，它工作得很好，但当它更新时，我不知道我该怎么做。我搜索了一些方法，我发现使用存储过程对我来说是最舒服的。

Here is my code that I'm using now. When I try it gave me this error:

这是我现在使用的代码。当我尝试的时候，它给了我一个错误:

Operand type *: nvarchar is incompatible with user-defined table type

操作数类型冲突:nvarchar与用户定义的表类型不兼容

--- Stored procedure ---
CREATE PROCEDURE [dbo].[chkUpdate]
    @Operator IC_CHK READONLY
AS
BEGIN
    set nocount on;

    MERGE INTO tb_Operator c1
    USING @Operator c2 ON c1.IC = c2.IC

    WHEN MATCHED THEN
      UPDATE SET
            c1.Name = c2.Name,
            --c1.IC = c2.IC,
            c1.Email = c2.Email,
            c1.Status = c2.Status,
            c1.Datetime = c2.Datetime

    WHEN NOT MATCHED THEN
      INSERT VALUES(c2.Name, c2.IC, c2.Email, c2.[Status], c2.[Datetime]);
end

--- User-defined table type ---
CREATE TYPE [dbo].[IC_CHK] as table 
(
    [Id] [int] NULL,
    [Name] [nvarchar](50) NULL,
    [IC] [bigint] NULL,
    [Email] [nvarchar](MAX) NULL,
    [Status] [nvarchar](50) NULL,
    [Datetime] [datetime] NULL
)

VS 2010 code:

VS 2010代码:

protected void btnImport_Click1(object sender, EventArgs e)
{
    int i = 0;

    try
    {
        string path = string.Concat(Server.MapPath("~/Excel/" + UploadExcel.FileName));
        UploadExcel.SaveAs(path);

        String strCon = string.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;",path);
        OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [sheet1$]", strCon);

        DataTable myds = new DataTable();
        myda.Fill(myds);

        for (i = 0; i <= myds.Rows.Count - 1; i++)
        {
            String constr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

            using (SqlConnection con = new SqlConnection(constr))
            using (SqlCommand cmd = new SqlCommand("chkUpdate"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;

                cmd.Parameters.AddWithValue("@Operator", path);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        MsgBox1.alert("Import success");
        View.Visible = true;

        vBinds();
    }
    catch (Exception ex)
    {
        MsgBox1.alert(ex.Message);
    }
}

Do check for me and I'm appreciate it. Thank you

请帮我查一下，我很感激。谢谢你！

P/S: I double confirm that my user-defined table type has the same data type with my table.

P/S:我再次确认我的用户定义的表类型与我的表具有相同的数据类型。

1 个解决方案

#1

In the INSERT in your MERGE statement, I would recommend to explicitly define the columns you're inserting into. Most likely, that's the cause of the error - you're inserting your columns - but you're not specifying which target columns those should be inserted into.

在MERGE语句中的INSERT中，我建议显式地定义要插入的列。很可能，这就是错误的原因——您正在插入列——但是您没有指定应该插入哪些目标列。

Since you're not specifying that, you must supply values for each column in the table, in the exact order in which they are defined - is that really the case?? E.g. what do you insert into your ID column in the table??

因为没有指定，所以必须按照定义的顺序为表中的每一列提供值——真的是这样吗?你在表格的ID栏中插入什么?

Assuming the ID column on your actual database table is an IDENTITY column, I would use (otherwise, you'd have to list ID in the list of columns to insert into as well and provide a value in the VALUES list of values):

假设您实际的数据库表上的ID列是标识列，我将使用(否则，您必须在列列表中列出ID才能插入，并在值列表中提供值):

WHEN NOT MATCHED THEN
  INSERT(Name, IC, Email, [Status], [DateTime])
  VALUES(c2.Name, c2.IC, c2.Email, c2.[Status], c2.[Datetime]);

and I would also recommend not to use T-SQL reserved keywords like status or datetime as your column names - you're just asking for trouble doing so. Use more expressive names - something that really relates to your business domain - not just datetime.....

我还建议不要使用T-SQL保留的关键字，比如status或datetime，作为你的列名——这样做可能会有麻烦。使用更有表现力的名称——一些真正与您的业务领域相关的名称——而不仅仅是datetime…