插入错误：列名或提供的值数与表定义不匹配。 [英] Insert Error: Column name or number of supplied values does not match table definition.

问题描述

我收到以下错误：
 
 插入错误：列名或提供的值数与表定义不匹配。 
 
 
 excel中的总列数= 17 
表中的总字段= 17 + 3 = 20 
 3个fiedls中的1个是（primarykeyid-autogenerate）2是（将来自另一个文本框）和3是（默认值1）
 
i不知道如何将文本框值和默认值传递给过程。 
 
请建议我一个方式
 
这是我的存储过程代码

 USE [演示] 
 GO 
 / ******对象：StoredProcedure [dbo]。[spx_ImportFromExcel07]脚本日期：03/21/2014 18:25:49 * ***** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo]。[spx_ImportFromExcel07] 
 @SheetName varchar（20），
 @FilePath varchar（300），
 @HDR varchar（3），
 @TableName varchar（50）
 AS 
 BEGIN 
 DECLARE @SQL nvarchar（1000）
 IF OBJECT_ID（@ TableName，'U'）IS NOT NULL 
 
 SET @SQL ='INSERT INTO'+ @TableName +'SELECT *来自OPENDATASOURCE'
 
 ELSE 
 
 SET @SQL ='SELECT * INTO'+ @TableName +'FROM OPENDATASOURCE'
 
 
 SET @SQL = @SQL +'（''Microsoft.ACE.OLEDB.12.0''，''数据源='
 SET @SQL = @SQL + @FilePath +';扩展属性='''' Excel 12.0 ; HDR ='
 SET @SQL = @SQL + @HDR +'''''''）... ['
 SET @SQL = @SQL + @SheetName +']'
 EXEC sp_executesql @SQL 
 
 END 

这是我的插入代码：

 protected void btnSave_Click（object sender，EventArgs e）
 {
 string FileName = lblFileName.Text; 
 string Extension = Path.GetExtension（FileName）; 
 string FolderPath = Server.MapPath（ConfigurationManager.AppSettings [FolderPath]）; 
 string CommandText =; 
 switch（Extension）
 {
 case.xls：// Excel 97-03 
 CommandText =spx_ImportFromExcel03; 
休息; 
 case.xlsx：// Excel 07 
 CommandText =spx_ImportFromExcel07; 
休息; 
} 
 // insertDB（）; 
 String strConnString = ConfigurationManager.ConnectionStrings [CARGONETConnectionString]。ConnectionString; 
 SqlConnection con = new SqlConnection（strConnString）; 
 SqlCommand cmd = new SqlCommand（）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.CommandText = CommandText; 
 cmd.Parameters.Add（@ SheetName，SqlDbType.VarChar）.Value = ddlSheets.SelectedItem.Text; 
 cmd.Parameters.Add（@ FilePath，SqlDbType.VarChar）.Value = FolderPath + FileName; 
 cmd.Parameters.Add（@ HDR，SqlDbType.VarChar）.Value = rbHDR.Se​​lectedItem.Text; 
 cmd.Parameters.Add（@ TableName，SqlDbType.VarChar）.Value =TB_TransAgentSeaFreightRate; 
 cmd.Connection = con; 
尝试
 {
 con.Open（）; 
 object count = cmd.ExecuteNonQuery（）; 
 lblMessage.ForeColor = System.Drawing.Color.Green; 
 lblMessage.Text = count.ToString（）+记录已插入。; 
} 
 
 catch（例外情况）
 {
 
 lblMessage.ForeColor = System.Drawing.Color.White; 
 lblMessage.Text = ex.Message; 
 
} 
 
最后
 {
 
 con.Close（）; 
 con.Dispose（）; 
 Panel1.Visible = true; 
 //Panel2.Visible = false; 
 dg_AgentSFR.Visible = true; 
} 
 
} 
 

解决方案

你的插入语句看起来不对，也许是什么将解决您的问题将是一个动态表命名？在这种情况下，您可以通过不知道列来逃避

http：// technet.microsoft.com/en-us/library/ms188029.aspx [ ^ ]



否则一般构造是

插入MyTable（名称，列，表）
值（'first'，'second'，'third'）< / table> 

或者

 插入 进入 MyTable（named，columns，oftable）
（选择第一，第二，第三来自< ; mydatasource>）< / mydatasource>< / table> 

如您所见，目的表结构已知

i'm getting following error:

Insert Error: Column name or number of supplied values does not match table definition.


Total column in excel is = 17
Total field in table = 17 + 3 = 20
among 3 fiedls 1 is (primarykeyid-autogenerate) 2 is (will get from another textbox) and 3 is (defaulvalue 1)

i don't know how to pass text box value and default value to procedure.

Please suggest me a way

Here is my stored procedure code

USE [Demo]
GO
/****** Object:  StoredProcedure [dbo].[spx_ImportFromExcel07]    Script Date: 03/21/2014 18:25:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spx_ImportFromExcel07]
   @SheetName varchar(20),
   @FilePath varchar(300),
   @HDR varchar(3),
   @TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
    IF OBJECT_ID (@TableName,'U') IS NOT NULL

      SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

    ELSE

      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

 
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL

END

and here is my insertion code:

protected void btnSave_Click(object sender, EventArgs e)
       {
           string FileName = lblFileName.Text;
           string Extension = Path.GetExtension(FileName);
           string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
           string CommandText = "";
           switch (Extension)
           {
               case ".xls": //Excel 97-03
                   CommandText = "spx_ImportFromExcel03";
                   break;
               case ".xlsx": //Excel 07
                   CommandText = "spx_ImportFromExcel07";
                   break;
           }
           //insertDB();
           String strConnString = ConfigurationManager.ConnectionStrings["CARGONETConnectionString"].ConnectionString;
           SqlConnection con = new SqlConnection(strConnString);
           SqlCommand cmd = new SqlCommand();
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = CommandText;
           cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = ddlSheets.SelectedItem.Text;
           cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName;
           cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = rbHDR.SelectedItem.Text;
           cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = "TB_TransAgentSeaFreightRate";
           cmd.Connection = con;
           try
           {
               con.Open();
               object count = cmd.ExecuteNonQuery();
               lblMessage.ForeColor = System.Drawing.Color.Green;
               lblMessage.Text = count.ToString() + " records inserted.";
           }

           catch (Exception ex)
           {

               lblMessage.ForeColor = System.Drawing.Color.White;
               lblMessage.Text = ex.Message;

           }

           finally
           {

               con.Close();
               con.Dispose();
               Panel1.Visible = true;
               //Panel2.Visible = false;
               dg_AgentSFR.Visible = true;
           }

       }

解决方案

Your insert statement looks wrong, perhaps what will solve your problem would be a dynamic table naming? in this case you can get away with not knowing the columns
http://technet.microsoft.com/en-us/library/ms188029.aspx[^]

otherwise the general construct is

insert into MyTable (named, columns, oftable)
values('first', 'second', 'third')</table>

alternatively

insert into MyTable (named, columns, oftable)
(select first, second, third from <mydatasource>)</mydatasource></table>

and as you see the destination table structure is known

这篇关于插入错误：列名或提供的值数与表定义不匹配。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！