this my code following error will accuring plz help me
The Microsoft Jet database engine could not find the object 'sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
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.Fill(dsUserData, "UserData");
DataAdapter.AcceptChangesDuringFill = false;
DataAdapter.Dispose();
con.Close();
}
}
return dsUserData.Tables[0];
}
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
objXConn.Open()
'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)
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.
chrisjul
0 Points
1 Post
Microsoft Jet database engine could not find the object 'sheet1$'.
Dec 19, 2011 04:31 PM|LINK
this my code following error will accuring plz help me
The Microsoft Jet database engine could not find the object 'sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
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.Fill(dsUserData, "UserData"); DataAdapter.AcceptChangesDuringFill = false; DataAdapter.Dispose(); con.Close(); } } return dsUserData.Tables[0]; }Ahmed Moosa
Star
7784 Points
1232 Posts
Re: Microsoft Jet database engine could not find the object 'sheet1$'.
Dec 19, 2011 06:53 PM|LINK
hi,
it should work ,if connection string is valid, and sheet name and ofcourse excel file is exists, check for them ,and it will work.
MCC - MCPD -MCTS
Kajamoinudee...
Member
4 Points
3 Posts
Re: Microsoft Jet database engine could not find the object 'sheet1$'.
Feb 22, 2012 06:30 AM|LINK
Dim objXConn As New OleDbConnection(xConnStr) ' create your excel connection object using the connection string objXConn.Open() '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)SheoNarayan
Member
624 Points
100 Posts
Re: Microsoft Jet database engine could not find the object 'sheet1$'.
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
If you fail to keep the sheet name in square bracket "[]"
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.
Thanks
ASP.NET Online Training
500+ ASP.NET real time web development tips & tricks