Click here to Skip to main content
15,441,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello friends , I am saving an Excel sheet to my sql server table using asp.net file uplaoder. It is throwing this error " System.Data.OleDb.OleDbException: '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long .".
Please help me. Thanks in advance in friends.


This is my code.

C#
protected void BtnSbmit_Click(object sender, EventArgs e)
    {

        string _Path = "";
        string _Ext = "";
        string _FileName = "";


        bool _Uploaded = false;
        if (UploadExcel.HasFile)
        {
            _Path = Server.MapPath("~/App_Data");
            _Ext = System.IO.Path.GetExtension(UploadExcel.FileName).ToLower();
            _FileName = UploadExcel.FileName;
            _Path = _Path + "\\" + _FileName;
            UploadExcel.SaveAs(_Path);
            _Uploaded = true;
        }

        if (_Uploaded)
        {
            switch (_Ext)
            {
                case ".xls":
                    string _XlsProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _Path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=0'";
                    UploadSheet(_XlsProvider,_Path );
                    break;
                case ".xlsx":
                    string _XlsxProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _Path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=0'";
                    UploadSheet(_XlsxProvider, _Path);
                    break;
            }
        }
    }
    //Method responsible for bulk uploading data onto the sql server.
    private void UploadSheet(string _Provider,string spath)
    {
        string _Query = "select a,b,c,d,e,f,g,h,i,j,k,l,m,n.o from ["+spath+"]";//Query to select Name from the Excel sheet(Sheet1$)
        using (var myConnection = new OleDbConnection(_Provider))
        using (var destinationConnection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.
ConnectionStrings["superConnection"].ConnectionString))//Database Connection String
        using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.
ConnectionStrings["superConnection"].ConnectionString))//Database Connection String
        {
           
            string table = "orders";
            bulkCopy.DestinationTableName = table;//Destination Table name on the SQL Server.
            using (var myCommand = new OleDbCommand(_Query, myConnection))
            {
                myConnection.Open();
                destinationConnection.Open();
                var myReader = myCommand.ExecuteReader();
                bulkCopy.WriteToServer(myReader);
            }
        }
    }
Posted
Updated 3-Oct-11 1:20am
v5
Comments
CodingLover 3-Oct-11 5:47am    
Can you please post the complete stack trace you ends with?

1 solution

Hello

I guess SQL won't agree to store characters that it uses as delimiters.. I do not use Microsoft SQL but I do store binary data in a SQLite database and it works fine only for BLOB fields...

MTC

Thierry
 
Share this answer
 

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