Excel -data type mismatch [Resolved]

Posted by perfectchourasia-9163 under ASP.NET on 3/22/2012 | Points: 10 | Views : 1700 | Status : [Member] | Replies : 1
I have to search data from excel .Excel data look like this

Email   ID Name
muralim@carevoyant.com 1 muskan
vvshanthanu@hotmail.com 2 muskan
mohini@mohiniexports.org 3 muskan
vishnu.rao@icicibank.com 4 muskan

I wanna to search select * from [sheet1$] Where ID = '? or Name =? or Email =? but it gives error when i search only Name then it work file


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ordersearch.aspx.cs" Inherits="order_order" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</form>
</body>
</html>


protected void Button1_Click(object sender, EventArgs e)
{
string location = Server.MapPath("~/excel_file/mysore second list.xls");
string oledbconnectionstring = string.Empty;
oledbconnectionstring = "provider=microsoft.jet.oledb.4.0;data source=" + location + ";extended properties=excel 8.0;";
string SqlString = "select * from [sheet1$] Where ID = '?'";
using (OleDbConnection conn = new OleDbConnection(oledbconnectionstring))
{
using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("Name", TextBox1.Text);
cmd.Parameters.AddWithValue("Email", TextBox2.Text);
cmd.Parameters.AddWithValue("ID", TextBox3.Text);

conn.Open();
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Response.Write(reader["ID"].ToString() + "<br/>");
}
}
}
}
}

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