Exception import Excel data to SQL Server

Question

I've have an page where the user uploads an Excel document. The document contain member information, and after submitted the files content should replace the members data contained in the SQL Server table.

How ever it's like after I added the fileUploader the codes causes an exception.

if (fileUploader.HasFile)
{
     string contentType = fileUploader.PostedFile.ContentType;
     // Excel 2007 || Excel 2003
     if (contentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") || contentType.Equals("application/vnd.ms-excel"))
     {
        hidePanels();
        Members members = new Members();
        if (members.getAllNotExportedMembers().Count > 0)
        {
           //error message
        }
        else
        {
           bool backupSucceed =
                        members.backUpDatabase(Server.MapPath("~/backup-db") + @"\db-backup-" +
                                               DateTime.Now.ToString().Replace("/", "-").Replace(":", "-") + ".bak");
           if (backupSucceed)
           {
               bool fileUploaded = Helper.saveFile(fileUploader.FileBytes,
                                                            Server.MapPath("~/admin/kar/"),
                                                            fileUploader.FileName, 1, User.Identity.Name);
                if (fileUploaded)
                {
                     bool truncateSucceed = members.truncateTable();
                     if (truncateSucceed)
                     {
                        importExcel(Server.MapPath("~/admin/kar/"), "2012.xlsx");
                     }
                }
            }
        }
     }
}

If I comment everything out except:

bool truncateSucceed = members.truncateTable();
if (truncateSucceed)
{
    importExcel(Server.MapPath("~/admin/kar/"), "2012.xlsx");
    showSuccesPanel("Success");
}

It works as it should and doesn't cause an exception.

The exception:

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at admin.admin_uploadMemberList.importExcel(String filePath, String fileName) in uploadMemberList.aspx.cs:line 91

OleDB connectionstring:

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + fileName
+ @";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

How can this be?

By the way, you're missing the "T" in "TypeGuessRows" in your connection string. — Bridge
– Bridge, Commented Aug 2, 2012 at 22:35

KLIM8D · Accepted Answer · 2012-08-02 22:27:50Z

Allright I found what were causing the problem. It was caused by the saveFile method - if I were uploading a file and a file with the same name already existed - the exception was thrown.

public static bool saveFile(this Byte[] fileBytes, string filePath, string fileName, int category, string uploadedBy)
{
    try
    {
        FileStream fileStream = new FileStream(filePath + "/" + fileName, FileMode.Create, FileAccess.ReadWrite);
        fileStream.Write(fileBytes, 0, fileBytes.Length);
        fileStream.Dispose();
        fileStream.Close();
        FileUpload fileUpload = new FileUpload();
        return fileUpload.createFileUpload(fileName, category, filePath, uploadedBy, DateTime.Now);
    }
    catch (Exception ex)
    {
        mLog.logMessage(ex, HttpContext.Current.Request.Url.ToString(), 1);
        return false;
    }
}

It just overwrites the file as it should - but maybe someone can explain why I'm getting the exception then.

Collectives™ on Stack Overflow

Exception import Excel data to SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related