Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Aug 17, 2012 01:28 PM by SheoNarayan
Dec 19, 2011 04:31 PM|LINK
this my code following error will accuring plz help me
private DataTable ReadExcelData(Int32 UserID)
string file = Server.MapPath("UserData.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
string query = "select Empcode,Empname,Joiningdate ,Designation,DOB,Empage,Fathername,Address,Qualification,phoneno,Emailid,phoneno1 from[sheet1$] ";
if (UserID > 0)
query = query + " WHERE UserID=" + UserID.ToString();
DataSet dsUserData = new DataSet();
using (OleDbConnection con = new OleDbConnection(constr))
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, con))
DataAdapter.AcceptChangesDuringFill = false;
Dec 19, 2011 06:53 PM|LINK
it should work ,if connection string is valid, and sheet name and ofcourse excel file is exists, check for them ,and it will work.
Feb 22, 2012 06:30 AM|LINK
When I debugged I found a space in the 'sheet 1$ instead of the assigned sheet name 'sheet1'.
So better try to read the sheetname and use it in the query.
Dim objXConn As New OleDbConnection(xConnStr)
' create your excel connection object using the connection string
'Get the name of the first worksheet:
Dim dbSchema As DataTable = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim firstSheetName As String = dbSchema.Rows(0)("TABLE_NAME").ToString()
Dim objCommand As New OleDbCommand("SELECT * FROM [" + firstSheetName + "]", objXConn)
Hope this helps.
Aug 17, 2012 01:28 PM|LINK
Looks like this post is not marked as answered, I have written a comprehensive article on this topic covering all possible error that may come while reading MS Excel file in C#.
Below are possible error if you skip either $ or square bracke 
If you fail to suffix $ in the Sheet name
System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'Sheet2'. Make sure the object exists and that you spell its name and the path name correctly.
If you fail to keep the sheet name in square bracket ""
System.Data.OleDb.OleDbException: Syntax error in FROM clause.
Full article is here http://www.dotnetfunda.com/articles/article1954-how-to-read-ms-excel-file-and-populate-into-gridview.aspx
Hope this will be useful.