Hi I have an error while uploading file and then importing it into my sql table
The process cannot access the file 'c:\inetpub\www root\FlowLine\E xcel File\Fan_Detail s.xls' because it is being used by another process
Now Some times its run successfully, i don't no how but somtimes it gives error,
I figure out there is security issue of the folder where its storing the file,
But i had given all right to user there
code is
string fullPath;
string Path;
public void fetch_file_path ()
{
path = fup_path.Posted File.FileName;
path = System.IO.Path. GetFileName(pat h);
fullPath = Server.MapPath( "Excel File" + "\\" + path);
if (fup_path.Poste dFile.FileName != "")
{
fup_path.Posted File.SaveAs(ful lPath);
}
}
//**********Impor ts Xls file in Sql table t_fan_details** *************** ***********
public void import_xlsFile( )
{
fetch_file_path ();
try
{
utility dut_count = new utility();
int table_count;
string sqlStr = "select count(*) from t_fan_details ";
table_count = dut_count.iscou nt(sqlStr);
// Create Connection to Excel Workbook
string strConnection = @"provider=Micr osoft.Jet.OLEDB .4.0;data source=" + fullPath + "; Extended Properties=Exce l 8.0;";
OleDbConnection connection = new OleDbConnection (strConnection) ;
if (connection.Sta te == ConnectionState .Closed)
{
connection.Open ();
}
OleDbCommand command = new OleDbCommand("s elect f_Volume,f_Tota l_Pressure,f_St atic_Pressure,f _outlet_type,f_ Density,f_power _correction,f_d uty_point_toler ance,f_Total_Ef feciency,f_Stat ic_Effeciency,f _rpm,f_Fan_Powe r,f_Motor_Power ,f_Shaft_Power, f_Velocity,f_du ty_point FROM [Data$]", connection);
// Create DbDataReader to Data Worksheet
OleDbDataReader dr = command.Execute Reader();
int count=0;
while (dr.Read())
{
count += 1;
}
// SQL Server Connection String
Source=localhos t;database=demo ;user id=sa;pwd=sa";
// Bulk Copy to SQL Server
SqlBulkCopy bulkcopy = new SqlBulkCopy(Con figurationManag er.AppSettings["connectionstri ng"]);
bulkcopy.Destin ationTableName = "t_fan_details" ;
// bulkCopy.WriteT oServer(dr);
bulkcopy.WriteT oServer(dr);
bulkcopy.Close( );
if (!dr.IsClosed)
dr.Close();
int selected_record =table_count + 1;
sqlStr = "select max(f_sno) from t_fan_details where f_sno in (select top " + selected_record + " f_sno from t_fan_details order by f_sno desc)";
utility dut_max_fsno = new utility();
int fsno = dut_max_fsno.is count(sqlStr);
Int64 f_fantype_sno, f_model;
f_fantype_sno=C onvert.ToInt64( Request.QuerySt ring["id"]);
f_model = Convert.ToInt64 (Request.QueryS tring["model_sno"]);
int i = 0;
for (i = 0; i < count; i++)
{
sqlStr = "update t_fan_details set f_fantype_sno=" + f_fantype_sno + ", f_model_sno=" + f_model + " where f_Sno="+fsno+" ";
utility dut_fandetails = new utility();
dut_fandetails. executeSQL(sqlS tr);
fsno = fsno + 1;
}
Response.Write( "Data Imported From Xls file");
if (connection.Sta te == ConnectionState .Open)
connection.Clos e();
}
catch (Exception ex)
{
Response.Write( ex.Message);
}
}
protected void btn_upload_Clic k(object sender, EventArgs e)
{
import_xlsFile( );
}