Oledbdataadapter Syntax Error In From Clause
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta syntax error in from clause c# access Discuss the workings and policies of this site About Us Learn
Syntax Error (missing Operator) In Query Expression
more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Syntax Error in From clause Excel up vote 1 down vote favorite public void SetConnection(string text1, string text2, string text3, string text4, string text5, string text6, string text7) { connectionString1 = "Initial Catalog=testdb; Data Source=work\\sqlexpress"; database = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\Users\\test.xls';Extended Properties= \"Excel 8.0;HDR=Yes;IMEX=1\";"); database.Open(); database1 = new OleDbConnection("Provider=SQLOLEDB.1;" + connectionString1); database1.Open(); } data1 = DataAccess.DatabaseTables("SELECT * from [Sheet1$])", DataAccess.database); public DataTable DatabaseTables(string QueryString, OleDbConnection DataConnection) { OleDbCommand SQLQuery = new OleDbCommand(); DataTable data = null; SQLQuery.Connection = null; OleDbDataAdapter dataAdapter = null; SQLQuery.CommandText = QueryString; SQLQuery.Connection = DataConnection; data = new DataTable(); dataAdapter = new OleDbDataAdapter(SQLQuery); dataAdapter.Fill(data); return data; } I am getting exception "Syntax error in from clause" for the line of code starting at data1. This line calls the function below it DatabaseTables which generates this exception. I have also included the connection string which uses jet etc in the fn SetConnection. I think the problem could be the connection string itself. Currently i have to leave the excel file open to prevent a "could not decrypt" error. I did try the ACE driver but got errors. The excel work book is saved as a 97-03 workbook. c# .net excel oledbconnection oledbcommand share|improve this question asked May 20 '13 at 9:56 Arantuath 961212 Is your sheet named as She
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join http://stackoverflow.com/questions/16646926/syntax-error-in-from-clause-excel the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How do I resolve the OleDb error “Syntax error in FROM clause”? up vote 0 down vote favorite Presently I am http://stackoverflow.com/questions/14445561/how-do-i-resolve-the-oledb-error-syntax-error-in-from-clause attempting to import a CSV file using this function: public DataSet ImportCommaSeparatedValueFileToDataSet(string SourceFile) { var dsImportCSVtoDataSetReturn = new DataSet(); using (var objAdapter1 = new OleDbDataAdapter()) { String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFile.Substring(0, SourceFile.LastIndexOf(@"\")) + ";Extended Properties='text;HDR=Yes;FMT=Delimited'"; var objConnection = new OleDbConnection(sConnectionString); objConnection.Open(); var objCmdSelect = new OleDbCommand("SELECT * FROM " + SourceFile, objConnection); objAdapter1.SelectCommand = objCmdSelect; objAdapter1.Fill(dsImportCSVtoDataSetReturn); objConnection.Close(); } return dsImportCSVtoDataSetReturn; } When I attempt to import a file that has no space in the filename, it works fine. When I attempt to import the following file: D:\Workspace\WoldCard export.csv Then I receive the following exception: excException = {"Syntax error in FROM clause."} Source = "Microsoft JET Database Engine" StackTrace " at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecor
caused by something not that complex. As the case always is once you have the solution. So I thought I would share some of the things that I've come across, and hopefully tha {"Syntax error in https://blogs.msdn.microsoft.com/spike/2008/07/17/syntax-error-in-from-clause-when-using-text-based-datasources/ FROM clause."} when using text based datasources ★★★★★★★★★★★★★★★ Michael Aspengren - MSFTJuly 17, 20080 Share 0 0 This is also one of the issues that I have seen a few times. What happens here is that customer uses *.csv or *.xls or any text file as a data source and connects to it using .Net and OleDb. The error reported from the framework when issuing a SELECT is: syntax error {"Syntax error in FROM clause."} The most common reason for this is that the *.csv or *.xls file name contains a space. No space and all is well. The reason for this is that the file name is the table name, and as such, no spaces are allowed and should be enclosed in square brackets ie. [table name]. Let’s show by example. Create a *.csv file in C:\Temp with the following content. CID;FNAME;LNAME syntax error in 1;John;Smith 2;Peter;Johnson 3;Steve;Lloyd Then save it as Test.csv and then as Test Test.csv. Create a new .Net console application and just edit the Program.cs file to look like this. using System; using System.Text; using System.Data.OleDb; using System.Data; class Program { static void Main(string[] args) { // With space string fName = @"C:\Temp\Test Test.csv"; // Without space //string fName = @"C:\Temp\Test.csv"; // Get the dir out of the full path. string path = fName.Substring(0, fName.LastIndexOf(@"\")); string cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='text;HDR=Yes;FMT=CSVDelimited'"; OleDbConnection oc = new OleDbConnection(cString); oc.Open(); // Get the file out of the full path. int pos = fName.LastIndexOf(@"\") + 1; string tablename = fName.Substring(pos, fName.Length - pos); // This will work with spaces and without spaces in filename, // this is because the tablename will be now enclosed in square brackets. OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + tablename + "]", oc); // Running the select will result in: // {"Syntax error in FROM clause."} //OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [" + tablename, oc); OleDbDataAdapter oda = new OleDbDataAdapter(ocmd); DataSet ds = new DataSet(); oda.Fill(ds); oc.Close(); Console.WriteLine("Number of rows in {0}: {1}", tablename, ds.Tables[0]