Syntax error (missing operator) in query expression, Oledb UPDATE Statement

Question

I'm a beginner at c# and I've tried looking through many posts to try and resolve my problem but I haven't had any luck, so I thought I may as well create a post to ask for your help. Basically,I'm trying to update an excel file with some extra info, but the problem is every-time I run the source code I keep getting this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression 'Wireless Stereo Headphone (TAK) (damaged)'.

I've tried a lot of ways to fix it including using an INSERT statement instead of the UPDATE statement but nothing seems to work.

Thanks again for the Help =).

int m_intRecipientESMID = Convert.ToInt32(m_recipientESMID);

//Converting Row integer to string
string m_excelRowCoordinateLoanItemTrackerStr = m_excelRowCoordinateLoanItemTracker.ToString();

//Creating a connection directory to access the Excel "LoanItemTracker" file
string m_pathSourceExcelLoanItemTrackerNOHDR = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBoxExcelLoanItemTrackerLocation.Text + @";Extended Properties=""Excel 8.0; HDR=NO;ImportMixedTypes=Text;TypeGuessRows=0""";
OleDbConnection m_pathConnectionExcelLoanItemTrackerNOHDR = new OleDbConnection(m_pathSourceExcelLoanItemTrackerNOHDR);

m_pathConnectionExcelLoanItemTrackerNOHDR.Open();   //Open Excel File LoanItemTracker.xlsx to conduct Write/Update process

string m_scannedItemName2 = "Wireless Stereo Headphone (TAK)      (damaged)";

// write the loaned item name into the Excel Spread sheet "LoanItemTracker.xls"
string m_excelRowColumnLoanItemNameCoord = String.Concat(m_excelColumnCoordinateLoanItemNameLoanItemTracker, m_excelRowCoordinateLoanItemTrackerStr);
string m_LoanItemNameWriteCommand = String.Format("UPDATE [Sheet1${0}:{0}] SET F1={1}", m_excelRowColumnLoanItemNameCoord, m_scannedItemName2);
OleDbCommand m_LoanItemTrackerCommandWrite = new OleDbCommand(m_LoanItemNameWriteCommand, m_pathConnectionExcelLoanItemTrackerNOHDR);

m_LoanItemTrackerCommandWrite.ExecuteNonQuery();

m_pathConnectionExcelLoanItemTrackerNOHDR.Close();

Patrick Hofman · Accepted Answer · 2014-05-25 12:16:53Z

1

You have to put quotes around the text:

String.Format("UPDATE [Sheet1${0}:{0}] SET F1='{1}'", m_excelRowColumnLoanItemNameCoord, m_scannedItemName2);

I put them before and after {1}. Also, it is preferred to use parameterized queries. You won't need quotes then too.

answered May 25, 2014 at 12:16

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Majid Akbari · Accepted Answer · 2014-05-25 12:19:17Z

0

check it : UPDATE [Sheet1${0}:{0}] SET F1='{1}' maybe you forgot quotes.

answered May 25, 2014 at 12:19

Majid Akbari

2101 silver badge13 bronze badges

Collectives™ on Stack Overflow

Syntax error (missing operator) in query expression, Oledb UPDATE Statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related