Click here to Skip to main content
15,891,828 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
           }

       }
Posted

1 solution

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
SQL
insert into MyTable (named, columns, oftable)
(select first, second, third from <mydatasource>)</mydatasource></table>


and as you see the destination table structure is known
 
Share this answer
 
v2
Comments
[no name] 22-Mar-14 0:16am    
Hi
I have clearly mentioned that i'm passing this to Stored procedure from there only i'm inserting into table.Since i need to insert values from excel i'm using stored procedure.

My above things are working fine.what i need is in my table i have 19 fields where as in excel i have values for 17 fields.....remaining two fields want to pass manually.

So please help me to do this
Thomas Nielsen - getCore 2-Apr-14 7:47am    
So you need the general construct from above, and make the two manually passed parameters variables and you should be good to go

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900