CODEWITHSUNDEEP
vb.netms-accesssyntax-error
EliVictorio
EliVictorio

Reputation: 11

Syntax Error (missing operator) in Access Query

I have a table named Services and it has two columns ID and Price. here is my code (VB.NET):

com.CommandText = "UPDATE Services SET Price = 
(CASE WHEN (ID = 'Bedsheet') THEN @Bedsheet WHEN (ID = 'Comforter') 
THEN @Comforter WHEN (ID = 'PressOnly') THEN @PressOnly WHEN (ID = 'WDF') 
THEN @WDF WHEN (ID = 'WDP') THEN @WDP END) WHERE ID IN 
('Bedsheet','Comforter','PressOnly','WDF','WDP')"

It always says a Syntax error (missing operator) in query expression message. What do I have to correct in my code?

Upvotes: 0

Views: 499

Answers (1)

EliVictorio
EliVictorio

Reputation: 11

Quoting from a person who gave me help, "The CASE syntax works in SQL/Server but is not supported in ACE (the Access native SQL driver)." I used the Switch() function instead and got the job done. here is my new code:

UPDATE Services SET Price = SWITCH(ID = 'Bedsheet', @Bedsheet, ID = 'Comforter', @Comforter, ID = 'PressOnly', @PressOnly, ID = 'WDF', @WDF, ID = 'WDP', @WDP) 
WHERE ID IN('Bedsheet','Comforter','PressOnly','WDF','WDP')

Upvotes: 1

Related Questions