Hi,
i am trying to import data from excel into sql server table.In local i can able to insert more than 7585 rows but when i deploy my web application to server side i can import only 7584 rows when i try to insert more than 7584 rows i get the following error.
please help me to fix this.
this is the code i use
if (FileUpload1.HasFile)
{
string _strFileType = "", _strNewFileName = "", _strOLEDBConnection = "";
var fileName = Path.GetFileName(FileUpload1.FileName);
_strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
int fileExtPos = fileName.LastIndexOf(".");
string newfilename = fileName.Substring(0, fileExtPos);
_strNewFileName = newfilename + "___" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss-ffff") + _strFileType;
string _strPath = Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName);
FileUpload1.SaveAs(_strPath);
string ListName = txtmpListName.Text;
string originalfilename = fileName;
string _strdate = DateTime.Now.ToString("yyyy-MM-dd");
string _strSQL = "";
+ ";Extended Properties=Excel 12.0;");
if (_strFileType.Trim() == ".xls")
{
_strOLEDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +
";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (_strFileType.Trim() == ".xlsx")
{
_strOLEDBConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), _strNewFileName) +
";Extended Properties=\"Excel 12.0;HDR=Yes\"";
}
var adapterbvandrev = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", _strOLEDBConnection);
var dsbvandrev = new DataSet();
adapterbvandrev.Fill(dsbvandrev);
}
Error:
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, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Elector.Content.ImportVoterList.btnSubmit_Click(Object sender, EventArgs e)