#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Asp.net SQL BULK copy error


    I'm using upload function , it was working fine now i got error while uploading saying

    " The given ColumnMapping does not match up with any column in the source or destination. "

    below is my code

    protected void Btn_SNDATA_Up_Click(object sender, EventArgs e)
    {
    //upload(FU_SNData, "Select '" + Drp_Region.SelectedValue + "','" + date + "', * from [SN Data$]", "DO_INBOUND_SN_DATA");

    string connectionString = "";
    lblResults.Text = "";
    if (!Page.IsValid)
    return;

    //Make sure the demo Excel file was uploaded
    if (Path.GetExtension(FU_SNData.FileName).ToLower() != ".xls")
    {
    lblResults.ForeColor = Color.Red;
    lblResults.Text = "System Allows only Excel sheets upload with required data format";
    return;
    }

    try
    {
    string uploadFileName = Server.MapPath("~/Uploads/" + Path.GetFileName(FU_SNData.FileName));
    FU_SNData.SaveAs(uploadFileName);
    if (Path.GetExtension(FU_SNData.FileName).ToLower() == ".xls")
    {
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + uploadFileName + ";" + "Extended Properties=Excel 8.0;";
    }
    if (Path.GetExtension(FU_SNData.FileName).ToLower() == ".xlsx")
    {
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + uploadFileName + ";" + "Extended Properties=Excel 12.0 Xml;HDR=YES;";
    }
    DataTable excelData = new DataTable();
    using (OleDbConnection myConnection = new OleDbConnection(connectionString))
    {
    //Get all data from the InventoryData worksheet
    OleDbCommand myCommand = new OleDbCommand();
    //myCommand.CommandText = "Select '" + Drp_Region.SelectedValue + "','" + date + "', * FROM [Sheet1$]";
    myCommand.CommandText = "Select * FROM [Amount$]";
    myCommand.Connection = myConnection;

    //Read data into DataTable
    OleDbDataAdapter myAdapter = new OleDbDataAdapter();
    myAdapter.SelectCommand = myCommand;
    myAdapter.Fill(excelData);

    myConnection.Close();
    }
    //lblResults.Text = string.Format("STARTING ROW COUNT: {0:N0}<br />", TCONTableRowCount());
    //Start timing!
    Stopwatch sw = Stopwatch.StartNew();
    InsertViaSqlBulkCopyWithoutTransaction(excelData);
    lblResults.Text += string.Format("<br />data inserted and Operation took {0} milliseconds...", sw.ElapsedMilliseconds);

    //Output ending rowcount
    //lblResults.Text += string.Format("<br />ENDING ROW COUNT: {0:N0}<br />";


    //Finally, delete the uploaded file
    File.Delete(uploadFileName);
    }
    catch (Exception ex)
    {
    // lblResults.Text = "An error has happened during upload your file, make sure your file is with the required format and try agin ";
    lblResults.Text = ex.Message.ToString();

    }
    }
    private void InsertViaSqlBulkCopyWithoutTransaction(DataTable excelData)
    {
    {
    con.Open();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
    {
    bulkCopy.DestinationTableName = "tmp_OUTBOUND_SR_AMOUNT";
    // SqlBulkCopyOptions.KeepIdentity;

    //You can optionally specify the batch size... by default, all records are sent to the database in one batch
    //bulkCopy.BatchSize = 100

    //Define column mappings
    foreach (DataColumn col in excelData.Columns)
    {
    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    }


    bulkCopy.WriteToServer(excelData);
    }

    con.Close();
    }
    }
    public int TCONTableRowCount()
    {
    int rowCount = 0;

    //using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrin gs["InventoryDBConnectionString"].ConnectionString))
    {
    con.Open();

    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText = "SELECT COUNT(*) FROM tmp_OUTBOUND_SR_AMOUNT";
    myCommand.Connection = con;

    rowCount = Convert.ToInt32(myCommand.ExecuteScalar());

    con.Close();
    }

    return rowCount;
    }


    I used below select command

    " select [Delivery No],[Item No],[Customer Code],[Material],[Val.Type] as [ValType],[Div.] as [Div],[Description],[Qty],[Unit],[Price],[Amount],[Currency],[Available Stock],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Customer PO],[PO Item] from [Sheet1$ "

    but gives error saying Invalid bracketing of name '[Val.Type]'.
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Aug 2008
    Location
    USA
    Posts
    1,584
    Rep Power
    343
    Originally Posted by hardtoget410
    I'm using upload function , it was working fine now i got error while uploading saying

    " The given ColumnMapping does not match up with any column in the source or destination. "

    below is my code

    Code:
    protected void Btn_SNDATA_Up_Click(object sender, EventArgs e)
        {
            //upload(FU_SNData, "Select '" + Drp_Region.SelectedValue + "','" + date + "', * from  [SN Data$]", "DO_INBOUND_SN_DATA");
    
            string connectionString = "";
            lblResults.Text = "";
            if (!Page.IsValid)
                return;
    
            //Make sure the demo Excel file was uploaded
            if (Path.GetExtension(FU_SNData.FileName).ToLower() != ".xls")
            {
                lblResults.ForeColor = Color.Red;
                lblResults.Text = "System Allows only Excel sheets upload with required data format";
                return;
            }
    
            try
            {
                string uploadFileName = Server.MapPath("~/Uploads/" + Path.GetFileName(FU_SNData.FileName));
                FU_SNData.SaveAs(uploadFileName);
                if (Path.GetExtension(FU_SNData.FileName).ToLower() == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + uploadFileName + ";" + "Extended Properties=Excel 8.0;";
                }
                if (Path.GetExtension(FU_SNData.FileName).ToLower() == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + uploadFileName + ";" + "Extended Properties=Excel 12.0 Xml;HDR=YES;";
                }
                DataTable excelData = new DataTable();
                using (OleDbConnection myConnection = new OleDbConnection(connectionString))
                {
                    //Get all data from the InventoryData worksheet
                    OleDbCommand myCommand = new OleDbCommand();
                    //myCommand.CommandText = "Select '" + Drp_Region.SelectedValue + "','" + date + "', * FROM [Sheet1$]";
                    myCommand.CommandText = "Select  * FROM [Amount$]";
                    myCommand.Connection = myConnection;
    
                    //Read data into DataTable
                    OleDbDataAdapter myAdapter = new OleDbDataAdapter();
                    myAdapter.SelectCommand = myCommand;
                    myAdapter.Fill(excelData);
    
                    myConnection.Close();
                }
                //lblResults.Text = string.Format("STARTING ROW COUNT: {0:N0}<br />", TCONTableRowCount());
                //Start timing!
                Stopwatch sw = Stopwatch.StartNew();
                InsertViaSqlBulkCopyWithoutTransaction(excelData);
                lblResults.Text += string.Format("<br />data inserted and Operation took {0} milliseconds...", sw.ElapsedMilliseconds);
    
                //Output ending rowcount
                //lblResults.Text += string.Format("<br />ENDING ROW COUNT: {0:N0}<br />";
    
    
                //Finally, delete the uploaded file
                File.Delete(uploadFileName);
            }
            catch (Exception ex)
            {
                // lblResults.Text = "An error has happened during upload your file, make sure your file is with the required format and try agin ";
                lblResults.Text = ex.Message.ToString();
    
            }
        }
     private void InsertViaSqlBulkCopyWithoutTransaction(DataTable excelData)
        {
            {
                con.Open();
    
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
                {
                    bulkCopy.DestinationTableName = "tmp_OUTBOUND_SR_AMOUNT";
                   // SqlBulkCopyOptions.KeepIdentity;
    
                    //You can optionally specify the batch size... by default, all records are sent to the database in one batch
                    //bulkCopy.BatchSize = 100
    
                    //Define column mappings
                    foreach (DataColumn col in excelData.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
    
    
                    bulkCopy.WriteToServer(excelData);
                }
    
                con.Close();
            }
        }
        public int TCONTableRowCount()
        {
            int rowCount = 0;
    
            //using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["InventoryDBConnectionString"].ConnectionString))
            {
                con.Open();
    
                SqlCommand myCommand = new SqlCommand();
                myCommand.CommandText = "SELECT COUNT(*) FROM tmp_OUTBOUND_SR_AMOUNT";
                myCommand.Connection = con;
    
                rowCount = Convert.ToInt32(myCommand.ExecuteScalar());
    
                con.Close();
            }
    
            return rowCount;
        }
    I used below select command

    " select [Delivery No],[Item No],[Customer Code],[Material],[Val.Type] as [ValType],[Div.] as [Div],[Description],[Qty],[Unit],[Price],[Amount],[Currency],[Available Stock],[Bin 1],[Bin 2],[Bin 3],[Bin 4],[Bin 5],[Customer PO],[PO Item] from [Sheet1$ "

    but gives error saying Invalid bracketing of name '[Val.Type]'.

    Hi,

    try using [code ] tag to add the code.
    as the error state that , your excel columns and table (db) columns are different, before uploading the doc
    please make sure,
    1.no of columns in excel shold be present in table(db) if your uploading entire excel (need to mapped).

    2. just recheck this, if you have TimeStamp columns in your table? Or identity columns? If you have identity columns, you need to specify SqlBulkCopyOptions.KeepIdentity
    Last edited by markWilson; September 11th, 2012 at 08:16 AM.
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

    Jquery Shake Effect ,
    Andriod KitKat


    Thank You,
    KiranK

Similar Threads

  1. 2 '80004005' errors
    By desiboy681 in forum Microsoft Access Help
    Replies: 2
    Last Post: November 15th, 2006, 11:27 AM
  2. Object required: '' Error???
    By miniskirt in forum ASP Development
    Replies: 15
    Last Post: July 3rd, 2006, 01:21 AM
  3. Execute SQL
    By erickh in forum ASP Development
    Replies: 8
    Last Post: March 29th, 2006, 03:44 AM
  4. Need help with SQL Querey or Stored Procedure
    By bleutiger in forum SQL Development
    Replies: 1
    Last Post: January 20th, 2005, 06:16 AM
  5. Cannot establish a connection to SQL Server 2K sp3a remotely
    By ngandy in forum Microsoft SQL Server
    Replies: 6
    Last Post: January 19th, 2005, 02:54 AM

IMN logo majestic logo threadwatch logo seochat tools logo