操作数类型冲突:表类型与int不兼容

本文关键字：类型 int 不兼容 冲突 操作数 | 更新日期: 2023-09-27 18:21:39

我正试图通过表将整数值集合传递给存储过程

请在下面找到C#代码

 int i = lbActors.Items.Count;
            int[] actors = new int[i];
            int j = 0;
            foreach (ListItem item in lbActors.Items)
            {
                if (item.Selected == true)
                {
                    actors[j] = Convert.ToInt32(item.Value);
                    j++;
                }
            }
            DataTable dt = new DataTable("movieAndActorType");
            dt.Columns.Add("actorId",typeof(Int32));
            int k=0;
            while(actors[k]!=0)
            {
                dt.Rows.Add(actors[k]);
                k++;
            }
            try
            {
                sc.CommandText = "usp_AddMovie";
                sc.Connection = sqlCon;
                sc.CommandType = CommandType.StoredProcedure;
                sc.Parameters.Add("@producerId", SqlDbType.Int).Value = Convert.ToInt32(ddlProducer.SelectedValue);
               sc.Parameters.Add("@actorIds", SqlDbType.Structured).Value = dt;
                sc.Parameters.Add("@movieName", SqlDbType.VarChar).Value = txtName.Text;
                sc.Parameters.Add("@yearOfRelease", SqlDbType.Int).Value = Convert.ToInt32(txtReleaseYear.Text);
                sc.Parameters.Add("@plot", SqlDbType.VarChar).Value = txtPlot.Text;
                sc.Parameters.Add("@image", SqlDbType.Image).Value = imgbyte;
                sqlCon.Open();
                sc.ExecuteNonQuery();
                sqlCon.Close();
            }
            catch(SqlException ex)
            {
                Console.WriteLine(" SQL error" + ex.ToString());
            }

在"sql server"中，我试图将数据输入下面的两个表

create table tbl_movie
(
movieId int primary key IDENTITY(1,1),
producerId int foreign key references tbl_producer(producerId),
movieName varchar(30) not null unique,
yearOfRelease int not null check(yearOfRelease>1900 and yearOfRelease<=datepart(yyyy,getDate())),
plot varchar(5000) not null,
poster image 
);

   create table tbl_movieAndActor                 (
movieId int foreign key references tbl_movie(movieId),
actorId int foreign key references tbl_actor(actorId),
constraint pk_movieAndActor primary key (movieId,actorId)
);

创建用户定义的类型，将数据从应用程序传递到过程

     create type movieAndActorType As Table
 (
  actorId int not null
 )

和存储过程

create proc proc_AddMovie(@producerId int,@actorIds As movieAndActorType Readonly,@movieName varchar(50),@yearOfRelease int,@plot varchar(500),@image image)
 as
 begin
 insert into tbl_movie(producerId,movieName,yearOfRelease,plot,poster) values(@producerId,@movieName,@yearOfRelease,@plot,@image)
 insert into tbl_movieAndActor(movieId,actorId) select scope_identity(),actorId from @actorIds
 end

我在sql server中测试了存储过程，它在那里工作得很好，但当我在应用程序中传递值时，它会引发sql异常。

     "Operand type clash: table type is incompatible with int"

我在编码方面很天真，请帮我解决这个问题。

感谢

操作数类型冲突:表类型与int不兼容

我终于找到了错误的原因，我在代码中使用了不正确的过程名称。过程名称是"Proc_addMovie"，但我使用的是"usp_AddMpvie"。在浪费了很多时间后，我终于注意到了这一点。

您需要先修复C#代码并确保它能正常工作。你的while循环永远不会起作用。以下是填充表格的方法：

            DataTable dt = new DataTable("movieAndActorType");
            dt.Columns.Add("actorId", typeof(Int32));               
            for (int i = 0; i < actors.Length; i++)
            {
                var newRow = dt.NewRow();
                newRow["actorId"] = actors[i];
                dt.Rows.Add(newRow);
            }

请尝试将其插入tbl_MovieAndActor表：插入tbl_movieAndActor（movieId，actorId）SELECT movieId=@moveId，来自@actorIds 的actorId

这一切都可以在这里使用C#代码和存储过程：

     int[] actors = { 7, 8, 9 }; // Actor IDs in database                       
        DataTable dt = new DataTable("movieAndActorType");
        dt.Columns.Add("actorId", typeof(Int32));
        for(int i = 0;i <actors.Length; i++)
        {
            var dRow = dt.NewRow();
            dRow["actorId"] = actors[i];
            dt.Rows.Add(actors[i]);                
        }
        var img = new Byte[5];
        try
        {
            using (var conn = new SqlConnection("Your connection string"))
            {
                using (var cmd = new SqlCommand())
                {
                    cmd.CommandText = "usp_AddMovie";
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@producerId", SqlDbType.Int).Value =   
                    Convert.ToInt32("99");
                    cmd.Parameters.Add(new SqlParameter { ParameterName = 
                   "@actorIds", SqlDbType = SqlDbType.Structured, Value = dt, 
                    TypeName = "dbo.movieAndActorType" });
                    cmd.Parameters.Add("@movieName", SqlDbType.VarChar).Value 
                    = "Spectre";
                    cmd.Parameters.Add("@yearOfRelease", SqlDbType.Int).Value 
                    = Convert.ToInt32("1972");
                    cmd.Parameters.Add("@plot", SqlDbType.VarChar).Value = 
                    "Some Plot";
                    cmd.Parameters.Add("@image", SqlDbType.Image).Value =                                                             
                    img;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine(" SQL error" + ex.Message);
        }
相关文章：