Where Condition gives error while searching in excel [Resolved]

Posted by perfectchourasia-9163 under ASP.NET on 3/23/2012 | Points: 10 | Views : 1434 | Status : [Member] | Replies : 1
public static string OledbConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
string location = Server.MapPath("~/Excel_file/") + ConfigurationManager.AppSettings["filename"].ToString().Trim();
GridView1.DataSource = GetExcelData(location);
GridView1.DataBind();
}
public DataTable GetExcelData(string location)
{

OledbConnectionString = string.Empty;
string ext = Path.GetExtension(ConfigurationManager.AppSettings["filename"].ToString().Trim());
if(ext==".xls")
{
OledbConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+location+";Extended Properties=Excel 8.0;";
}
else if(ext==".xlsx")
{
OledbConnectionString = "Microsoft.Jet.OLEDB.12.0;Data Source=" + location + ";Extended Properties=Excel 12.0;";
}
else
{
}
OleDbConnection objConn = null;
objConn = new OleDbConnection(OledbConnectionString);
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1$] where Orderid=1", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset, "excel");
objConn.Close();
return objDataset.Tables[0];
}

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/



Responses

Posted by: perfectchourasia-9163 on: 4/3/2012 [Member] Starter | Points: 25

Up
0
Down

Resolved
public DataTable GetExcelData(string location)
{
OledbConnectionString = string.Empty;
string ext = Path.GetExtension(ConfigurationManager.AppSettings["filename"].ToString().Trim());
if (ext == ".xls")
{
OledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + location + ";Extended Properties=Excel 8.0;";
}
else if (ext == ".xlsx")
{
OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + location + ";Extended Properties=Excel 12.0;";
}
else
{ }
OleDbConnection objConn = null;
objConn = new OleDbConnection(OledbConnectionString);
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}

string query = "Select * From [Order Sheet$] Where rtrim(ltrim([Order No#]))=" + txtorderid.Text.ToString().Trim() + " and rtrim(ltrim([Email Id]))='" + txtemail.Text.Replace("'", " ").Replace("-", " ").ToString().Trim() + "' and rtrim(ltrim([Delivery Name]))='" + txtCname.Text.Replace("'", " ").Replace("-", " ").ToString().Trim() + "'";
OleDbCommand objCmdSelect = new OleDbCommand(query, objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);
box.Visible = true;

try
{
if (objDataset.Tables[0] != null)
{
if (objDataset.Tables[0].Rows.Count == 0)
{
viewdetail.Visible = false;
GridView1.DataSource = objDataset.Tables[0];
GridView1.DataBind();
txtCname.Text = "";
txtemail.Text = "";
txtorderid.Text = "";
}
else
{
viewdetail.Visible = true;
GridView1.DataSource = objDataset.Tables[0];
GridView1.DataBind();
txtCname.Text = "";
txtemail.Text = "";
txtorderid.Text = "";
}

}
else
{
viewdetail.Visible = false;
}
}
catch (Exception ex)
{

}
finally
{
objCmdSelect.Dispose();
objConn.Close();
objConn.Dispose();

}
// objConn.Close();
return objDataset.Tables[0];

}

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

perfectchourasia-9163, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response