hi,,
The below code is successfully running in local host but when it is upload in server the below red line statment showing error...what the problem and behaviour of datatable at server...
Please needed help......
public partial class CustomerMaster : System.Web.UI.Page
{
string connString = "";
string strFileType;
bool flag;
OleDbConnection con;
string FName;
protected void Page_Load(object sender, EventArgs e)
{
Label1.Visible = false;
GridView1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
//Button1.Attributes.Add("onclick", "document.body.style.cursor = 'wait';");
try
{
strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
//string path = FileUpload1.PostedFile.FileName;
HttpFileCollection h1 = Request.Files;
HttpFileCollection httpfiles = Request.Files;
if (httpfiles.Count > 0)
{
FName = httpfiles[0].FileName;
Session["loc"] = "uploads\\" + FName;
string filecheck = Session["loc"].ToString();
if (File.Exists(Server.MapPath(filecheck)))
{
ClientScript.RegisterStartupScript(this.GetType(), "", "alert('This File already uploaded Successfully.Cannot be uploaded again.');", true);
}
else
{
readExcel(Server.MapPath(@"uploads\\" + FName));
string path = Server.MapPath(@"uploads\\" + FName);
if (flag)
{
using (FileStream fs = File.Create(Server.MapPath(@"uploads\\" + FName)))
{
SaveFile(httpfiles[0].InputStream, fs);
}
con = new OleDbConnection(connString);
con.Open();
/* For dynammic sheet names */
DataTable sheetname = null;
sheetname = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[sheetname.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in sheetname.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
string name = excelSheets[sheetname.Rows.Count - 1];
string query = "SELECT * FROM [" + name + "]";
string query2 = "select * from CustomerMaster";
//OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(query, con);
DataTable dt = new DataTable("");
da.Fill(dt);
DataTable dt2 = dt.Clone();
/** importing rows from dt to dt2**/
for (int r = 14; r < dt.Rows.Count; r++)
{
try
{
dt2.ImportRow(dt.Rows[r]);
}
catch { }
}
/** setting datatable row as column header for datatable dt2**/
foreach (DataColumn column in dt2.Columns)
{
string cName = dt.Rows[13][column.ColumnName].ToString(); ---->showing error in server
if (!dt2.Columns.Contains(cName) && cName != "")
{
column.ColumnName = cName;
}
}
int count = dt2.Columns.Count;
string constring = ConfigurationManager.ConnectionStrings["DB_ProjectionConnectionString"].ConnectionString;
SqlDataAdapter da2 = new SqlDataAdapter(query2, constring);
DataSet ds = new DataSet();
/*Checking column names from datatable to database customer table**/
//int c=0;
//for(int j=0;j<dt2.Columns.Count;j++)
//{
// if (dt2.Columns[j].ColumnName == ds.Tables[0].Columns[j+1].ColumnName)
// {
// c++;
// }
//}
//if (c < 13)
//{
// Response.Write("Columns are mismatched or Required Columns are not available....");
//}
/**comparing columns names with required static columns of excel sheet **/
string[] excelcnames = {"Customer Identifier"," ","Top Node Indicator","Channel","CSR","Channel Head",
"F7","Search Term 2 Text","Customer Deletion Indicator","Cust Acct Asgn Grp","F11","Number of Records","Region",
"Account Manager","M1%","M2%","EmailId","Purchase_Order","Proforma_Invoice",
"PermitRequire","PermitReportFrequencyInMonth","Customer_Category","Address"};
int m = 0;
for (int c = 0; c < excelcnames.Length; c++)
{
if (dt2.Columns[c].ColumnName == excelcnames[c])
{
m++;
}
}
//dt2.Rows.Remove(dt2.Rows[0]);
/** checking M1+M2==100**/
string rule = "success";
for (int c1 = 0; c1 < dt2.Rows.Count; c1++)
{
string m1 = dt2.Rows[c1][14].ToString();
string m2 = dt2.Rows[c1][15].ToString();
if (Convert.ToInt32(m1) + Convert.ToInt32(m2) == 100)
{ }
else
{
rule = "failed";
}
}
if (rule == "success")
{
if (m == 23)
{
SqlBulkCopy sbc = new SqlBulkCopy(constring);
sbc.DestinationTableName = "CustomerMaster";
sbc.ColumnMappings.Add("Channel", "Account_Type");
sbc.ColumnMappings.Add("Channel Head", "ChannelHeadName");
sbc.ColumnMappings.Add("CSR", "CSR");
sbc.ColumnMappings.Add("Customer Identifier", "CustSoldToNumbers");
sbc.ColumnMappings.Add("Search Term 2 Text", "City");
sbc.ColumnMappings.Add("Region", "Region");
sbc.ColumnMappings.Add("Account Manager", "Account Manager");
sbc.ColumnMappings.Add("M1%", "M1%");
sbc.ColumnMappings.Add("M2%", "M2%");
sbc.ColumnMappings.Add("EmailId", "EmailId");
sbc.ColumnMappings.Add("Purchase_Order", "Purchase_Order");
sbc.ColumnMappings.Add("Proforma_Invoice", "Proforma_Invoice");
sbc.ColumnMappings.Add("PermitRequire", "PermitRequire");
sbc.ColumnMappings.Add("PermitReportFrequencyInMonth", "PermitReportFrequencyInMonth");
sbc.ColumnMappings.Add("Customer_Category", "Customer_Category");
sbc.ColumnMappings.Add("Address", "Address");
//sbc.ColumnMappings.Add("Account_Type", "Account_Type");
//sbc.ColumnMappings.Add("ChannelHeadName", "ChannelHeadName");
//sbc.ColumnMappings.Add("CSR", "CSR");
//sbc.ColumnMappings.Add("CustSoldToNumbers", "CustSoldToNumbers");
//sbc.ColumnMappings.Add("EmailId", "EmailId");
//sbc.ColumnMappings.Add("Purchase_Order", "Purchase_Order");
//sbc.ColumnMappings.Add("Proforma_Invoice", "Proforma_Invoice");
//sbc.ColumnMappings.Add("PermitRequire", "PermitRequire");
//sbc.ColumnMappings.Add("PermitReportFrequencyInMonth", "PermitReportFrequencyInMonth");
//sbc.ColumnMappings.Add("Customer_Category", "Customer_Category");
//sbc.ColumnMappings.Add("6040Rule", "6040Rule");
//sbc.ColumnMappings.Add("Address", "Address");
//sbc.ColumnMappings.Add("City", "City");
//sbc.ColumnMappings.Add("Region", "Region");
sbc.WriteToServer(dt2);
Label1.Visible = true;
Label1.Text = "Uploaded Successfully.";
da2.Fill(ds);
GridView1.Visible = true;
GridView1.DataSource = ds;
GridView1.DataBind();
da.Dispose();
con.Close();
con.Dispose();
Button1.Enabled = true;
}
else
{
/** if insertion fails due to columns mis-match **/
da.Dispose();
con.Close();
con.Dispose();
Label1.Visible = true;
System.IO.File.Delete(path);
Label1.Text = "Uploading Failed. Check the Spread Sheet, Columns are mis-matched. Columns should be in Mentioned order only without Empty. Order is<br>Customer Identifier<br/>(No Header Name)<br/>Top Node Indicator<br/>Channel<br/>CSR<br/>Channel Head<br/>(No Header Name)<br/>Search Term 2 Text<br/>Customer Deletion Indicator<br/>Cust Acct Asgn Grp<br/>(No Header Name)<br/>Number of Records<br/>Region<br/>Account Manager<br/>M1%<br/>M2% (No Space Between % and M1,M2)<br/>EmailId<br/>Purchase_Order<br/>Proforma_Invoice<br/>PermitRequire<br/>
PermitReportFrequencyInMonth<br/>Customer_Category<br/>Address";
}
}
else
{
/** if M1,M2 fails **/
con.Close();
con.Dispose();
Label1.Visible = true;
System.IO.File.Delete(path);
Label1.Text = "M1% and M2% doesn't satisfy the rule of 100%. Please check in Spreadsheet and Upload it.";
}
}
else
{
/** uploaded file other than required excel sheet **/
Label1.Visible = true;
Label1.Text = "Please upload valid Excel SpreadSheet.";
}
}
}
}
catch (Exception ex)
{
con.Close();
GC.Collect();
Label1.Visible = true;
Label1.Font.Bold = false;
System.IO.File.Delete(Server.MapPath(@"uploads\\" + FName));
Label1.Text = "Upload Failed. Please Check the File whether it contains all the columns or not. Columns should be in Mentioned order only without Empty. Order is<br>Customer Identifier<br/>(No Header Name)<br/>Top Node Indicator<br/>Channel<br/>CSR<br/>Channel Head<br/>(No Header Name)<br/>Search Term 2 Text<br/>Customer Deletion Indicator<br/>Cust Acct Asgn Grp<br/>(No Header Name)<br/>Number of Records<br/>Region<br/>Account Manager<br/>M1%<br/>M2% (No Space Between % and M1,M2)<br/>EmailId<br/>Purchase_Order<br/>Proforma_Invoice<br/>PermitRequire<br/>PermitReportFrequencyInMonth<br/>Customer_Category<br/>Address";
Label1.Text = ex.Message;
}
finally
{
}
}
private void SaveFile(Stream stream, FileStream fs)
{
byte[] buffer = new byte[4096];
int bytesRead;
while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0)
{
fs.Write(buffer, 0, bytesRead);
}
fs.Flush();
fs.Close();
fs.Dispose();
}
protected void readExcel(string ExcelFile)
{
string path = ExcelFile;
flag = false;
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
flag = true;
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
flag = true;
}
else if (strFileType.Trim() == ".xlsm")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 Macro;HDR=Yes;IMEX=2\"";
flag = true;
}
}
}