Import excel into database and show results in case of some error

This is web form code





























This is code behind code....
public partial class UploadEmployee : System.Web.UI.Page
{
#region Object and Variable Declaration
Cls_UtilityPrograms clsObj = new Cls_UtilityPrograms();
Cls_EIS_Business_Update clsbupd = new Cls_EIS_Business_Update();
FileUpload FlUpd;
DataTable dt_exception;
DataColumn dc;
string str;
string filename,msg;
#endregion
protected void Page_Load(object sender, EventArgs e)
{
((PMSMasterInner)Page.Master).RegisterPostbackTrigger(BtnOpen);
if (!Page.IsPostBack)
{
}
LblMsg.Text = "";
}
protected void BtnOpen_Click(object sender, EventArgs e)
{
msg = "";
FlUpd = (FileUpload)Master.FindControl("ContentSection1").FindControl("Upload");
if (FlUpd.HasFile == true)
{
str = FlUpd.PostedFile.FileName.ToString();
filename = FlUpd.FileName.ToString();
dt_exception = new DataTable();
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = "Employee Code";
dt_exception.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = "Error";
dt_exception.Columns.Add(dc);
clsbupd.UploadData(str, filename, Session[Cls_Constant.CONST_EMPLOYEE_CODE].ToString(), ref msg, ref dt_exception);
LblMsg.Text = msg;
if (dt_exception.Rows.Count > 0)
{
GdErrors.DataSource = dt_exception;
GdErrors.DataBind();
}

}
else
{
LblMsg.Text = "Please upload Template";
}
}
}

This is code for uploading data in database ..this method is calling on above code to insert the data...


public void UploadData(string str, string filename, string session_ecode, ref string msg, ref DataTable dt_exception)
{
dt = new DataTable();
string name = System.IO.Path.GetExtension(str);
table_ref = GetTableReference(filename);
if (table_ref == 0)
{
msg = "Invalid File";
return;
}
columnName = "";
columnValue = "";
if (name == Cls_Const.CONS_EXCELFILE1 name == Cls_Const.CONS_EXCELFILE2)
{
dt = clsup.GetTableExcel(str);
count_column = dt.Columns.Count;
count_row = dt.Rows.Count;
if (count_row == 0)
{
msg = "No Data in excel file";
return;
}
if (table_ref != -1)
{
for (int i = 0; i < columnname ="=" columnname =" dt.Columns[i].ToString();" columnname =" columnName" i =" 0;" empcode =" dt.Rows[i][0].ToString();" j =" 0;" columnvalue ="=" columnvalue =" Cls_Const.CONS_NULLSTRING;" columnvalue = "'" columnvalue =" columnValue" columnvalue =" columnValue" table_ref ="=" dt1 =" new" dt1 =" clsup.GetEmpCode(empcode);"> 0)
{
ecodee = dt1.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecodee = "";
}
if (empcode == ecodee)
{
ecodee = "'" + empcode + "'";
clsup.InsertData(table_ref, columnName, columnValue, ecodee, Cls_Const.CONS_EMPCODE);
}
else
{
clsup.InsertData(table_ref, columnName, columnValue, Cls_Const.CONS_NULLSTRING, Cls_Const.CONS_NULLSTRING);
try
{ //Login Creation
ColumnValue = "'" + empcode.Trim() + "','" + empcode.Trim() + "','" + obj.base64Encode(Cls_Const.CONS_PWD).ToString() + "','" + DateTime.Now.ToString() + "','" + session_ecode + "'";
clsup.InsertData(48, Cls_Const.CONS_COLUMN_NAME, ColumnValue, Cls_Const.CONS_NULL, Cls_Const.CONS_NULL);
}
catch (Exception)
{
msg = Cls_Const.CONS_LOGIN_ERRORMSG;
}
}
}
else if (table_ref == -1)
{
int Bvid, Cmpid, desgid, subunitid, deptid, processid;
for (int j = 0; j < empcode =" dt.Rows[j][0].ToString();" bvid =" Convert.ToInt32(dt.Rows[j][1].ToString());" cmpid =" Convert.ToInt32(dt.Rows[j][2].ToString());" desgid =" Convert.ToInt32(dt.Rows[j][6].ToString());" subunitid =" Convert.ToInt32(dt.Rows[j][3].ToString());" processid =" Convert.ToInt32(dt.Rows[j][5].ToString());" deptid =" Convert.ToInt32(dt.Rows[j][4].ToString());" ec = "'" dt1 =" new" dt1 =" clsup.GetEmpCode(empcode);"> 0)
{
ecodee = dt1.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecodee = "";
}
if (empcode == ecodee)
{
DataTable dt2 = new DataTable();
dt2=clsDalUtilPrg.GetDataTable_DAL(table_ref, 0, empcode, null, Cls_Const.CONS_EMPCODE);
string ecode1;
if (dt2.Rows.Count > 0)
{
ecode1 = dt2.Rows[0][Cls_Const.CONS_EMPCODE].ToString();
}
else
{
ecode1 = "";
}
if (empcode == ecode1)
{
ecodee = "'" + empcode + "'";
clsup.InsertData(table_ref, columnName, columnValue, ecodee, Cls_Const.CONS_EMPCODE);
}
else
{
clsup.InsertData(table_ref, columnName, columnValue, Cls_Const.CONS_NULLSTRING, Cls_Const.CONS_NULLSTRING);
}
}
}
}
catch (Exception ex)
{
dr = dt_exception.NewRow();
dr["Employee Code"] = empcode;
//dr["Employee Name"]=dt.Rows[i][2].ToString();
if (ex.Message.ToString() == "Conversion failed when converting the varchar value 'NULL' to data type int.")
error = "Invalid data in excel file";
else if (ex.Message.ToString() == "There is no row at position 0.")
error = "Employee does not exist";
else
error = ex.Message.ToString();
dr["Error"] = error;
dt_exception.Rows.Add(dr);
}
finally
{
columnValue = "";
}
}
if (dt_exception.Rows.Count == 0)
{
msg = "Data uploaded successfully";
}
else
{
msg = "Data uploaded excluding following rows";
}
}
else
{
msg = "Please upload excel files only";
}
}

protected int GetTableReference(string excelfilename)
{
switch (excelfilename)
{
case "Primary_Information.xls": table_ref = 5; break;
case "Primary_Information.xlsx": table_ref = 5; break;
case "Employee_Information.xls": table_ref = -1; break;
case "Employee_Information.xlsx": table_ref = -1; break;
case "Qualification.xls": table_ref = 41; break;
case "Qualification.xlsx": table_ref = 41; break;
case "Bank_Detail.xls": table_ref = 11; break;
case "Bank_Detail.xlsx": table_ref = 11; break;
case "Experience.xls": table_ref = 42; break;
case "Experience.xlsx": table_ref = 42; break;
case "Assets.xls": table_ref = 43; break;
case "Assets.xlsx": table_ref = 43; break;
default: table_ref = 0; break;
}
return table_ref;
}

Delete Duplicate Rows From Table

--Create Table
create table #temptable
(
ID int,
col1 int,
col2 int,
col3 int
)
--Insert values into temp table
insert into #tempTable values (1,1,2,3)
insert into #tempTable values (1,1,2,3)
insert into #tempTable values (1,1,2,3)
insert into #tempTable values (1,1,2,3)
select * from #tempTable
--proc to delete duplicate rows
create PROCEDURE [dbo].[REMOVE_DUP]
AS
BEGIN
DECLARE @ROW_CNT INT
SET ROWCOUNT 0
SELECT @ROW_CNT=1
WHILE @ROW_CNT>0
BEGIN
SELECT ID FROM #tempTable GROUP BY ID HAVING COUNT(*)>1
SELECT @ROW_CNT=@@ROWCOUNT
SET ROWCOUNT 1
DELETE FROM #tempTable WHERE ID IN(
SELECT id FROM #tempTable GROUP BY ID HAVING COUNT(*)>1
SET ROWCOUNT 0
END
SET ROWCOUNT 0
END
--Execute stored procedure
exec [REMOVE_DUP]

Exporting GridView to Excel

In this code snippet, I will show how you can export data from a GridView control to a Microsoft Excel spreadsheet.
The Code
Listing 1: Default.aspx

Listing 2: Default.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}

private void BindData()
{
string query = "SELECT * FROM Categories";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
DataSet ds = new DataSet();
ad.Fill(ds, "Categories");
GridView1.DataSource = ds;
GridView1.DataBind();
}

private string ConnectionString
{
get { return @"Server=localhost;Database=NorthWind;Trusted_Connection=true"; }

}
protected void BtnExport_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";

// If you want the option to open the Excel file without saving then
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

In the above listing, the GridView control will be populated with the data from the Categories table of the Northwind database. You will have to give the appropriate Server name or IP instead of localhost as server name in the above connection string.
If you use this code and try to export the GridView control, you will see an error message saying that the GridView control must be placed inside the form tags with the runat = server attribute.
This is pretty confusing, since your GridView is already inside the form tags and also contains the runat = server attribute. You can easily resolve this error by adding the following lines.
Listing 3: Overiding VerifyRenderingInServerForm Method
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
Yup, that’s it. Now, when you click the button, the GridView control will be exported correctly. It will prompt you either to open the file as it is or to save it elsewhere.

Updating table using row_number() over (order by columnName)

--Create temp table
create table #temptable
(
col1 int,
col2 int,
col3 int
)
--insert three values
insert into #tempTable values (1,2,3)
insert into #tempTable values (1,2,3)
insert into #tempTable values (1,2,3)
--update value based on row_number() over (order by columnName)
update temp
set col3=3
from
(
select row_number() over(order by col1) as row_id,* from #tempTable
)temp
where row_id =2
--View Updated Data
select * from #tempTable

Java Script function - Email Validation

Java Script function - Email Validation
function emailValidator(elem, helperMsg)
{
var emailExp = /^[\w\-\.\+]+\@[a-zA-Z0-9\.\-]+\.[a-zA-z0-9]{2,20}$/;
if (elem.value != "")
{
if(elem.value.match(emailExp))
{
return true;
}
else
{
alert(helperMsg);
elem.focus();
elem.select();
return false;
}
}
}

Use of this function: you can call on onblur event of text box
onblur="emailValidator(this,'Invalid official mail id-1.')"

Java Script function to check numbers only..

Java Script function to check numbers only..

function onlyNumbers(evt)
{
var e = event evt; // for trans-browser compatibility
var charCode = e.which e.keyCode;
if (charCode > 31 && (charCode <> 57))
return false;
return true;
}

Call this function on button onkeypress event
onkeypress="return onlyNumbers();"

Problem in uploading excel file

This is my method where I am sending my excel file add...
but in case of con open it is throwing exception "The file is allready open or permission problem"
it is only in the case when I am deplyoing my application in server . in local its working fine.


public DataTable GetTableFromExcel(string FileName)
{
try
{
//str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";User Id=;Password=;Extended Properties=Excel 12.0;HDR=NO;IMEX=1";
//str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + @";Mode=ReadWrite;ReadOnly=false;Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""";
//str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + @";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""";
str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + @";Mode=ReadWrite;Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""";

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (connection = factory.CreateConnection())
{
connection.ConnectionString = str;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "Select * from [Sheet1$]";
connection.Open();
excelda = new OleDbDataAdapter();
excelda.SelectCommand = command;
dt = new DataTable();
excelda.Fill(dt);
}
}

}
catch (Exception)
{
}
finally
{
connection.Close();
}
return dt;
}

Delegate with real life example

coming very soon.....

Displaying Grid View With empty Data Source.

Step1: Check your data source.
Step2: if in data source there is no record then send grid view control with in this method and change the name of grid view wherever it is necessary.

private void BindGridView(GridView GrdQuali)
{
//Check to ensure we have zero rows in our GridView
//and that our GridView has been assigned a DataSource
if (GrdQuali.Rows.Count == 0 && GrdQuali.DataSource != null)
{
//Create ourselves a DataTable to
//hold our "dummy" row
DataTable dt = null;
// need to clone sources otherwise it will be indirectly adding to
// the original source
//Check to see if the DataSource of our grid is a DataTable or DataSet
if (GrdQuali.DataSource is DataSet)
{
dt = ((DataSet)GrdQuali.DataSource).Tables[0].Clone();
}
else if (GrdQuali.DataSource is DataTable)
{
dt = ((DataTable)GrdQuali.DataSource).Clone();
}
//Check to ensure our DataTable object was assigned a value
if (dt == null)
{
return;
}
// now we add our empty row
dt.Rows.Add(dt.NewRow());
//Bind our GridView
GrdQuali.DataSource = dt;
GrdQuali.DataBind();
//hide our "dummy" row
GrdQuali.Rows[0].Visible = false;
GrdQuali.Rows[0].Controls.Clear();
}
}