Syntax error (missing operator) in query expression

Question

0.00/5 (No votes)

See more:

Please help I've been trying to sort this out for 2 days!!

This is my code

OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(("SELECT FirstName, LastName, Salutation, PurchaseDate, ContractEndDate, Make, Model, RegistrationNumber, DateOfRegistration, OTRPrice, CashDeposit, PXPrice, BalanceToFinance, SalesConsultant, FullAddress, HomeTel, WorkTel, MobileTel, email, NewOrUsed, FinanceType, MonthlyPayment, GFVorBalloon, TermInMonths, APR, ContractNumber, " + (Profile.Company) + " AS Company ID,  FROM " + sWorkbook), OleDbConn);

OleDbConn.Open();   

OleDbDataReader dr = OleDbCmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);

I keep getting the error Syntax error (missing operator) in query expression 'New Company' where 'New Company' is the value Profile.Company.

So it is picking up the value but wont use it??

Thanks in advance

Posted 2-Oct-10 23:46pm

just_got_used_to_1.1

Updated 2-Oct-10 23:54pm

dan!sh

v2

Add a Solution

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2010-10-02T23:59:00

Solution 1

You need to remove the comma(,) before FROM:

OleDbCommand OleDbCmd = new OleDbCommand(("SELECT FirstName, LastName, Salutation, PurchaseDate, ContractEndDate, Make, Model, RegistrationNumber, DateOfRegistration, OTRPrice, CashDeposit, PXPrice, BalanceToFinance, SalesConsultant, FullAddress, HomeTel, WorkTel, MobileTel, email, NewOrUsed, FinanceType, MonthlyPayment, GFVorBalloon, TermInMonths, APR, ContractNumber, " + (Profile.Company) + " AS Company ID FROM " + sWorkbook), OleDbConn);

Good luck!

Posted 2-Oct-10 23:59pm

E.F. Nijboer

Comments

E.F. Nijboer 3-Oct-10 6:23am

Assuming the result of (Profile.Company) is a valid column name... shouldn't you specify the name of the worksheet instead of the workbook (-> sWorkbook)?

just_got_used_to_1.1 3-Oct-10 7:13am

sWorkbook is referenced earlier in the code as the worksheet. It all works fine until I try to add the Profile.Common value. Thanks

E.F. Nijboer 3-Oct-10 9:59am

You could simply first put the sql in a string and have a look at it (or post it) before using it in the OleDbCommand constructor. You could also try and replace "New Company" with "NewCompany" for testing purposes so you know it's about the space or just something else.

*dan!sh* · Answer 2 · 2010-10-03T00:04:00

Solution 3

This is a basic syntax for querying excel file:

Select columnName from [sheetname$]

Check how your query looks like at runtime. Also, since your column name has a space in it, try giving it inside [].

Posted 3-Oct-10 0:04am

dan!sh

Comments

just_got_used_to_1.1 3-Oct-10 7:12am

Thanks d@nish, the column name is actually CustomerID, that was a typo as I've been playing around with it for toooo long!!

I've changed it and still the same error

Syntax error (missing operator) in query expression 'New Company'.

Any other ideas. I've run out !

just_got_used_to_1.1 · Answer 3 · 2010-10-03T00:02:00

Solution 2

Thanks,

I've changed that now and I'm still getting exactly the same error.

Any other thoughts.

BTW if I change the value to an integer it works fine, so it's something to do with the syntax for using a string I think.

Cheers

Posted 3-Oct-10 0:02am

just_got_used_to_1.1

Updated 3-Oct-10 0:04am

v2

Comments

Sandeep Mewara 3-Oct-10 7:36am

Not an answer. Use 'Add Comment' feature to respond back.

just_got_used_to_1.1 3-Oct-10 7:39am

Thanks Sandeep this has already been pointed out to me. Sorry.

just_got_used_to_1.1 · Answer 4 · 2010-10-03T00:07:00

Thanks d@nish, the column name is actually CustomerID, that was a typo as I've been playing around with it for toooo long!!

I've changed it and still the same error

Syntax error (missing operator) in query expression 'New Company'.

Any other ideas. I've run out !

Here's the stack trace - Line 80 is highlighted red as the error line

Line 80: OleDbDataReader dr = OleDbCmd.ExecuteReader();

Syntax error (missing operator) in query expression 'New Company'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'New Company'.

Source Error:

Line 78: OleDbCommand OleDbCmd = new OleDbCommand(("SELECT FirstName, LastName, Salutation, PurchaseDate, ContractEndDate, Make, Model, RegistrationNumber, DateOfRegistration, OTRPrice, CashDeposit, PXPrice, BalanceToFinance, SalesConsultant, FullAddress, HomeTel, WorkTel, MobileTel, email, NewOrUsed, FinanceType, MonthlyPayment, GFVorBalloon, TermInMonths, APR, ContractNumber, " + (Profile.Company) + " AS CompanyID FROM " + sWorkbook), OleDbConn);
Line 79: OleDbConn.Open();
Line 80: OleDbDataReader dr = OleDbCmd.ExecuteReader();
Line 81: DataTable dt = new DataTable();
Line 82: dt.Load(dr);
Stack Trace:

[OleDbException (0x80040e14): Syntax error (missing operator) in query expression 'New Company'.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1070856
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +116
System.Data.OleDb.OleDbCommand.ExecuteReader() +6
Main_AddBulkRenewal.ButtonUploadFile_Click(Object sender, EventArgs e) in
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Syntax error (missing operator) in query expression

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Syntax error (missing operator) in query expression

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us