MySQL .NET connector doesnt support complex queries?

I have a table with real estate listings. Each listing is linked to an estate agent. When a agent leave the company his listings has to be redistributed equally to the active agents using the data of the last update as sorting criteria.

After some documentation searching I've managed to obtain a single row MySQL command which works well when executed from a MySQL GUI (I'm using Navicat MySQL):

UPDATE listings, (SELECT @rownum:=@rownum+1 As rownum, x.id FROM (SELECT @rownum:=0) as r, listings as x) as t SET listings.agentid = ELT(MOD(t.rownum+1,4)+1,1007,1015,1011,1010) WHERE listings.id = t.id AND listings.agentid = 1014  

where in the above example: 1014 is the id of the leaving agent and 1007,1015,1011,1010 are the ids of the remaining agents

When this query is executed via MySQL .NET Connector 6.5.4 (the last one) using ExecuteNonQuery from a Visual Basic .NET 2 Framework application, a fatal error with no description is fired.

I have 2 questions:

is there a problem with MySQL .NET Connector supporting such a command?

any possible workaround to my problem... maybe a better query... (I'm not comfortable with the idea of moving to the application level)?

链接地址: http://www.djcxy.com/p/60976.html

上一篇: .NET MySql连接器的utf8问题

下一篇: MySQL .NET连接器不支持复杂的查询？